June 30, 2011 at 10:39 am
I need to display data in a report format(either via a sql statement or via SSRS 2008 R2).
the data contains a recursive parent child relationship with amounts linked to each.
The lowest level (big parent) has a parent value of null. the rest is recursively dependent on each other.
1. My first problem is that i'd like each level to display the total of its associated child-levels. i.e it should sum its own total and that of its child totals(All child levels).
2.i'd like to display this in SSRS as clickable groups. I know about the recursive parent option, but it still does not auto sum the data of sub-levels
The Data has 4 columns:
RootID,ReferencedParentID,Description,Amount
28,NULL,ADMINISTRATIVE,0
29,28,Rejection of membership application,28
30,28,Inaccessible networks,0
31,28,Contributions of benefits,0
32,28,Information not received from the scheme,0
33,28,Payment of benefits,0
34,28,Other,47
35,28,Pre-authorisation,224
74,33,Short payment,0
75,33,Unpaid account,0
76,33,Reversal of a claim,0
78,32,Incorrect information from scheme,62
79,32,Brochures / BOT information,18
80,32,Membership Certificate,10
81,32,Other,15
82,31,Contributions misrepresented / premium increase without prior notice,51
83,31,Benefits suspended ? non-payment,11
84,31,Benefits suspended / terminated due to non-disclosure of material information,6
85,31,Benefits exhausted or excluded,12
86,31,Limits on benefits,21
87,31,Other,13
88,30,Restriction on Choice of Provider,7
89,30,Other,2
90,30,Provider not accessible,8
91,75,Account not received / submitted / stale,121
92,75,Incorrect information on account,48
93,75,Sub-limits in option,252
94,75,Member terminated,25
95,75,Other,50
96,76,Member terminated,12
97,76,Sub-limits in option,4
98,76,Claim paid in error,21
99,76,Other,3
100,74,Sub-limits in option,251
101,74,Other,23
what i do then is a recursive query
WITH temptab ( RootID, ReferencedParentID, description,amount, level )
AS ( SELECT root.RootID,
root.ReferencedParentID,
CAST(ROOT.Description AS VARCHAR(1000)) AS description,
root.amount,
0 AS level
FROM #tempIT ROOT
WHERE ROOT.ReferencedParentID IS NULL
and ROOT.RootID = 28
UNION ALL
SELECT sub.RootID,
sub.ReferencedParentID,
CAST(( temptab.description + ' | ' + sub.Description ) AS VARCHAR(1000)) AS description,
sub.Amount AS amount,
LEVEL + 1
FROM #tempIT sub
INNER JOIN temptab ON temptab.RootID = sub.ReferencedParentID
)
SELECT *
FROM temptab
This returns following data:
RootID,ReferencedParentID,description,amount
28,NULL,ADMINISTRATIVE,0
29,28,ADMINISTRATIVE | Rejection of membership application,28
30,28,ADMINISTRATIVE | Inaccessible networks,0
31,28,ADMINISTRATIVE | Contributions of benefits,0
32,28,ADMINISTRATIVE | Information not received from the scheme,0
33,28,ADMINISTRATIVE | Payment of benefits,0
34,28,ADMINISTRATIVE | Other,47
35,28,ADMINISTRATIVE | Pre-authorisation,224
74,33,ADMINISTRATIVE | Payment of benefits | Short payment,0
75,33,ADMINISTRATIVE | Payment of benefits | Unpaid account,0
76,33,ADMINISTRATIVE | Payment of benefits | Reversal of a claim,0
96,76,ADMINISTRATIVE | Payment of benefits | Reversal of a claim | Member terminated,12
97,76,ADMINISTRATIVE | Payment of benefits | Reversal of a claim | Sub-limits in option,4
98,76,ADMINISTRATIVE | Payment of benefits | Reversal of a claim | Claim paid in error,21
99,76,ADMINISTRATIVE | Payment of benefits | Reversal of a claim | Other,3
91,75,ADMINISTRATIVE | Payment of benefits | Unpaid account | Account not received / submitted / stale,121
92,75,ADMINISTRATIVE | Payment of benefits | Unpaid account | Incorrect information on account,48
93,75,ADMINISTRATIVE | Payment of benefits | Unpaid account | Sub-limits in option,252
94,75,ADMINISTRATIVE | Payment of benefits | Unpaid account | Member terminated,25
95,75,ADMINISTRATIVE | Payment of benefits | Unpaid account | Other,50
100,74,ADMINISTRATIVE | Payment of benefits | Short payment | Sub-limits in option,251
101,74,ADMINISTRATIVE | Payment of benefits | Short payment | Other,23
78,32,ADMINISTRATIVE | Information not received from the scheme | Incorrect information from scheme,62
79,32,ADMINISTRATIVE | Information not received from the scheme | Brochures / BOT information,18
80,32,ADMINISTRATIVE | Information not received from the scheme | Membership Certificate,10
81,32,ADMINISTRATIVE | Information not received from the scheme | Other,15
82,31,ADMINISTRATIVE | Contributions of benefits | Contributions misrepresented / premium increase without prior notice,51
83,31,ADMINISTRATIVE | Contributions of benefits | Benefits suspended ? non-payment,11
84,31,ADMINISTRATIVE | Contributions of benefits | Benefits suspended / terminated due to non-disclosure of material information,6
85,31,ADMINISTRATIVE | Contributions of benefits | Benefits exhausted or excluded,12
86,31,ADMINISTRATIVE | Contributions of benefits | Limits on benefits,21
87,31,ADMINISTRATIVE | Contributions of benefits | Other,13
88,30,ADMINISTRATIVE | Inaccessible networks | Restriction on Choice of Provider,7
89,30,ADMINISTRATIVE | Inaccessible networks | Other,2
90,30,ADMINISTRATIVE | Inaccessible networks | Provider not accessible,8
So, if i would type it out in excel it would look like this(I summed the child fields manually):
Description,Amount
ADMINISTRATIVE,1691
Contributions of benefits,114
Benefits exhausted or excluded,12
Benefits suspended ? non-payment,11
Benefits suspended / terminated due to non-disclosure of material information,6
Contributions misrepresented / premium increase without prior notice,51
Limits on benefits,21
Other,13
Inaccessible networks,17
Other,2
Provider not accessible,8
Restriction on Choice of Provider,7
Information not received from the scheme,199
Brochures / BOT information,18
Incorrect information from scheme,62
Membership Certificate,10
Other,15
Other,47
Payment of benefits,1062
Reversal of a claim,40
Claim paid in error,21
Member terminated,12
Other,3
Sub-limits in option,4
Short payment,274
Other,23
Sub-limits in option,251
Unpaid account,496
Account not received / submitted / stale,121
Incorrect information on account,48
Member terminated,25
Other,50
Sub-limits in option,252
Pre-authorisation,224
Rejection of membership application,28
June 30, 2011 at 11:32 am
Welcome rud. Next time please provide the sample DDL and DML to build your tables so we can setup your environment on our side.
This article is a great starting point: http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
Here is the test bed for those dropping by:
IF OBJECT_ID(N'tempdb..#tempIT') > 0
DROP TABLE #tempIT ;
GO
CREATE TABLE #tempIT
(
RootID INT,
ReferencedParentID INT,
DESCRIPTION VARCHAR(500),
Amount INT
) ;
GO
INSERT INTO #tempIT
(RootID, ReferencedParentID, DESCRIPTION, Amount)
VALUES (28, NULL, 'ADMINISTRATIVE', 0),
(29, 28, 'Rejection of membership application', 28),
(30, 28, 'Inaccessible networks', 0),
(31, 28, 'Contributions of benefits', 0),
(32, 28, 'Information not received from the scheme', 0),
(33, 28, 'Payment of benefits', 0),
(34, 28, 'Other', 47),
(35, 28, 'Pre-authorisation', 224),
(74, 33, 'Short payment', 0),
(75, 33, 'Unpaid account', 0),
(76, 33, 'Reversal of a claim', 0),
(78, 32, 'Incorrect information from scheme', 62),
(79, 32, 'Brochures / BOT information', 18),
(80, 32, 'Membership Certificate', 10),
(81, 32, 'Other', 15),
(82, 31, 'Contributions misrepresented / premium increase without prior notice', 51),
(83, 31, 'Benefits suspended ? non-payment', 11),
(84, 31, 'Benefits suspended / terminated due to non-disclosure of material information', 6),
(85, 31, 'Benefits exhausted or excluded', 12),
(86, 31, 'Limits on benefits', 21),
(87, 31, 'Other', 13),
(88, 30, 'Restriction on Choice of Provider', 7),
(89, 30, 'Other', 2),
(90, 30, 'Provider not accessible', 8),
(91, 75, 'Account not received / submitted / stale', 121),
(92, 75, 'Incorrect information on account', 48),
(93, 75, 'Sub-limits in option', 252),
(94, 75, 'Member terminated', 25),
(95, 75, 'Other', 50),
(96, 76, 'Member terminated', 12),
(97, 76, 'Sub-limits in option', 4),
(98, 76, 'Claim paid in error', 21),
(99, 76, 'Other', 3),
(100, 74, 'Sub-limits in option', 251),
(101, 74, 'Other', 23) ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 30, 2011 at 10:48 pm
Hi.
I've got a post on my blog that should be able to help you:
http://jsimonbi.wordpress.com/2011/01/14/dealing-with-parent-child-sql-queries-pt2/
Here is an excerpt that aggregates each level:
;WITH CTE
As
( SELECT p.ProductName
, p.ProductID
, ParentProductID
, ps.SaleQty
FROM dbo.Product p
LEFT OUTER JOIN dbo.ProductSale ps
ON ps.ProductID = p.ProductID
WHERE ParentProductID IS NOT NULL
UNION ALL
SELECT p.ProductName
, p.ProductID
, p.ParentProductID
, H.SaleQty
FROM dbo.Product p
INNER JOIN CTE H
ON H.ParentProductID=p.ProductID
)
SELECT c.ProductName, c.ProductID, SUM(SaleQty) AS SaleQty
FROM CTE c
WHERE SaleQty IS NOT NULL
GROUP BY c.ProductName, c.ProductID
July 1, 2011 at 3:25 am
Thanks, that has worked
July 1, 2011 at 4:33 am
rud (7/1/2011)
Thanks, that has worked
Really? Would you post the code you ended up with, please, because I just don't see that particular aggregation doing what you asked. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 1, 2011 at 4:52 am
1.Actually the best solution was
I was missing that second column(in the article called "Total Order Quantity") which solved my SSRS aggregation issue.
2.But the sql in itself also worked. I just don't do the "description " column combining anymore because I don't need it for SSRS.
WITH temptab2
AS ( SELECT root.RootID,
root.ReferencedParentID,
ROOT.Description,
root.amount,
0 AS level
FROM #tempIT ROOT
WHERE ROOT.ReferencedParentID IS NOT NULL
UNION ALL
SELECT sub.RootID,
sub.ReferencedParentID,
sub.description,
temptab2.Amount AS amount,
LEVEL + 1
FROM #tempIT sub
INNER JOIN temptab2 ON temptab2.ReferencedParentID = sub.RootID
)
SELECT RootID,
referencedParentid,
description,
SUM(amount) AS amount
FROM temptab2
WHERE amount IS NOT NULL
GROUP BY referencedParentid,
description,
rootid
--some root records that don't have children(full dataset I have) are being dropped
--in CTE. i add them back here
UNION ALL
SELECT RootID,
referencedParentid,
description,
amount
FROM #tempIT
WHERE referencedParentid IS NULL
AND RootID NOT IN ( SELECT rootid
FROM temptab2 )
July 1, 2011 at 6:51 am
Thanks... I wanted to take a deeper dive on this particular post and your code will help a lot. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply