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 (sample in 2000).
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.