sql server 2005 - Updating report parameters based on parameter selection? (SSRS) -

I have created a report in SSRS with two report parameters. I will update it on the basis of the second selection Want second for The first is the list of sales agents, and the other is the list of distributors. Each agent can have many distributors.

So if the report user selects an agent, then I want to show only my distributors in the list for the second parameter. Obviously the parameter values ​​will come from a query, but I do not know how once the agent is selected and how to reboot it in the list.

This works automatically if you organize your parameters and datasets properly < / P>

• First of all, set a primary (report) dataset, then a dataset for each parameter dropdown. To correct dependency in parameter variables, code WHERE in the dataset
• Order another, order your parameters in the report. Parameter menu so that the user first variable / parameter is on top of the filling, and the second dataset depends on that parameter. Follow this order through parameters;
• Repeat for the next criteria

This will do your job if your second and subsequent datasets know where the variable SRSS is populated with the first parameter.

As an example, I have three datasets from the prestigious pubs database (in the sample 2000).

PubList is used to populate the @p parameter, and looks like this:

  choose pub_id, by pub_name Publisher  

populates the title list @t parameter, and looks like this:

  select the title_ id, Title titled: pub_id = Finally  reportdataset  looks like this: 
 
  Choose ytd_sales from the heading, value, title where title_id = @t  
 
  the order of the parameters in the report Iport parameters  menu key; Because the dataset should be executed in the order shown above, and the @t parameter is in a dataset, which depends on the @ p parameter being set first, we @ moment at the top of the list. 
 
 Now, with the SSRS label, evaluates the required dataset to fill the dropdown for the first parameter, it depends on a dataset for which the parameter is not required, so it can be prepared immediately is. 
 
 Then, after obtaining that parameter value, it can populate the second parameter dropdown. As a result the report is being produced.