Multiple Parameters in SSRS

The Multiple Parameters in SSRS allows the users to Filter the SSRS Reports using Multiple parameter values Dynamically. It is similar to selecting Brand, Size, and Price Range in E-commerce websites like Flipkart and Amazon.

This article will show you how to add Multiple parameters in SSRS or SQL Server Reporting Services with an example.

We use the below-shown report to explain, Multiple parameters in SSRS Reports. Please refer to Table Report article to understand the creation of SSRS Table report. If you observe the below screenshot, It was a normal Table report with Country, State, City, Full Name, Occupation, Yearly Income, and Sales Amount columns.

Multiple Parameters in SSRS 0

The below screenshot shows the Data Source and Dataset we used for this report.

Multiple Parameters in SSRS 1

The SQL Command we used in the above screenshot is:

SELECT Geo.[EnglishCountryRegionName] AS [Country]
      ,Geo.[StateProvinceName] AS [State]
      ,Geo.[City]
      ,Cust.FirstName +' '+ Cust.LastName AS [Full Name]  
      ,Cust.EnglishEducation AS Education 
      ,Cust.EnglishOccupation AS Occupation
      ,SUM(Cust.YearlyIncome) AS YearlyIncome
      ,SUM(Fact.SalesAmount)AS SalesAmount 
FROM  DimCustomer AS Cust
  INNER JOIN
      FactInternetSales AS Fact ON 
 Cust.CustomerKey = Fact.CustomerKey
 INNER JOIN
     [DimGeography] AS Geo ON
   Cust.GeographyKey = Geo.GeographyKey
 GROUP BY Geo.[EnglishCountryRegionName]
         ,Geo.[StateProvinceName] 
	 ,Geo.[City]
	 ,Cust.FirstName
         ,Cust.LastName  
         ,Cust.EnglishEducation 
         ,Cust.EnglishOccupation 
  ORDER BY [Country]

Adding Multiple parameters in SSRS

Before we start creating the Country parameter. Let us create one dataset in the Report data Tab. First, We are creating one Embedded Dataset holding Distinct country names from [DimGeography] table in [AdventureWorksDW2014]

Multiple Parameters in SSRS 2

SQL Command we used in the above screenshot is:

SELECT [EnglishCountryRegionName] AS [Country]

FROM [DimGeography]

To add Multiple Parameters in SSRS, right-click on the Parameters Folder present in the Report Data tab will open the Context Menu to select Add parameters.. option.

Multiple Parameters in SSRS 3

Once you click on Add parameters.. option, it will open a new window called Report parameter Properties to configure the parameter properties. Please provide a valid Name, Prompt text and Data type for the Report Parameter.

Allow Multiple values: If you want to allow the user to select more than one value, Please check mark this option. Please refer to SSRS Multi Value parameter article to understand the uses of multi-values.

Multiple Parameters in SSRS 4

Next, We have to select Available Values. If you can specify the values manually, you can choose the specify values option and enter the values manually.

In this SSRS Multiple Parameters example, We would like to use the CountryName Dataset we created earlier. So, select Get Values from a query option and select the Dataset from the list.

Multiple Parameters in SSRS 5

Please specify the Value Filed and Label Field. Here, we have only one column so, both will be Country.

  • Value Filed: This value sent to the query. A report will be filtered using this value.
  • Label Field: This value show to the end-user.
Multiple Parameters in SSRS 6

Click Ok to finish configuring the Country parameter.

Next, we are creating one more Embedded Dataset holding Distinct State names from [DimGeography] table in [AdventureWorksDW2014]. SQL Command we used in the below screenshot is:

SELECT DISTINCT 
         A1.[EnglishCountryRegionName]
        ,A2.[StateProvinceName]
FROM [DimGeography] AS A1
    ,[DimGeography] AS A2
WHERE A1.[EnglishCountryRegionName] = A2.[EnglishCountryRegionName]
ORDER BY [StateProvinceName]
Multiple Parameters in SSRS 7

Next, We have to create one more parameter to hold the State information. So, Right Click on the Parameters Folder present in the Report Data tab will open the Context Menu to select Add parameters.. option.

Please provide a valid Name, Prompt text, and Data type for the Report Parameter. Please, checkmark this Allow Multiple values option If you want to allow the user to select more than one value.

SSRS Multiple parameters 1

In this SSRS Multiple Parameters example, We would like to use the StateDS dataset we created earlier. Select Get Values from a query option and select the Dataset from the list. Next, We are assigning [State Province Name] as Value Filed and Label Field.

SSRS Multiple parameters 2

Click Ok to finish configuring the State parameter.

Next, we are creating one more Embedded Dataset holding Distinct City names from [DimGeography] table in [AdventureWorksDW2014]. SQL Command we used in the below screenshot is:

SELECT DISTINCT 
         A1. [StateProvinceName]
        ,A2.City 
FROM [DimGeography] AS A1
    ,[DimGeography] AS A2
WHERE A1.StateProvinceName = A2.StateProvinceName
ORDER BY City
SSRS Multiple parameters 3

Next, We have to create one more parameter to hold the City information. So, Right-click on the Parameters Folder present in the Report Data tab will open the Context Menu to select Add parameters.. option.

Please provide a valid Name, Prompt text, and Data type for the Report Parameter. Please, checkmark this Allow Multiple values option If you want to allow the user to select more than one value.

SSRS Multiple parameters 4

In this SSRS Multiple Parameters example, We would like to use the CityDS dataset we created earlier so, select Get Values from a query option and select the Dataset from the list. Next, We are assigning [City] column as Value Filed and Label Field.

SSRS Multiple parameters 5

We have successfully created three Datasets and three Parameters for Country Name, State Name, and City.

Now, We have to apply filter conditions to the dataset. Please refer to Filter at Dataset Level in SSRS article to understand the creation of Filters.

Select the Dataset from the Report data tab, and right-click on it will open the context menu. Please select the Dataset Properties.. option from it

SSRS Multiple parameters 6

In this SSRS Multiple Parameters example, we are going to display the records whose [Country Name] present in [Country Parameter], [State Name] present in [State Parameter], and [City Name] present in [City Parameter]. So, we are creating three filters for a country, state, and City.

SSRS Multiple parameters 6

Here we are using Operator as SQL IN Operator. If you find any difficult to write the parameter name, Please click on the fx button and select the parameter name from the GUI.

Click Ok to finish configuring the Filters at Dataset Level. Let’s Click on the Preview Tab to preview the Data.

SSRS Multiple parameters 8

From the above screenshot, see that it is displaying a Blank Report allowing us to select Multiple values from the Country names, States, and Cities present in the Datasets we created.

SSRS Multiple parameters 9

To display the records, We have to select the Country Name (s) from the list, State Name (s) from the list and City Name (s) from the list and Press Enter.

For now, We selected the Australia Country, New South Wales State, and Coffs Harbour and Darlinghurst Cities.

Multiple parameters in SSRS 10

In this SSRS Multiple Parameters example, If you know all the states and City names in every Country, you won’t face any problem. What if you don’t remember them?. To resolve these situations, We have to use SSRS Cascading parameters to filter the data.