Hierarchical SQL Queries, SSRS 2008, and Summing Columns

Question

I am building an SSRS report and am having a bit of trouble figuring out how to implement it.

First, here is the basic structure of the data:

Level 1a
- Level 2a
- Level 2b
- Level 2c
  - Level 3a
Level 1b
Level 1c

In the database, each level has an id and a parent id where all parent ids at the root (parents of level 1) are 0. Additionally, each level has a column with a numeric value. For example, I might have:

Level1a
ParentId = 0
Id = 1
DataValue = 42

Level2a
ParentId = 1
Id = 2
DataValue = 1

Currently, I have implemented a common table expression to get the hierarchy structure and levels of the data:

WITH cteTable(id, parentId, data, level) AS 
(SELECT id, 
        parentId, 
        data, 
        1 AS level 
FROM sampleTable 
WHERE (parentId = 0) 
UNION ALL 
SELECT b.id, 
       b.parentId, 
       b.data, 
       c.level + 1 AS Expr1 
FROM sampleTable AS b 
INNER JOIN cteTable AS c ON b.parentId= p.id
) 
SELECT id, 
       parentId,  
       data, 
       level 
FROM cteTable

so the with the sample data from earlier, the results of the query would be:

+----+----------+------+-------+
| id | parentId | data | level |
|----+----------+------+-------+
| 1  | 0        | 42   | 1     |
| 2  | 1        | 1    | 2     |
+----+----------+------+-------+

From here, I need to build a report that will sum up the data to the root level. For example, the report would show that the data sum for id 1 is 43. One thing I don't know for certain is how deep the levels go - there will be some instances where there are no child levels and others where the tree goes a few levels deep.

There are two options I am considering right now. One is to create a drill down report which will show the data at each level with a sum where available. For example:

-Level1a SUM_LEVEL2_AND_LEVEL1a_DATA
   Level2a DATA
   Level2b DATA
  -Level2c SUM_LEVEL3_AND_LEVEL2c_DATA 
      Level3a DATA
Level1b DATA
Level1c DATA

The other is to sum the data to the root and show a simple table in the report with the parent totals only. For example:

Level1a SUM_LEVEL1A_AND_ALL_CHILDREN_DATA
Level1b SUM_LEVEL1B_AND_ALL_CHILDREN_DATA
Level1c SUM_LEVEL1C_AND_ALL_CHILDREN_DATA

I can't seem to figure out how to get the drill down report working with the googling I've done (there is a link here that seemed to be useful, but I just couldn't get it to work).

Changing the database schema is not an option.

Does anyone know of any good resources I could use to get started or have any ideas on how to proceed with this?

Let me know if I am missing anything...

score 0 · Accepted Answer · answered May 13 '11 at 21:40

I finally found a solution to this one.

The common table expression (CTE) was a good start. The only change I made to it was in the recursive definition - instead of collecting the id of the immediate parent for each child, I assigned it the grandparent's id (the parent id the immediate parent had). This meant that every child was assigned the ultimate parent id instead of moving up only one step in the hierarchy. From there, it was pretty straightforward to use this in the report.

I put the CTE in a stored procedure of its own - the only purpose is to bring back the entire hierarchy. From there, I created another stored procedure, which combined the CTE with a different stored procedure. At this time, I applied the filters and returned the final result set.

Hierarchical SQL Queries, SSRS 2008, and Summing Columns

1 Answers1