Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Creating Dynamic Report Filters in SQL Server Reporting Services SSRS


By:   |   Read Comments (14)   |   Related Tips: > Reporting Services Dynamic Reports

Quickly Resolve Performance Problems for IIS, .NET and SQL Server       >>>   Get Started


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 the Code Plex site.

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.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, September 23, 2014 - 9:24:35 AM - Raymond Barley Back To Top

There isn't a way to embed a function within a dataset.  Using functions or stored procedures in the database is the best option I think.


Tuesday, September 23, 2014 - 2:08:15 AM - Emma Back To Top

Hi Ray, I'm also a newbie for SSRS. Is it possible to create Function in an embedded dataset and continue without create stored procedure? thank you.

 

 


Thursday, March 13, 2014 - 12:57:46 PM - Raymond Barley Back To Top

The stored procedures and functions are created in the SQL Server database.  Typically you use the object explorer in SQL Server Management Studio, you connect to a particular SQL Server instance, select a database, click the New Query button, and enter the create procedure or create function statement.


Thursday, March 13, 2014 - 12:15:46 PM - Rick Back To Top

Ray,

I am a newbie to SSRS and I am trying to create dynamic filters.  I read through your post and it sounds like it will work for my needs.  However, I don't know where I write and store the functions and stored procedures.  Could you or someone else let me know how I go about doing this?

Thank you,

Rick

 


Saturday, June 08, 2013 - 8:00:50 AM - Chander Sharma Back To Top

Hi,

You can view an easy example on creating a parameterized report in ssrs - http://www.phpring.com/creating-a-parameterized-report-in-ssrs/


Tuesday, July 26, 2011 - 4:36:03 PM - Denise Back To Top

Sorry, didn't read the directions about pasting code. Here it is again in an easier-to-read format:

where ee_erid= @erid
and exists(select Item from dbo.SSRSSplit(@filter_Value, ',')
where case
 when @filterby = 'Location' then loc_name
 when @filterby = 'HRIS System' then hrissys_name
 when @filterby = 'Paysite' then paysite_name
 when @filterby = 'Employment Status' then eed_employment_status
 when @filterby = 'Organization' then org_name
 when @filterby = 'Job Class' then job_name
 when @filterby = 'Department' then dept_name
 when @filterby = 'Division' then div_name
 when @filterby = 'Union Indicator' then eed_union_code
 when @filterby = 'Union Code' then uc_unioncode
 when @filterby = 'Retirement Code' then rc_name
 when @filterby = 'UDF 1' then eed_user_def_0
 when @filterby = 'UDF 2' then eed_user_def_1
 when @filterby = 'UDF 3' then eed_user_def_2
 when @filterby = 'UDF 4' then eed_user_def_3
 when @filterby = 'UDF 5' then eed_user_def_4
 when @filterby = 'UDF 6' then eed_user_def_5
 when @filterby = 'UDF 7' then eed_user_def_6
 when @filterby = 'UDF 8' then eed_user_def_7
 when @filterby = 'UDF 9' then eed_user_def_8
 when @filterby = 'UDF 11' then eed_user_def_9
 end = Item)


Tuesday, July 26, 2011 - 4:33:06 PM - Denise Back To Top

I just wanted to provide an update since we figured out how to use the split function within the where clause of my base query for the optional dynamic filters. Hopefully this will save someone else a lot of headaches trying to get the syntax just right. 

My split function name is dbo.SSRSSplit, I copied it directly from the link Ray gave in his response above and didn't change anything other than the name.

