mssqltips logo

Optimize Report Parameter Dropdowns in SQL Server Reporting Services

By:   |   Updated: 2009-12-18   |   Comments (5)   |   Related: > Reporting Services Parameters

Problem
We have many SQL Server Reporting Services (SSRS) 2005 reports that use our OLTP systems as data sources.  These are legacy OLTP systems purchased from a variety of vendors.  We do not have the luxury of reporting from a data warehouse.  Reports typically have many parameters allowing the users to choose the values they want from dropdown lists.  Many of the same parameters are used on every report.  The users are complaining about the time it takes just to select the parameters and get a report running.  Do you have any ideas on how we can get the parameter lists populated faster?   In this tip I will cover a few techniques to handle this.

Solution
There are two issues that contribute to the slowness that the users are experiencing when the report parameter dropdowns are being populated.  First when you have parameters lists that are filtered based on the selection in a previous parameter, this causes a postback and all parameter lists are refreshed whether they need it or not.  You can confirm this behavior by running the SQL Server Profiler while selecting parameter values in the dropdown lists.  You will see the report parameter lists being refreshed via calls to the SQL statement or stored procedure you specified in a particular Report Dataset that you are using to supply the list of available values for a report parameter.  Unfortunately this is the behavior in SSRS 2005.  SSRS 2008 allows you to choose the behavior you want for each report parameter's available values; you can select automatically determine when to refresh, always refresh, or never refresh.  This new feature in SSRS 2008 will at least stop refreshing parameter lists that are not affected by selections in other parameter lists.

The second issue is that when the data source is an OLTP system, the report parameter dropdowns may be populated by queries that are not supported by the proper indexes and/or they are using SELECT DISTINCT on very large tables to get the list of available values for a dropdown.  With many users running reports concurrently, you have queries running that are reading much more data than they should.  Your queries may be executing table scans on very large tables.  In any event you will have users complaining about how long it takes to populate the report parameter dropdowns.

When it comes to populating dropdowns for the report parameter available values in the most efficient way possible, it is a good practice to create lookup tables that contain just the values you want in the dropdown lists.  There are several advantages to working with lookup tables such as:

  • You can create a primary key that is clustered to match the order that you want the items to appear in the dropdown; this eliminates sorting.  Sometimes the order isn't just a sort on a particular column; e.g. you want United States as the first item in the Country drop down, followed by Canada and Mexico, then the rest of the countries.
  • The lookup table can have just a couple of columns such as sort order, label and value (label and value are the columns you need to populate the available values).  The narrow row size makes the query that much faster, compared to a table in the OLTP system which may have in excess of 100 columns.
  • You can create stored procedures to populate the dropdowns from the lookup tables rather than duplicating all sorts of SQL queries in many reports.
  • Even if you are reporting from a data warehouse, using lookup tables provides faster queries when you have type-2 slowly changing dimensions that may require you to use SELECT DISTINCT to populate a dropdown list.

The one obvious disadvantage to lookup tables is that they have to be maintained.  However, the average lookup table contains data that doesn't change often so this is not a big concern.  In the case of a data warehouse, the lookup table maintenance can easily be integrated into the normal ETL processing.

Let's walk through some code samples for creating and working with lookup tables.  For the sake of simplicity I will base these examples on the Adventure Works database that came with SQL Server 2005 (navigate to this link on CodePlex to download the database; look for the AdventureWorksDB.msi hyperlink).

Lookup Table Design

Let's design two lookup tables; one for Territory Group and another for Territory.  These are based on the Sales.SalesTerritory table in AdventureWorks.  The requirement for Territory Group is that North America be first, followed by the other groups in alphabetical order.  The basic lookup table has the following schema and primary key:

CREATE TABLE dbo.TerritoryGroupLookup
(
 SortOrder  int not null
, ParamLabel  varchar(50) not null
, ParamValue  int identity not null
);
ALTER TABLE dbo.TerritoryGroupLookup
 ADD CONSTRAINT PK_TerritoryGroupLookup
 PRIMARY KEY CLUSTERED (SortOrder)

Note that the primary key is clustered and is on the SortOrder column rather than the ParamValue column.  When we do a SELECT from the TerritoryGroupLookup we generally want the rows to be ordered by SortOrder; the clustered primary key will do this automatically.  Ordinarily the ParamValue would match an existing value in the underlying table and it wouldn't be an identity.  However, in this case there isn't one since the Group appears multiple times in the Sales.SalesTerritory table. 

We will use the following T-SQL script to populate the TerritoryGroupLookup table:

;WITH CTE AS (
 SELECT DISTINCT [Group]
 FROM Sales.SalesTerritory
 WHERE [Group] <> 'North America'
)
INSERT INTO dbo.TerritoryGroupLookup (
 SortOrder
, ParamLabel
)
SELECT TOP 1 0, [Group]
FROM Sales.SalesTerritory
WHERE [Group] = 'North America'
UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY [Group]), [Group]
FROM CTE                            

This satisfies our requirement that North America be first, followed by the other groups in alphabetical order.  We assign the SortOrder of zero to the North America group, then use the ROW_NUMBER() function to assign sequential values to the remaining groups. When we query the TerritoryGroupLookup using SELECT * FROM dbo.TerritoryGroupLookup we get the following output, in the correct order:

Our second lookup table will have the following schema and primary key:

CREATE TABLE dbo.TerritoryLookup
(
 SortOrder  int not null
, ParamLabel  varchar(50) not null
, ParamValue  int not null
, TerritoryGroup int not null
);
ALTER TABLE [dbo].[TerritoryLookup]
 ADD CONSTRAINT [PK_TerritoryLookup]
 PRIMARY KEY CLUSTERED (TerritoryGroup, SortOrder)
                        

