Using Report Parameters in SQL Server Reporting Services

Parameters Using Another Parameter

The next task is to create the state/province parameter. As I said before, the state/province parameter depends on which country the use chose in the first query, so in our state/province dataset, we should write the query to satisfy that criteria.

You can see from the image above that ‘@Country’ is included for the query. The ‘@Country’ variable will get the value of the Country parameter that user selected.

Next is to create a report parameter named Province and attach it to the dsProvience dataset. This is done the same way we did the Country parameter. The Province report parameter screen will look as depicted below.

Since we are nearly halfway through the report, we are in position to do a simple test by clicking the “preview” tab. You can see that the province list changes when you change the country. For example, the following two images can be seen when countries Australia and Germany.

Multi-value Parameters

After the country and province parameters have been configured, we will now configure the city parameter. There is no master table for city data. We are using the Person.[Address] table to get a list of cities as a workaround. As the city depends on the selected province, we need to use @Province parameter to get the list of cities. Below is an image of the dataset for city, dsCity.

The next task is to define the city parameter and attach the dsCity dataset to it. The following is the City parameter image.

There are a few differences in the city parameter than in other two parameters we configured previously.

As we said at the start, users should have the option of selecting one or more cities from the list. Therefore, we need to select a Multi-Value attribute.

Next is to select a default value for the city parameter. For city, we are going to select all the available values by default. To select all the values, we should set the dsCity dataset and city value filed for default values option.

The order of the parameters indicates the order in which it will be displayed in the report. To change the order you can use the up and down arrows.