Problem
I have a requirement where I need to provide two report parameters for a report. The first parameter will present a list of columns that the user can choose to filter the report. The second parameter will contain a list of available values based on what was chosen for the first parameter. As an example my first report parameter provides the options Gender and Marital Status. Based on the user selection I need to populate my second report parameter’s available list with Male and Female or Married and Single. How can I do this?
Solution
SSRS provides the capability to define a dataset to be used to populate the dropdown list for a report parameter. When you define the dataset you specify a query or stored procedure that will return the list of values for the report parameter dropdown. In this case we have a slight variation; the list of available values for the second report parameter isn’t filtered based on the first parameter; it’s completely different for each choice.
As a starting point let’s implement some functions and stored procedures that we can use to solve this problem; then we’ll create a report that will leverage these objects.
Functions and Stored Procedures
I want to create a function that will return the list of available values for each different filter parameter. By creating individual functions I can reuse them in multiple reports. The following functions will be used to populate the report parameter dropdown lists for Gender and Marital Status:
create function dbo.GetGenderChoiceList()
returns table
as
return
(
select
'Female' as FILTER_LABEL
, 'F' as FILTER_VALUE
union all
select
'Male'
, 'M'
)
go
create function dbo.GetMaritalStatusChoiceList()
returns table
as
return
(
select
'Married' as FILTER_LABEL
, 'M' as FILTER_VALUE
union all
select
'Single'
, 'S'
)
go
The above functions are examples of table-valued functions. They can be specified in the FROM clause of a select statement same as if they were actual tables. There are two column aliases that are used: FILTER_LABEL and FILTER_VALUE. When you define a report parameter and specify that the available options come from a query, you specify which column in the result set is the label (what you see in the dropdown list) and which column is the value (what you filter on). By aliasing these as FILTER_LABEL and FILTER_VALUE we make all parameter lists look the same.
Next let’s create the stored procedures we need. We’ll be creating a stored procedures to populate the report parameter dropdown lists and query the customer table.
GetCustomerFilterOptions will be used to populate the first report parameter dropdown, allowing the user to choose the filter column. It is shown below:
create procedure dbo.GetCustomerFilterOptions
as
begin
set nocount on;
select
'Gender' as FILTER_LABEL
, 'Gender' as FILTER_VALUE
union all
select
'Marital Status'
, 'Marital Status'
end
go
The FILTER_LABEL and FILTER_VALUE are the same; we could have assigned numbers to the FILTER_VALUE but in this case it is more intuitive to just go with the text descriptions.
GetFilterChoiceList will be used to populate the second report parameter dropdown, giving us the list of available values based on the filter column selected in the first report parameter dropdown; it is shown below:
create procedure dbo.GetFilterChoiceList
@filterby varchar(50)
as
begin
set nocount on;
declare @sql nvarchar(500)
set @sql = case @filterby
when 'Gender' then
N'select FILTER_LABEL, FILTER_VALUE
from dbo.GetGenderChoiceList()'
when 'Marital Status' then
N'select FILTER_LABEL, FILTER_VALUE
from dbo.GetMaritalStatusChoiceList()'
end
exec sp_executesql @sql
end
go
Note that GetFilterChoiceList simply calls the appropriate function that we created earlier, based on the filterby parameter. The sp_executesql stored procedure is used to execute a dynamic SQL statement.
FilterCustomerList contains a query to select customers and apply a filter based on parameters passed in to the stored procedure; it is shown below:
create procedure dbo.FilterCustomerList
@filterby varchar(50)
, @filtervalue varchar(50)
as
begin
set nocount on;
select LastName, Gender, MaritalStatus
from dbo.DimCustomer
where
Gender = case when @filterby = 'Gender' then
@filtervalue else Gender end
and
MaritalStatus = case when @filterby = 'Marital Status' then
@filtervalue else MaritalStatus end
end
go
The WHERE clause in FilterCustomerList utilizes a case statement that compares a column to the filter value passed in as a parameter (if that was the filter by option) or to itself (if the filter by option is on a different column). This simplifies the WHERE clause. The dbo.DimCustomer table is in the AdventureWorksDW database which you can download from here.
We can run the following script to test our stored procedures:
exec dbo.GetCustomerFilterOptions
exec dbo.GetFilterChoiceList 'Gender'
exec dbo.FilterCustomerList 'Gender', 'M'
The results returned from the above script are shown below:
The above results show the list of filter options, the filter choice list for Gender, and the customers where Gender is Male (M).
Create the Report
The following steps show creating the report using Business Intelligence Development Studio (BIDS) 2005 where the report design surface contains the following tabs:
The Data tab is used to create datasets (select New Dataset from the dropdown list on the Data tab), the Layout tab is used to specify the report layout, and the preview tab allows you to run the report within BIDS. The annotations on the above screen shot call out the buttons on the data tab that you can click to execute the query (or stored procedure) and refresh the field list; i.e. the columns returned. You should execute the query (or stored procedure) in order to make sure that the result set columns are available in the dataset.
At a high level we need to perform the following steps:
- Create datasets to populate the report parameter dropdown lists and create a dataset to return the filtered list of customers
- Configure the report parameters to populate the dropdown lists based on the datasets
- Layout the report
Create Datasets
Create a new dataset that will be used to populate the first report parameter dropdown list; specify the command type as StoredProcedure, enter the stored procedure name GetCustomerFilterOptions as the query string:
Create a new dataset that will be used to populate the second report parameter dropdown list; specify the command type as StoredProcedure, enter the stored procedure name GetFilterChoiceList as the query string:
Create a new dataset that will be used to query the DimCustomer table based on the report parameters; specify the command type as StoredProcedure, enter the stored procedure name FilterCustomerList as the query string:
If you follow a consistent naming convention for stored procedure parameters, the Parameters tab (shown in the Dataset screen shots above) will get filled in automatically and will match the report parameters (discussed in the next section). Occasionally you will have to manually edit the Parameters tab or the Report Parameters (discussed below) if you are not consistent with naming.
Configure Report Parameters
In this step we will specify the dataset to use to populate each report parameter dropdown list. Click Report on the top-level menu in BIDS, then select Report Parameters (you may have to click on the Layout tab in order to see the Report top-level menu). You will see the Report Parameters dialog as shown below:
Select filterby in the Parameters listbox and specify the Available values as shown below:
Select filtervalue in the Parameters listbox and specify the Available values as shown below:
Layout the Report
Click on the Layout tab, drag the Table report item from the Toolbox onto the layout design surface, then fill in the columns as shown below by dragging them from the CustomerQuery dataset and dropping them onto the table:
Run the Report
Click the Preview tab, select a value from the filterby and filtervalue dropdown lists, then click View Report and you will see the following:
Next Steps
- Think about the functions and stored procedures that are required to retrieve the data you need for your reports. You will likely find that creating these database objects then using them in a report is easier than embedding the SQL commands in the report. In addition you can use the functions and stored procedures in other reports.
- Download the sample code from this tip and experiment on your own.