I wrote my where clause like this (the issue I was having previously with null values breaking the whole query wasn't an issue with the new logic so I was able to delete that):

 

where

 

ee_erid= @erid

andexists(select Item from dbo.SSRSSplit(@filter_Value,',')

 

where

 

case when @filterby ='Location'then loc_name

when @filterby ='HRIS System'then hrissys_namewhen @filterby ='Paysite'then paysite_namewhen @filterby ='Employment Status'then eed_employment_statuswhen @filterby ='Organization'then org_namewhen @filterby ='Job Class'then job_namewhen @filterby ='Department'then dept_namewhen @filterby ='Division'then div_name when @filterby ='Union Indicator'then eed_union_codewhen @filterby ='Union Code'then uc_unioncodewhen @filterby ='Retirement Code'then rc_nameend= Item)

 

 


Thursday, July 21, 2011 - 2:27:44 PM - Denise Back To Top

Hi, Ray. Thanks for your quick reply! I really appreciate the help.

I have not worked with the split function before and I'm still a bit confused, though. First, I've never had a problem with sprocs not accepting multi-valued parameters when I'm using them in SSRS. As long as I use  "in(@parameter)" in the sproc it worked in SSRS. True, when it passes it to SQL Server, I understand it changes it to dynamic sql, but that hasn't affected me on the user interface side at all.

My base query for the report that has the optional filters is not a sproc, however, it's just straight sql in an SSRS dataset. The sprocs I was referring to were the sprocs I built for the optional filters that you outlined in your article...GetReportFilterOptions and GetReportFilterChoiceList. And then I have a function for each list I need to generate depending on what they selected for their filter.

But where, in all that, do I use this split or the in operator?  As I mentioned previously, I see no other place but in the where clause of my base query.  Below, I've pasted the whole were clause. @erid will remain single choice and is not part of the optional filters, but all the rest of the where clause is. Can you specifically tell me how I would use a split function in here, bearing it mind I have to keep the isnull logic for where the client isn't using that column to store any data.

where ee_erid= @erid
and isnull(loc_name,'') = isnull(case when @filterby = 'Location' then @filter_Value else loc_name end,'')
and isnull(hrissys_name,'') = isnull(case when @filterby = 'HRIS System' then @filter_Value else hrissys_name end,'')
and isnull(paysite_name,'') = isnull(case when @filterby = 'Paysite' then @filter_Value else paysite_name end,'')
and isnull(eed_employment_status,'') = isnull(case when @filterby = 'Employment Status' then @filter_Value else eed_employment_Status end,'')
and isnull(org_name,'') = isnull(case when @filterby = 'Organization' then @filter_Value else org_name end,'')
and isnull(job_name,'') = isnull(case when @filterby = 'Job Class' then @filter_Value else job_name end,'')
and isnull(dept_name,'') = isnull(case when @filterby = 'Department' then @filter_Value else dept_name end,'')
and isnull(div_name,'')  = isnull(case when @filterby = 'Division' then @filter_Value else div_name end,'')
and isnull(eed_union_code,'') = isnull(case when @filterby = 'Union Indicator' then @filter_Value else eed_union_code end,'')
and isnull(uc_unioncode,'') = isnull(case when @filterby = 'Union Code' then @filter_Value else uc_unioncode end,'')
and isnull(rc_name,'') = isnull(case when @filterby = 'Retirement Code' then @filter_Value else rc_name end,'')

 


Thursday, July 21, 2011 - 12:56:50 PM - Ray Barley Back To Top

With SSRS 2005 a multi-valued parameter would be passed to your stored

proc as a comma delimited list.  In your Dataset parameters tab you would

specify an expression like =Join(Parameters!PARAMETERNAME.Value, ",") for the 

parameter value to generate the comma delimited list.

 

In your stored proc you would use a split function like this:

 

WHERE COLUMN_NAME IN (SELECT X FROM dbo.SPLIT(@PARAMETER_NAME, ',')

 

You can find many examples of SPLIT functions; here is one that a colleague

wrote and I've used: http://bisqlserver.rdacorp.com/2007/02/using-multi-value-parameters-in-stored.html

 

I'm not sure if SQL Server 2008+ has changed any of this.


Thursday, July 21, 2011 - 11:18:58 AM - Denise Back To Top

This article is fabulous and I've successfully implemented optional filters in my report and have been using them for some time. However, I now need the filters to allow multi-select, rather than single-choice. I don't see anywhere in the functions or stored procedures where I could implement an 'in' operator, rather than an '=" operator. I strongly suspect I need to do it in the where clause of my core query. But, nothing I've tried has worked.  For instance, here is one of my optional filters in my where clause. (I had to add the isnull to the base example given in this article because the whole query was failing if the client we were running the report for wasn't using the particular field to store any values.)

and isnull(loc_name,'') = isnull(case when @filterby = 'Location' then @filter_Value else loc_name end,'')

Is this where I need to somehow to implement the 'in' operator? Or did I miss something when looking through the sproc and udf code?


Thursday, December 30, 2010 - 1:38:47 PM - Denise Back To Top

Actually, never mind. I figured it out. In case anyone else wants to know:

I created another UDF called GetUnFilteredList where I set the filter label to 'All' and the filter value to 'null,

Then, added union all select 'all', 'all' to the filter options sproc.

Then added when 'all' then N'select FILTER_LABEL, FILTER_VALUE from dbo.GetUnFilteredList()' to the choice list sproc.

I didn't need to do anything to my base query.

 


Thursday, December 30, 2010 - 1:19:03 PM - Denise Back To Top

Thank you for this article. It was extremely helpful. I was previously using expressions in the report itself for dynamic report filters, which worked well, except that it blows up if I try to program more than three filter options.

I'm trying to figure out how to modify my report so that the user can choose to not select a filter. I suspect it's similar to what you replied to jwmiles' question, but not exactly.

Would I need to modify the stored procedures and the UDFs, or just the where clause of my base query?

For example, here is the current where clause of the base query that the report uses. How can I make the dynamic parameter optional, so they  can bring back the entire report without filtering on any of the options below?

where loc_name = case when @filterby = 'Location' then @filter_Value else loc_name end
and hrissys_name = case when @filterby = 'HRIS System' then @filter_Value else hrissys_name end
and paysite_name = case when @filterby = 'Paysite' then @filter_Value else paysite_name end
and eed_employment_status = case when @filterby = 'Employment Status' then @filter_Value else eed_employment_Status end
and org_name = case when @filterby = 'Organization' then @filter_Value else org_name end
and job_name = case when @filterby = 'Job Class' then @filter_Value else job_name end


Thursday, May 13, 2010 - 11:15:07 AM - raybarley Back To Top

Maybe this will help.  Here's an example where you can use case in the where clause to compare a column to a value that a user entered (or selected from a dropdown).  The point is that you have to have some value to represent that the user selected ALL or didn't make a selection or entry, in which case you don't want to actually filter on that column.

create table dbo.Project (
project_id varchar(50),
project_name varchar(50),
project_type varchar(50)
)

insert into dbo.Project values ('sample', 'sample project', 'infrastructure')

declare @project_id varchar(50)
set @project_id = 'ALL'
select * from dbo.Project
where project_id = case when @project_id = 'ALL' then project_id else @project_id end
 

Set @project_id to a value and the where clause compares project_id to @project_id; set @project_id to 'ALL' and the where clause simply compares project_id to itself which is always true

 


Thursday, May 13, 2010 - 10:18:10 AM - jwmiles Back To Top

Hopefully this response is seen. I've used this tip with great success, but now I'd like to modify it slightly and am having some trouble.

I'm setting up 3 filters:

Project #
Project Director
Department

The last two filtervalue options are pulled dynamically from the database, however I'd like the user be able to specify the Project # they wish to filter by if that filterby option is selected. Basically, a textbox instead of a drop down.

I've tried modifying the stored procedure to say

when 'Project #' then

N'select bmproj.cproject AS FILTER_LABEL, bmproj.cproject AS FILTER_VALUE from dbo.bmproj WHERE (bmproj.cproject = @proj)'

However this produces an error about forward dependancies with the @proj variable being used in the filtervalue variable.

Does anyone know of a way of doing this?

Thanks.


Learn more about SQL Server tools