SSRS Report Iterating Over Multiple Parameters

Question

I have a report that has multiple cascading parameters, which works quite well out of Report Manager. I would like to create a version that will take a single parameter, and iterate over every parameter down the line.

As an example, suppose parameters such as state, county and city. If we pass in a state (ie Florida) the report would have multiple pages for each county, each page consisting of one city in that county. The object is to email monthly reports to stakeholders in each state, with data for each city in their state, organized by county.

I found this post:

Creating a report with SSRS that iterates over a parameter

which is right on topic, and basically says:

• Create a new tablix report whose query returns a (unique) set of possible values for the parameter
• Group by that set
• There is no detail row, but in the group footer/header add a subreport
• The subreport will be the report you already are using and it's parameter will be the parameter value from your main report query
• Set page breaks for the group

I got that much to work, but I have gotten stuck extending this concept to more than one parameter. I have tried to implement a hierarchy of sub-reports, each grouping on a dataset associated with a parameter, but I have not met with success. Is there a reason this isn't possible?

I would be grateful for any suggestions.

Answer 1

I would extend the query described in your 1st bullet point to cover all the parameter value combinations e.g. all required combinations of state, county and city. These fields would need to be added to the existing Row Group.

Then a single sub-report object can be passed all the parameter values from that query.