Updating report parameters based on parameter selection? (SSRS)

reporting-servicessql-server-2005

I've created a report in SSRS with two report parameters. I'd like the second to update it's values based on the selection in the first. The first is a list of sales agents, and the second is a list of distributors. Each agent can have many distributors.

So if the report user selects an agent, I'd like only their distributors to show in list for the second parameters. Obviously the parameter values will come from a query, but I don't know how to make it rebind the list once they select an agent.

Best Solution

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.

Related Question