two options for a hierarchy report

Question

I have a hierarchy report that I can't quite get it right.

First let me present a sample data, as follows.

create table level_table

(

level1 varchar(10),

level2 varchar(10),

level3 varchar(10),

level4 varchar(10),

data int

)

insert level_table (level1, level2, level3, level4, data) values ('A', 'AA', null, null, 10)

insert level_table (level1, level2, level3, level4, data) values ('A', 'AB', 'ABA', null, 8)

insert level_table (level1, level2, level3, level4, data) values ('A', 'AB', 'ABB', null, 5)

insert level_table (level1, level2, level3, level4, data) values ('B', 'BA', 'BAA', 'BAAA', 15)

insert level_table (level1, level2, level3, level4, data) values ('B', 'BA', 'BAA', 'BAAB', 6)

insert level_table (level1, level2, level3, level4, data) values ('B', 'BB', 'BBA', null, 15)

insert level_table (level1, level2, level3, level4, data) values ('B', 'BC', null, null, 9)

select * from level_table

As you can see, this is a ragged hierarchy that can go upto 4 levels deep. For simplicity, the data is always posted at the lowest level and aggregated up. The biggest challenge in working with a ragged hierarchy is that the report needs to know dynamically when to stop drilling down.

From what I've seen, there are two ways to build a drill down report on this kind of data.

Method 1: Recursive Hierarchy as demonstrated in the following link.
http://www.sqlservercentral.com/articles/Development/reportingonhierarchicalrecursivedatausingreporting/2476/

To use this method, I need to modify the above data to use the recursive parent child relationship. I've done this previously and was able to build a drill down report that was smart enough to know to stop drilling when it reached the lowest level. It's simple to build and works great.

However, (and perhaps I'm doing something wrong) when you export this report out to excel, you lose the drill down capability. Instead, the data is exported with everything expanded, and is very confusing to work with, if the end users wanted to further process the data in excel.

Question: So is there a way to export a recurive hierachy report to excel and still maintain the drill down capability?

Method 2: Insert groups for each level and toggle inner groups from the outter group. I don't have a link for this method, but hopefully you'll know what I'm talking about. To use this method, you work with the data as presented above and insert a group for each level, ie, group by level3 within group by level 2 within group by level1.

When you make a report as described above you get something that looks like this.

http://img518.imageshack.us/img518/7782/drilldown.jpg

But as I indicated when I started this thread, as it is, the report does not know where the bottom level is. For example, level3 'ABA" and 'ABB' has the toggle item and if you drilled down on it you get another unnecessary row repeated for these two data points.

But if I can get this solved, I want to go with this method because, unlike method1, I don't lose the drill down feature when the report is exported to excel.

Question: So is there a way to make the drill down here dynamic? Someone seems to have done it in this link, http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/2b03bb40-c868-4177-9253-9269f3d8de36/, but I'm not sure how to implement the expression he is talking about here.

Thanks for reading and I look forward to some helpful suggestions.

Answer 1

Any suggestions from SSRS experts? Was my question not clear?

Answer 2

Hi,

 

Generally speaking, there are several ways to achieve the hierarchy report in Reporting Services so far.

 

One is using Recursive Hierarchy group as you already know. The advantage is the dataset could be made simpler. You just need ID and Parent ID to generate report. However, in this method, you cannot achieve aggregate for each level. Also, after exporting to the Excel, the drill down will disappear.

 

Another method is using the dataset with level columns as you mentioned above. You could simply achieve the hierarchy report and exporting to the Excel. The limitation is that you need to write the hard-code for each level. However, if you want to stop a drill down when there is no further group, you could achieve this by adding additional independent columns. In your example, it means you need to add 4 columns before each group. In this way, drill down will stop when there are no rows. If you want to see more details, here is a similar thread for reference:

http://social.technet.microsoft.com/forums/en-US/sqlreportingservices/thread/563e6d9e-c824-4719-933e-37306a3b652f

 

Another one, is using sub report to achieve Hierarchy group, you have to create sub report for each level.  If you want to see more details, here is a similar thread for reference:

http://social.technet.microsoft.com/forums/en-US/sqlreportingservices/thread/62d41018-9b5a-4197-9b32-b4a181f82d9e

 

However, because of the technological limitation in Excel, you need to use a List object instead of a table or a matrix, otherwise, you will see error message: "Subreports within table/matrix are ignored" after exporting to the Excel.

 

Please let me know if this helps.

 

Regards,

Raymond

Answer 3

Raymond, thank you for your input. What you wrote above covers what I've experimented with and as you noted, there are limitations within each. The recursive method is the most desired because of it's simple and dynamic design, but the export function to excel has some wanted behaviors.

I worked out an adaptation to the recurive method to get over some of these export behaviors. I wrote about it here, in case anyone else is interested.

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/734188e8-cd11-4fc1-bcf5-6dc4d806c411