SSRS - Toggle subreports by parameter value

Question

Requirements are as follows:

• Grouping hierarchies should be swappable, for example Country > City > Department and Country > Department > City can be selected by the end user.
• For the end user, there should be only one report.
• The hierarchy that the user wants can be selected in a parameter value.
• The data for the report should only be loaded once, since the query contains cpu intensive calculations.

I'm trying to do this by adding subreports for each hierarchy. Since hidden subreports are automatically loaded, I cannot toggle visibility of multiple subreports, or the data will be loaded twice. The ReportName property of the Subreport does not allow expressions. Can I use custom code to solve this, or what is a viable solution?

Answer 1

There are quite a few ways to do this.

If your dataset is embedded in your report, then I think the simplest way is:

1. Add your parameter, let's call it GroupingOrder, and hardcode your options: say set the Value to 1 for label of Country > City > Department and value of 2 for Country > Department > City

2. Add two calculated fields to your dataset within SSRS. (Right click on the dataset name and select "Add Calculated Field...". Name one "MiddleGroupName" and the other "InnerGroupName." Set the Field Source to formulas such as this for MiddleGroupName:

   =IIF(Parameters!GroupingOrder.Value = 1, Fields!City.Value, Fields!Department.Value)

3. Use these calculated fields as normal groups in your report.

4. For bonus points, set the column titles based on your parameter: the column title for the middle group could be:

   =IIF(Parameters!GroupingOrder.Value = 1, "City", "Department")

Another method could involve moving these calculations into the Grouping logic itself.

But I would steer clear of subreports in this case. They tend to hurt performance and create debugging difficulty.