SSRS - Multiple Values Selection Report

Multi-value parameters allow us to select more than one value for a report parameter. To create a multiple values parameterised report, we must have to change report parameters properties from single valued to multi-valued and must have made the necessary changes in a query also, a filter, and an expression to accommodate using a multi-value parameter collection. 

How does Multiple Values SSRS Report work?

For Multiple Values Dropdown List Selection, we are assuming that we have an Employee Master Data table in our database which contains the following data field –
1) Emp Id

2) Name
3) Age
4) Salary
5) Department

We need to display this information in our SSRS report and Report header should be like as “Department Wise Employee Details” and report should be like as –

Department wise Employees Details
Emp Id	Name	Salary	Age	Department
100	Amit Gupta	15450	32	IT
101	Anil Sharma	15965	22	Finance
102	Bill Smith	18540	25	HR
103	Chris Gray	18025	28	Sales & Marketing
104	David King	18025	22	Procurement
105	Eliza Roy	15965	25	Admin
106	Flash God	15965	23	Others

In our previous post Parameterized Dropdown List SSRS in Report, we have learnt how to add drop-down list parameter in the SSRS Report. We are starting from our last post.

In the post, we’ll modify the Drop-down List parameters settings as well as modify the main report dataset of the report. We are using SQL Server Data tools from Microsoft SQL Server 2012.

Now, we have the following report which is based on the single selection values as shown below:

Another records from Department Finance as give below:

Change in SQL Query for multiple values

1)      In Report Data tool, double click on Dataset1 in Datasets as shown below: 

2)     It will open Dataset properties as shown below:

3)     In the query section, change the name as EmployeeDeptDetails instead of DataSet1  as shown below:



4) Now, we need to change the where condition WHERE   Department in  (@inpDept)  instead of  WHERE   (Department =@inpDept) as shown below :

Note: Equal (=) sign is used to match the single value whereas In sign is use to match the multiple values in the SQL.

5)     After clicking on the Ok Button on the Dataset properties window, we will see the modification in Report Data tool as shown below:

Change in Report Parameter settings

6)     In our next step, we need to click Parameters in the Report Data as shown below:

  

7)     Double click on the @inpDept parameter, it will open Report Parameter Properties window. In General tab, we need to check the All multiple values checkbox  as shown below:



8)     Now click on OK button and preview the report as shown below:

In the above screen, checkboxes have been appeared against each department.

9)     We can select one or multiples values from the Department list and click on the View Report to get the information as shown below:

If we choose more than one values as shown below:

Now we are able to get the information on the report for multiple Departments.