Updating report parameters based on parameter selection? (SSRS)

Question

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.

Solution 1

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.

Solution 2

You will most likely need to create a shared data source for the multiple datasets

1. Design the report first with the query and fields to be displayed without any parameters. This way you will be able to set the main dataset of the report.
2. update the query of the dataset by adding the parameter. always use @ before param_name
3. on the report layout view, right click and select parameters from the pop up menu.
4. the parameters list will automatically has the param_name without the @ sign.
5. select the parameter from the list and update the its properties...

6. if you preview the report, it will ask for a value entered for param_name before viewing the report...

7. now go to data tab, and add new dataset.... call it dsPopParam

8. the query will be used to populate the param_name parameter.... no parameters needed here.
9. repeat steps 3 to 5 but in step 5 update the property setting in Available values section to be from Query and select the dataset dsPopParam. Value and Label fields will be the fields returned from the query of dsPopParam.
10. select the view tab again and notice this time the drop down list box containing the list of parameters....

Important note: in case the report did not render or exception was thrown, just switch to data tab again, select the report dataset and execute the query, you will be prompted to enter a value for param_name...enter it and execute it... then switch to preview tab and

Solution 3

You first create a dataset with a list of items which the user will select from.

Select ID,Agentname from Agents Order by Agentname

You then create the second dataset;

eg. Select ID,ItemName from Orders where agentid=@agentid

IN your report paramenter, you click on agentid parameter and then from a Query on available values. You select dataset1 and ID as value and AgentName as Label.

Your last dataset will be the actual data. In this You create your statement like :

Select Quantity, Amount From Orderdetails where OrderID=@orderid

In reporting service, you go to parameter and the set Order to get value from Dataset2

So the Order of parameter should be agentid, orderid. Reporting Service will automatically prompt for parameter by order.

Solution 4

I haven't tried this myself, but I saw an example where they configured Available Values to come from a query, then used an expression to specify the query. The expression included the value of the first parameter.

View more solutions

Comments

[John Saunders]

Jeremy, can you point to an example of that, or include one in your answer? Thanks.

[Jon Dewees]

Ok, this is what I did: I have two parameters, @SalesRep and @Distributor (both are strings). Now the Dataset for the sales rep is just a query. But the dataset for the distributor parameter is a query as follows: SELECT [Customer Number] AS ID, [Customer Name] AS Name FROM Distributors Where [Sales Representative]=@SalesRep ORDER BY Name Note the dependency on the @SalesRep parameter. In the report parameters, make sure that @SalesRep is ordered above @Distributor.

[Jeremy Smyth]

Yup, you got it. The topmost parameter must be the dataset with no dependencies. It produces the dataset to populate the second parameter, and so on.

[Jon Dewees]

The next question is how I do this with Multi-Valued lists... working on it right now.

[mbourgon]

FYI, I had to set up the actual report dataset (the one that provides the info) AFTER the parameter datasets, not before.