The SSRS Multi Value Parameter allows the users to select More than one value from the List and then, SSRS will filter the Report data using the user selected values. OR SSRS Multi Value Parameter allows the users to Dynamically Filter the SSRS Reports using more than one value. In this article we will show you, How to add Multi value parameter in SQL Server Reporting Services 2014 with example.
We are going to use below shown report to explain, SSRS Multi Value Parameter in table Reports. Please refer SSRS Table Report article to understand the creation of 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 SQL Command we used in the above screenshot is:
SELECT Geo.[EnglishCountryRegionName] AS [Country]
,Geo.[StateProvinceName] AS [State]
,Cust.FirstName +' '+ Cust.LastName AS [Full Name]
,Cust.EnglishEducation AS Education
,Cust.EnglishOccupation AS Occupation
,SUM(Cust.YearlyIncome) AS YearlyIncome
FROM DimCustomer AS Cust
FactInternetSales AS Fact ON
Cust.CustomerKey = Fact.CustomerKey
[DimGeography] AS Geo ON
Cust.GeographyKey = Geo.GeographyKey
GROUP BY Geo.[EnglishCountryRegionName]
ORDER BY [Country]
Add SSRS Multi Value Parameter 2014
Before we start creating the parameter. 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]
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 specified it as CountryParameter
- Prompt: The text you specified here will be displayed as 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 below screenshot. If you can specify the values manually, you can select specify values option and enter the values manually. In this 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 will be sent to query. Report will be filtered using this value.
- Label Field: This value is shown to end-user.
Click Ok to finish configuring the SSRS Multi Value Parameter. Now, We have to apply filter condition on dataset. Please refer 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
In this example, We are going to display the records whose [Country Name] is present in [Country Parameter] we just created 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 Filters at Dataset Level. Lets Click on Preview Tab to preview the Data.
From the above screenshot you can observe that, It is displaying Blank Report allowing us to select Multiple values from the List of all the country names present in the Dataset we created. In order to display the records, We have to select the Country Name (s) from the list and Press Enter.
For now, We selected the Canada and Germany as Country Names.
Thank You for visiting Our Blog.