Dynamics Community Forum Thread Details

Customizing Retail Channel Reports

Hi there

I'm have to create some additional reports as well as to alter some existing ones.

I have to make several grouping.

I tried my procedure in SQL Server and they are working fine.

DECLARE @STARTDATE DATE
DECLARE @ENDDATE DATE

SET @STARTDATE = '2019-01-01'
SET @ENDDATE =getdate()

    DECLARE @t_Result TABLE(
        ACCOUNTNAME NVARCHAR(60),
		ACCOUNTNUM NVARCHAR(60),
		TRANSDATE DATE,
        TRANSAMOUNT DECIMAL(18,2),

        ISTOTAL BIT
    );

   INSERT INTO @t_Result SELECT 
        ACCT.NAME AS ACCOUNTNAME,
		ACCT.ACCOUNTNUM AS ACCOUNTNUM,
		RTT.TRANSDATE AS TRANSDATE,
        -CAST(SUM(TRANS.AMOUNT) AS DECIMAL(18,2)) AS TRANSAMOUNT, -- flip the sign because it is negative
		
        0 -- ISTOTAL
    FROM dbo.RETAILTRANSACTIONINCOMEEXPENSETRANS TRANS WITH (NOLOCK)
        INNER JOIN dbo.RETAILTRANSACTIONTABLE RTT WITH (NOLOCK)
            ON RTT.STORE = TRANS.STORE
            AND RTT.TERMINAL = TRANS.TERMINAL
            AND RTT.TRANSACTIONID = TRANS.TRANSACTIONID
        INNER JOIN dbo.RETAILINCOMEEXPENSEACCOUNTTABLE ACCT
            ON TRANS.STORE = ACCT.STOREID 
            AND TRANS.INCOMEEXEPENSEACCOUNT = ACCT.ACCOUNTNUM
            AND TRANS.DATAAREAID = ACCT.DATAAREAID
    WHERE-- TRANS.CHANNEL = '5637144621' 
        --AND 
		ACCT.ACCOUNTTYPE = 0 -- income type => 0, expense type => 1
        AND RTT.TRANSDATE >= @STARTDATE
        AND RTT.TRANSDATE < DATEADD(d, 1, @ENDDATE)
	GROUP BY 
		GROUPING SETS (
			(ACCT.NAME,ACCT.ACCOUNTNUM,RTT.TRANSDATE),
			(ACCT.NAME,ACCT.ACCOUNTNUM),
			(RTT.TRANSDATE),
			()
	)
   ORDER BY ACCT.NAME,RTT.TRANSDATE

    IF @@ROWCOUNT > 0
    BEGIN
		UPDATE @t_Result
		SET ACCOUNTNAME ='TOTAL', ISTOTAL = 1 WHERE
			ACCOUNTNAME IS NULL 
			AND ACCOUNTNUM IS NULL
			AND  TRANSDATE IS NULL

       -- INSERT INTO @t_Result SELECT
      --  '', '','',  SUM(TRANSAMOUNT), 1 -- ISTOTAL 
		--FROM @t_Result where ACCOUNTNAME <>'' or  TRANSDATE IS NOT NULL

        --SELECT CASE WHEN TRANSDATE IS NULL THEN ACCOUNTNAME ELSE '' END AS ACCOUNTNAME,TRANSDATE,TRANSAMOUNT 
        SELECT ACCOUNTNAME,ACCOUNTNUM, TRANSDATE,TRANSAMOUNT
		FROM @t_Result
		WHERE ACCOUNTNAME <>''
        ORDER BY ISTOTAL ASC, ACCOUNTNAME,TRANSDATE
    END

Would someone happen to know, if GROUPING SETS and/or UNION ALL are working?

Quick Links

Leaderboard