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.