none
Declarar un stored procedure en crystal report RRS feed

  • Pregunta

  • 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

    viernes, 6 de junio de 2014 15:36