SQL Server Reporting Services: combine initial rendering level and interactive expansion

Question

I have a report in SSRS 2008 that shows data aggregated from four levels of grouping. I'm using the ASP.NET ReportViewer control to show the report on my webform.

If I show all four levels in detail, the report gets very very long (hundreds of pages). So therefore, I'd like to set a default level to show (1-4) when I render the report - I do this by having a report parameter of type INT (@ShowLevel) which I set when rendering out the report for the first time.

The rows in the tablix are set to be hidden if the level they're on is more than the @ShowLevel value, e.g. if @ShowLevel = 2, the rows on level 3 and level 4 will have their "Hidden" property set to True, and won't show.

Great so far - it works nicely and all.

BUT: now the client would like to render the report to level 2 initially, but then interactively expand certain level-2 nodes to show their level 3 subnodes.

How do I handle this? Since on level 3, hidden is true, the node will "expand", but nothing is shown underneath it... is there a way for a level 3 node to check for

Hidden = (@ShowLevel < 3) OR (group-parent is expanded)

or something like that, to support both an initial rendering level (set via the report parameter), but still allow interactive expansion of individual nodes even past that initial "show level" ?

I can't seem to figure out how to do this properly and easily......

Any ideas? Hints? Tips & tricks??

Answer 1

Hi marc, have you looked at toggling as an option in Reporting Services. What you describe I have done plenty of times. Try this, highlight a group row once highlighted check the properties of that group row. One of the properties is called "Visibility". Expand that property. You will then see "ToggleItem", what toggleitem allows you to do is toggle an entire group based on clicking on another object. For instance, above this group you could create a textbox that forces the toggle.

In this manner you only display the data you want but you allow the client to toggle further levels.

Here is an example for you:

PS: Did you have any luck on the other thread with the reporting services report files using the ReportServer db ?