Crystal Report - Declarar un stored procedure en crystal report

 
Vista:
sin imagen de perfil

Declarar un stored procedure en crystal report

Publicado por miguel (13 intervenciones) el 06/06/2014 16:40:24
Buenas mi duda es como insertar un stored procedure en crytal report este es mi procedure el cual se le pasan 4 parametros
USE [XETUX]
GO
/****** Object: StoredProcedure [dbo].[SP_BACKEND_SALES_CONSOLIDATE] Script Date: 06-06-2014 09:08:41 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_BACKEND_SALES_CONSOLIDATE]
@startDate DATE,
@endDate DATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @current_journal INT = 0
DECLARE @sum_net_sales FLOAT
DECLARE @journalDate DATETIME

SELECT journal_id, journal_start_datetime INTO TEMP_JOURNAL
FROM T_POS_JOURNAL
WHERE CONVERT(DATE,journal_start_datetime) in (@startDate, @endDate)

SELECT TOP 1 @journalDate = journal_start_datetime FROM TEMP_JOURNAL;

SELECT @journalDate as journal_date,
isnull(SUM(CAST(orders.net_total AS DECIMAL(12,2))),0) as net_sales,
isnull(SUM(CAST(orders.service_value AS DECIMAL(12,2))),0) as services_sales,
isnull(SUM(CAST(orders.tax_total_value AS DECIMAL(12,2))),0) as tax_sales,
isnull(SUM(CAST(payment.tip AS DECIMAL(12,2))),0) as tips_sales,
isnull(SUM(CAST(orders.discount_total_value AS DECIMAL(12,2))),0) as discount_sales,
isnull(SUM(CAST(orders.net_total + orders.service_value + orders.tax_total_value AS DECIMAL(12,2))),0) as sales_total,
isnull(SUM(CAST(orders.number_of_diners AS INT)),0) as total_number_of_diners,
isnull(COUNT(CAST(orders.order_id AS INT)),0) as total_number_of_orders,
order_types.order_type_description,
order_types.total_sale as order_type_total_sale,
CAST((order_types.total_sale*100)/(SUM(CAST(orders.net_total AS DECIMAL(12,2)))) AS DECIMAL(12,2)) as order_type_total_percentage,
order_types.number_diners,
CAST((order_types.number_diners*100)/(SUM(CAST(orders.number_of_diners AS DECIMAL(12,2)))) AS DECIMAL(12,2)) as order_type_diners_percentage,
order_Types.orders_orderType,
CAST((order_Types.orders_orderType*100)/(COUNT(CAST(orders.order_id AS INT))) AS DECIMAL(12,2)) as order_type_orders_percentage
FROM T_POS_ORDER orders
LEFT JOIN T_POS_BILL bill
ON orders.order_id = bill.order_id
LEFT JOIN T_POS_BILL_PAYMENT billPayment
ON bill.bill_id = billPayment.bill_id
LEFT JOIN T_POS_PAYMENT payment
ON billPayment.payment_id = payment.payment_id,
(SELECT o.order_type_id, isnull(SUM(CAST(o.net_total AS DECIMAL(12,2))),0) as total_sale, oType.order_type_description, isnull(SUM(CAST(o.number_of_diners AS INT)),0) as number_diners, COUNT(o.order_id) as orders_orderType
FROM T_POS_ORDER o
LEFT JOIN T_POS_ORDER_TYPE oType
ON o.order_type_id = oType.order_type_id
GROUP BY o.order_type_id, oType.order_type_description
) as order_types
WHERE orders.journal_id in (SELECT journal_id FROM TEMP_JOURNAL)
GROUP BY order_types.order_type_description, order_types.total_sale, order_types.number_diners, order_Types.orders_orderType


DROP TABLE TEMP_JOURNAL
END
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
sin imagen de perfil
Val: 110
Plata
Ha mantenido su posición en Crystal Report (en relación al último mes)
Gráfica de Crystal Report

Declarar un stored procedure en crystal report

Publicado por Juan Gilberto (141 intervenciones) el 07/06/2014 20:24:51
Los Stored Procedures solamente pueden estar en el SQL Server (o similar)
y el reporte diseñado con Crystal Reports lo tomara como origen de datos, es decir, en Crystal Reports puedes seleccionar datos de Tablas, Vistas y Stored Procedures (Procedimientos Almacenados)
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar