Personalized Community is here!
Quickly customize your community to find the content you seek.
Now Available in Community - New TechTalk Videos for 2021
2021 Release Wave 2Discover the latest updates and new features releasing from October 2021 through March 2022.
2021 release wave 2 plan
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Community | FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All TechTalks
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(
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'
ACCT.ACCOUNTTYPE = 0 -- income type => 0, expense type => 1
AND RTT.TRANSDATE >= @STARTDATE
AND RTT.TRANSDATE < DATEADD(d, 1, @ENDDATE)
GROUPING SETS (
ORDER BY ACCT.NAME,RTT.TRANSDATE
IF @@ROWCOUNT > 0
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
WHERE ACCOUNTNAME <>''
ORDER BY ISTOTAL ASC, ACCOUNTNAME,TRANSDATE
Would someone happen to know, if GROUPING SETS and/or UNION ALL are working?
what kind of report are you talking about?
Did you test the same on your own?
Business Applications communities