Use report parameters in SQL Server Report Service (1)

When using a report, you need to add parameters to narrow the report scope for better analysis. You will often see that you need more than one parameter. The second parameter may depend on the first parameter. Generally, you are required to update the second parameter based on the first parameter.

In addition, you may need to add parameters with multiple and/or default values. This article describes how to create parameters in SQL Server 2005 report Service (SSRS.

Prerequisites

To understand the content of this article, you should understand how to create a report in SSRS, because this article will review in detail how to create a report in SSRS. However, I will introduce as much information as possible when creating an SSRS report. Also, you will need to know how to write a basic T-SQL query that connects two or more tables.

To effectively use resources, You Need To Install SQL Server 2005 and SQL Server Bi Development Kit together. Because I will use the data in the AdventureWorks sample database, if you install the AdventureWorks database in your SQL Server version, it will be much simpler.

Example

Since examples are helpful for readers, let's assume that we want to list employees based on their country, state, province, and city. The user should have an option to select a country. Based on the selected country, we will list the States/provinces of the selected country. After selecting a state, we should list the cities and users should have options to select one or more cities from the list. Based on one or more cities selected, the user should be able to get a list of qualified employees.

Implementation

First, you must create a report service project from SQL Server business intelligence, and then add a report to this project. Next, create a shared data source pointing to the AdventureWorks database. Now we need to add a dataset for the country. You can add this dataset from the database tag by selecting 'options' in the dataset list box. Configure the dsCountry dataset as shown in.

 

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