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 ‘ 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.

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.

Data Body

We have defined and configured three parameters. Now we need to set a data set for main data body. I have defined dsCustomer data set with following T-SQL.

SELECT i.CustomerID, c.FirstName, c.MiddleName, c.LastName, c.Phone, c.EmailAddress, a.AddressLine1, a.AddressLine2, a.City, a.PostalCode

FROM Sales.Individual AS i INNER JOIN

Person.Contact AS c ON c.ContactID = i.ContactID INNER JOIN

Sales.CustomerAddress AS ca ON ca.CustomerID = i.CustomerID INNER JOIN

Person.Address AS a ON a.AddressID = ca.AddressID INNER JOIN

Person.AddressType AS at ON ca.AddressTypeID = at.AddressTypeID

WHERE (a.City IN (@City))

Where the @City parameter contains multiple values of city, you need to use the IN function. Table control was used to present data.

Display Parameters

Normally you need to display the selected report parameters value in the report. For country and province it is straight forward; =Parameters!Country.Label and =Parameters!Province.Label are values needed to display for the country and province. As city is a multi-value parameter, you cannot use =Parameters!city.Label. Instead, you need to use =Join(Parameters!City.Label,","). From the JOIN command, it will concatenate all the values in the City parameter. You can define the character you want to join the values from the JOIN command.

Passing Parameters

Most of the time, you may not stop after developing just one report. Instead, you will build series of reports. In that case, you may need to pass parameters from one report to another. There are two ways of calling one SSRS report from another. One is subreport and the other is the jump to report option. There is not much of a difference in passing parameters using either method.

For example, if you want to display customers in Berks and Berkshire cities in England, UK, you would need to drag & drop the text box and type in the necessary text value in the text box. Right click the text box and select properties. Select the Navigation tab, then select the Jump to Report option and finally select the report you want to navigate to. In this case, it will be the report named Report. Then select the parameters button to pass the correct parameters.

You should remember that you need to pass the parameter value, not the label. You can see that we have passed GB for country (not United Kingdom), and the same for Province. As city is multi-value parameter, you need to use the Split command with the string of cities.

In case you need to pass parameter values from a parameter, you need to specify the parameter as indicated in following image, which shows an example from the subreport. However, there is nothing different between this and the earlier method.

You can see that the user interface is also the same as the Navigation method. In addition, it doesn’t make a difference whether it is a multiple parameter or not.

Sample

You can download the sample SSRS project. There are three reports attached to the project. The following are the details of those reports.

Report Name	Description
Report.rdl	Report with three parameters and data
Passing Parameters for Navigation Report.rdl	Passing parameters for hyperlink navigation
Passing Parameters for SubReport.rdl	Passing parameters for subreport

Conclusion

Parameters are very important feature in SSRS. Even though it is simple, there are many features that users do not use. This article has tried to cover most of the features about parameters of SSRS. If you need any further details, have questions or need further clarification, mail me at dineshasanka@gmail.com. I look forward to hearing from you.