The SSRS Multi Value Parameter allows the users to select More than one value from a list, and filter the Report data using the user-selected values. Or, SSRS Multi Value Parameter enables the users to to Filter the Reports using more than one value Dynamically.
In this article, we will show you how to add a Multi value parameter in SSRS with an example.
We are going to use the below-shown report to explain, SSRS Multi Value Parameter Filter in table 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, Full Name, Occupation, Yearly Income, and Sales Amount columns.
The below screenshot will show you the Shared Data Source, and Dataset we used for this SSRS Multi Value Parameter 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]
Add SSRS Multi Value Parameter Filter
Before we start creating the SSRS Multi Value Parameters. Let us create one more 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]
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.
- Name: Please specify the valid Parameter name as per your requirement. Here, we defined it as CountryParameter
- Prompt: The text you specified here will display as a label before the text box
- Data Type: Country name is a Text data type. So, we kept the default text unchanged
- Allow Multiple values: If you want to allow the user to select more than one value, Please check mark this option.
Next, We have to select Available Values as shown in the below screenshot. If you can specify the values manually, you can choose specify values option and enter the values manually.
In this SSRS Multi Value Parameter 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. The report will be filtered using this value.
- Label Field: This value show to the end-user.
Click Ok to finish configuring the SSRS Multi Value Parameter. Now, We have to apply filter condition on 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 Multi Value Parameter example, we will display the records whose Country present in [Country Parameter]. So, please select the Country as Expression and Operator as SQL IN Operator and Value as Parameter name.
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 SSRS Multi Value Parameter Filter at Dataset Level. Let’s Click on Preview Tab to preview the Data.
From the above screenshot, see that it displays a Blank Report, allows us to select multiple values from the list of all the country names present in the Dataset we created. To show the records, we have to select the Country Name (s) from the list and press Enter.
For now, We selected Canada and Germany as Country Names.
