I found that I had to set at least one of the report parameters to not have a default to keep the report from autorunning.
I had to use this configuration (notice that all 3 of the parameters I left without defaults accept Nulls so the users can just click the Null checkboxes):
[screenshots missing]
to get the users to see this and to keep the report from autorunning:
[screenshots missing]
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.
Best Answer
I've had no problem doing this with the original set of parameters that are populated from a query.
In my reports I have a "Farm" parameter which is populated by a "SELECT FarmNumber, FarmName FROM Farms" query. The user selects the farm he wants from a ComboBox. I show the selected farm in the header of the report using this expression:
"Label" is the "display text" (FarmName in this case) for the farm that the user selected.