Crystal Report - Declarar un stored procedure en crystal report

   
Vista:

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

Declarar un stored procedure en crystal report

Publicado por Juan Gilberto (129 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