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.

Continues…

Pages: 1 2 3 4




Related Articles :

  • No Related Articles Found

2 Responses to “Using Report Parameters in SQL Server Reporting Services”

  1. I wa searching some good article for Parameters and tried to understand the use of it on MSDN ,but could not get it corrctly .After seeing your article It is easy for me to use the parameters .Thanks
    for the nice example

  2. Wonderful Article. Its been a few years since I’ve done any SSRS and needed a refresh class.

    Thank you

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |