Wednesday, June 3, 2015

SSRS - Parameterized Dropdown List in SSRS Report

Parameterized Dropdown List SSRS in Report

For Parameterized Dropdown List Report, 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 SSRS - My First Parameterized Report, we have learnt to add parameter in the SSRS Report. We are starting from our last post. In the post, we’ll modify the parameter into Dropdown List in SQL Server Data tools from Microsoft SQL Server 2012.

Now, we have the following report as shown below:

 


 
We need to follow the following steps to add Department Dropdown List instead of the Textbox in the report-

1) In Report Data tool, right click on the Datesets and click on the Add Dataset as shown below:





2) It will open Dataset properties as shown below:
 



3) In the query section, change the name as DepartmentList and choose Use a dataset embedded in my report as shown below:
 



4) After clicking on the Query Designer button, Query Designer will open as shown below:



 

5) In the Query Designer, add Employees table and select Department column as shown below:

 

 



 
6) Now, change the query to get the unique departments as shown below:
SELECT Distinct Department
FROM Employees
Order by Department

7) Run the query in the Query Designer which return the following values as shown below:



 

8) After clicking on Ok button, we will come back the Dataset Properties window as shown below:



9) After clicking on the Ok Button on the Dataset properties window, DepartmentList is added in the Datasets as shown below:



 

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

 

11) Double click on the @inpDept parameter, It will open Report Parameter Properties window as shown below:
 

 

12) From Available Values, choose Get values from query. Select Dataset as DepartmentList, Value field and Label field will be Department as shown below:



 

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




14) We can choose any value from the Department list and click on the View Report as shown below:
 

 




Now we are able to get the information on the report called Department Wise Employee Details based on the Department Dropdown list parameter.

No comments:

Post a Comment

Popular Posts