2 5

Added by Peter Avila April 25, 2012

In one of my SQL courses; 6236: Reporting Services, we learn how to create an “All” option in the values dropdown list of a parameter and see how to “wire up” the All option so that, when selected, the report runs for all values.

As an example, imagine a Sales Report that shows sales by region with a Country parameter that allows users to run the report for different countries. After adding the All option, the Country parameter dropdown list looks like this:

The All option is useful, but it has an important limitation; it only allows us to select either a single country or all countries, but if we want to select a few countries at random, let’s say the United States, Mexico, and Canada, for the same report run, this approach doesn’t allow us to do that.

In order to allow random, multiple-value selections in a parameter’s dropdown list, we must first enable multiple values for that parameter.

1. In the Report Data pane of the report, right-click on the parameter in the Parameters folder and select Parameter Properties.
2. Put a check in the Allow multiple values checkbox.
3. Click OK.

By simply doing the above, our parameter list looks very different when we preview the report. In the example below, three countries have been selected at random (note also the Select All option that is built into this approach):

This is a good start, but we’re not done. We still have to tie the parameter into the report’s dataset; in other words, we have to restrict the data coming from the dataset to only those rows that contain the selected states.

In order to accomplish that, we need to keep in mind that a multi-valued parameter looks like a list of values when the user selects multiple values. With the selections shown in the example above, the parameter would look like this: Albania, Algeria, Andorra. We need to tell our query in the report’s dataset that it should select rows where the country is one of those. We do this with an IN operator and we must also delimit the parameter in parenthesis:

1. In the Report Data pane of the report, right-click on the dataset of the report and select Dataset Properties.
2. Add a WHERE clause to the query like this: WHERE Country IN (@Country)

Enjoy!
Peter Avila
SQL Server Instructor – Interface Technical Training
Phoenix, AZ