Note in this case we have a TerritoryGroup column.  This allows us to filter the TerritoryLookup based on a selection from the TerritoryGroupLookup.  The primary key is made up of the TerritoryGroup and the SortOrder columns; we will always be selecting the TerritoryLookup rows for a particular TerritoryGroup.

We will use the following T-SQL script to populate the TerritoryLookup table:

INSERT INTO dbo.TerritoryLookup (
 SortOrder
, ParamLabel
, ParamValue
, TerritoryGroup
)
SELECT 
  ROW_NUMBER() OVER (ORDER BY g.SortOrder, t.[Name])
, t.[Name]
, t.TerritoryID
, g.ParamValue
FROM dbo.TerritoryGroupLookup g
JOIN Sales.SalesTerritory t ON t.[Group] = g.ParamLabel                

The above script puts everything in order by the TerritoryGroup then alphabetically by Territory name.

Remember that the idea of the lookup table is to make populating the report parameter dropdowns as fast as possible.  In the above examples the Sales.SalesTerritory table in the OLTP database only has a few rows and a small number of columns.  However, this isn't always the case.

Populating Dropdown Lists

Now that we have created our lookup tables, we could create stored procedures that we can execute to populate the available values in the report parameters.  I usually name these using the convention of lookup table name and append PickList to that.  For example:

CREATE PROCEDURE dbo.TerritoryGroupPickList
AS 
BEGIN
 SET NOCOUNT ON;
 SELECT ParamLabel, ParamValue 
 FROM dbo.TerritoryGroupLookup
END

CREATE PROCEDURE dbo.TerritoryPickList
@TerritoryGroup int
AS 
BEGIN
 SET NOCOUNT ON;
 SELECT ParamLabel, ParamValue 
 FROM dbo.TerritoryLookup
 WHERE TerritoryGroup = @TerritoryGroup
END
               

Note that the TerritoryPickList procedure takes the TerritoryGroup as a parameter; we always select a TerritoryGroup then a Territory within that group in our report parameter dropdowns.  You could argue that creating these stored procedures is overkill.  However, my opinion is that as the number of reports grows, it's much better to have each one of them calling a stored procedure to populate a dropdown list so that if something about how the list is populated changes, you can theoretically change the stored procedure once and have all your reports working correctly.

Sample Report

As a final step we will add parameters to a report that take advantage of the lookup tables and stored procedures to populate the dropdowns.  The steps involved are:

  • Create report datasets that call the stored procedures to populate the dropdowns
  • Create report parameters that use the report datasets to filter the list of available values
  • Create a  report dataset that uses the report parameters

Create a report dataset for our two parameter dropdown lists by going to the Data tab in the report designer, select New Dataset in the Dataset dropdown, and filling in the dialog as follows:

The report designer will automatically fill in the Parameters tab and create the report parameter for you for the TerritoryPickList dataset (this happens after you click OK on the Query tab):

Click on the Layout tab in the report designer, then select Report, Report Parameters from the top-level menu.  You will see the Report Parameters dialog; fill in the Available values section as shown below:

Click the Add button on the Report Parameters dialog and add the Territory parameter; fill in the Available values section as shown below:

Next we will create a stored procedure that takes a Territory parameter and produces a result set that we can use to render a report:

CREATE PROCEDURE dbo.TerritoryReport
@Territory int
AS
BEGIN
 SET NOCOUNT ON;
 SELECT s.* 
 FROM Sales.SalesOrderHeader s
 JOIN Sales.SalesTerritory t ON t.TerritoryID = s.TerritoryID
 WHERE t.TerritoryID = @Territory
END
   

Add a report dataset that uses the above stored procedure, add a Table to the report layout, and finally drag/drop some columns onto it from the dataset to produce the following report:

Next Steps



Last Updated: 2009-12-18


get scripts

next tip button



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners 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
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, November 20, 2013 - 10:55:07 AM - Raymond Barley Back To Top

I went back to a SQL server 2005 report, added a multi-value parameter, created a linked report and I see the same behavior.

Here is a possible work around:

- add a hidden parameter to the original report with a default value; e.g. IS_LINKED_REPORT = 'N'

- in the linked report specify IS_LINKED_REPORT = 'Y'

- add a hidden multi-value parameter to the original report and hard-code the static values

- use a case statement in the WHERE clause of your dataset query to use the hidden multi-value parameter when you are running the linked report

 


Tuesday, November 19, 2013 - 2:59:27 PM - venubabu Back To Top

Hi,

I have a created an SSRS report in 2005 version and it has a one multi-valued parameter and has a default value which is a query based value. The values in the parameter are like USA, Canada, India, Sing...

I would like to create a linked report for the above mentioned report and wound need to pass only two hard coded values (USA, Sing). But when I go to the report parameters of the linked report, it was showing 'Query Based' at the 'default value' section.

I need to pass hard coded values to the linked report.

Can any one please suggest how i can create a linked report with static values in the query based parameter?

Any help would be greatly appreciated.

Thank you,
Venu Babu.


Thursday, March 31, 2011 - 11:06:47 PM - Ray Barley Back To Top

The way to get to the bottom of this is to capture the query plan using SQL Server Profiler. This tip discusses how to capture the graphical query plan in profiler: http://www.mssqltips.com/tip.asp?tip=1264

Once you have the plan you can determine where the execution time is being spent.  A good resource for analyzing plans is Grant Fritchey's book: http://www.sqlservercentral.com/articles/books/65831/   


Thursday, March 31, 2011 - 2:50:17 PM - Partha Mandayam Back To Top

It's a great tip.

I have a question. If the sql to populate the dropdowns executes fast in sql server management studio, why does it take so long in SSRS?


Friday, December 18, 2009 - 10:01:09 AM - rfisch Back To Top

Ray, Great tip!

 Rob Fisch



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools