Using Report Parameters in SQL Server Reporting Services

Introduction

When you are using reports, you need to have parameters that narrow down your report for better analysis. You will often see that you need to have more than one parameter, where the second parameter may depend on the first parameter, which many require you to refresh the second parameter depending on the first parameter.

In addition, you may have to have parameters with multiple and/or default values. This article addresses how to create parameters in SQL Server 2005 Reporting Services (SSRS).

Pre-Requests

To understand the article content, you should have a fair knowledge of creating a report in SSRS, as this article does review how to create a report in SSRS in detail. However, I will try to cover as much as possible when it comes to creation of SSRS report. Also, you will need to know how to write basic T-SQL queries joining two or more tables.

Resource wise, you need to have SQL Server 2005 installed with SQL Server Business Intelligence Development Studio. As I am going to use data in the AdventureWorks sample database, it will be easier if you have installed an AdventureWorks database with your version of SQL Server.

Sample Case

Since it is always helpful to the reader to explain things through example, let us assume we want to list out employees depending on their country, state/province and city. Users should have an option of selecting a country. Depending on the selection of country, we need to list state/provinces which belong to the selected country. After selecting a state, we should list all the related cities and the user should have the option of selecting one or more cities from the list. Depending on the selected city or cities, the user should get a final list of employees that fit the criteria.

Implementation

First, you must create a Report Server Project from SQL Server Business Intelligence, then add a report to the project. Next is to create a shared data source that is pointed to the AdventureWorks database. We now need to add a dataset for country. You can add this dataset from the dataset tab by selecting ‘<New Dataset> Option’ from the dataset list box. Then configure the dsCountry dataset as depicted in the below image.

The next task is to assign this dsCountry to a report parameter. Select the ‘Report Parameters’ option from the Report menu.

Below is an image of the screen you should see.

The options in the dialog box above are:

Name – Name of the parameter. When you select a value, that value is stored in this parameter. As this is a variable name, you cannot have special characters (*, ! or spaces, etc.) in this field.

Data Type – Data type of the parameter. Options of this field include: Boolean, Datetime, Integer, Float and String. In this case, we will chose ‘String’ for the country parameter.

Prompt – Prompt is what you see in the report. As this is a label, you can have any characters for the prompt.

The following attributes are as simple as their names indicate. The difference between Hidden and Internal is that hidden variable can be changed from mechanisms and internal variables cannot be changed.

The next step is to assign values for the parameters.

The non-queried option should be used when a parameter has fixed values, such as Yes/No, Male/Female etc. From query option is to fill values from a dataset to parameter. In this example, the dataset is dscountry and we need to fill in two values: value field and label field. The label field is what users will see and the value field is what will be stored in the parameter. In this case, Name is the label field while CountryRegionCode is the value field. For example, if the user selects the United Status, US will be stored in the Country parameter.

The next step is to assign default values. A default value makes it easier for users to work with reports because it makes the most probable value the user will chose the default value. For example, in this report users be selecting United States, so having United States being the default value makes it easier for the user to fill out the form.