Wednesday, June 3, 2015

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.

No comments:

Post a Comment

Popular Posts