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.
The below screenshot shows the Data Source and Dataset we used for this report.
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]
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.
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 Multi Value parameter article to understand the uses of multi-values.
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.
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.
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]
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.
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.
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
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.
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.
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 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
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.
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.
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.
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.
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 Cascading parameters to filter the data.
