Searchable / Dependant Parameters in SSRS
Introduction
The idea behind this post came from a question from a colleague, they wanted an SSRS report where they could select from a list of employees but the list could be filtered by searching on the surname (i.e. you enter ‘S’ and it would load a dropdown of all employees whose surname start with S).
It’s quiet an interesting requirement because we have very little control over the manner in which parameters are selected by the user. There is however a very simple way to achieve this utilising dependant parameters, in SSRS we can set the default value or available values to be dependant on the value selected in a previous parameter.
Getting Data On The Report
Lets start from the data for the report and work backwards, I’m using the Adventure Works sample database and we are going to load pay rate records for an employee.
We can create a new dataset that selects all pay records for an employee;
I’ll add a simple table to the report to display the returned records;
And when we run the report we are prompted to enter an employee ID;
Adding a Searchable Drop Down for Employee
SSRS will allow us to use a query to retrieve possible values for a parameters, so lets add a dataset that will load a list of employees whose surname starts with a specified search phrase;
Note the @Search parameter that is highlighted
Now if we select Report -> Report Parameters from the menu we will see Visual Studio has automatically generated an EmployeeID and Search parameter for us. First we need to move the Search parameter to be the first in the list since the values of the EmployeeID are dependant on it. Next highlight the EmployeeID parameter and in the “Available Values” sections tick the “From Query” radio button, select CandidateEmployees as the dataset, EmployeeID as the value field and DisplayName as the Label Field.
When we run the report the Employee dropdown is greyed out, if we enter “R” into the search box and then press tab the employee dropdown will be populated with all employees whose surname starts with R, we can select one and then click “View Report”… Hey Presto!!!
I like this approach, but wondered whether it could be adapted to cope with multi-select list boxes,i.e. ‘remember’ selections between searches. e.g. I search for staff whose surname begins with ‘S’, select the person I want; then do a second search for a staff member whose name begins with ‘B’
Peter Schmidt
December 5, 2011
BEtFhS gayzxiqioyno
xeoujruul
February 7, 2012