Mar
6
2010
Cascading Parameter Values in SSRS
Author: Jeremy SmythIn SQL Server Reporting Services (SSRS), you might want your choice in the first parameter to limit the choices available in the second (and subsequent) parameters.
This works automagically if you order your parameters and datasets correctly
- First, set up a primary (report) dataset, then a dataset for each parameter dropdown. Code the WHERE clause in the datasets to make the dependencies correct across parameter variables
- Secondly, order your parameters in the Report | Parameters menu so that the first variable/parameter you want the user to fill in is at the top, and make the second dataset depend on that parameter. Follow this ordering through the parameters; the final parameter(s) should be the one(s) the actual report dataset depends on.
- Repeat for subsequent parameters
This will work if your WHERE clause in the second and subsequent datasets have variables that SSRS knows are populated from earlier parameters.
As an example, I have three datasets from the venerable pubs database.
pubslist is used to populate the @p parameter, and looks like this:
SELECT pub_id, pub_name FROM publishers
titleslist populates the @t parameter, and looks like this:
SELECT title_id, title FROM titles WHERE pub_id = @p
Finally, reportdataset looks like this:
SELECT title, price, ytd_sales FROM titles WHERE title_id = @t
The order of the parameters in the Report | Report Parameters menu is crucial; because the datasets must be executed in the order shown above, and the @t parameter is in a dataset that relies on the @p parameter being set first, we move @p to the top of the list.
Now, SSRS evaluates the dataset needed to fill the dropdown for the first parameter with labels. It relies on a dataset that doesn’t need a parameter, so can be produced immediately.
Then, having got that parameter value, it can populate the second parameter’s dropdown. That in turn results in the report being produced.
