Optimize Report Parameter Dropdowns in SSRS

By: Ray Barley | 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:

territorygrouplookup output

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:

territorygroupdataset

territorydataset

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):

territorydatasetparameters

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:

territorygroupavailablevalues

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

territoryavailablevalues

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:

report output

Next Steps

Optimization sometimes requires relatively simple changes to make your queries execute faster. In this case adding lookup tables is a relatively easy thing to do that can have a big impact on performance.
You can download the sample report and use it as a basis for your own reports.
Review these related tips:

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2009-12-18

Optimize Report Parameter Dropdowns in SQL Server Reporting Services

Problem

Solution

Next Steps

About the author

Comments For This Article