Using Multi-Select in SSRS

Posted on January 15, 2011

4



One of my pet peeves is when developers try to put business logic into the actual report. I strongly believe that any business logic should reside in a stored procedure, and that stored procedure is then called by the report and the results displayed.

There are many benefits to using stored procedures as the source of the main data set within SSRS. These include:

1) Stored procedures allow the use of certain objects that cannot be used in embedded T-SQL in the report such as temp tables and indexes specific to those temp tables as well as table variables, improving both response times and readability

2) Stored procedures provide a layer of abstraction between the report and the business logic

3) Stored procedures allow re-use of similar logic

4) IMO the most important reason, stored procedures allow query plan reuse, thus improving response times.

An issue arises however with the use of the Multi-select option for prompts within SSRS. When using SSRS and multi-select, the first option is to allow (Select All), as shown below:

When a user selects (Select All) , a list of all countries in a delimited string is returned e.g. ‘AU,CA,DE,FR,GB,US’

This is fine with a small set of records, but will cause problems if you have a dimension with a very large record set, both with performance and the ability to store all members in a 8000 byte field if you have hundreds of thousands of dimension members. What would be more ideal is to return “ALL” to the stored procedure used to produce the result set for the report.

This document will describe a work-around for this situation. Note that if a user is dumb enough to click on (Select All), then deselects one value in a very large prompt list, this may still break the report.

Creating the DataSets

The query for our CountryPrompt dataset is:

 SELECT DISTINCT CountryRegionCode AS Country
FROM dbo.DimGeography
ORDER BY Country

In addition to our CountryPrompt dataset we will create another dataset called CntryCnt with the following query:

SELECT COUNT(DISTINCT CountryRegionCode) AS Cnt
FROM dbo.DimGeography

Next we create a dataset for our main result set. To do this we reference the stored procedure we’ve created. The stored procedure we will be calling works against AdventureWorks2008R2:

CREATE PROCEDURE spr_CountrySales(
   @CountryCode VARCHAR(50)
 , @Debug BIT = 0)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Sql  NVARCHAR(MAX)
DECLARE @Paramlist  NVARCHAR(1000)
SET @Sql =
 N' SELECT g.CountryRegionCode
, SUM(f.SalesAmount ) AS SalesAmount
  FROM dbo.FactInternetSales f
  INNER JOIN dbo.DimCustomer c
  ON c.CustomerKey = f.CustomerKey
  INNER JOIN dbo.DimGeography G
  ON g.GeographyKey = c.GeographyKey '
  + CASE WHEN @CountryCode <> 'ALL' THEN
   ' WHERE CountryRegionCode IN (' + @CountryCode + ') '
  ELSE '' END
  + ' GROUP BY CountryRegionCode
  ORDER BY CountryRegionCode '

  IF @Debug = 1 PRINT @Sql 
  EXEC sp_executesql @sql
END

Next we create the dataset within SSRS:

Creating the Parameters

The first parameter we create is pCountry to provide the initial drop-down list, using the CountryPrompt dataset. We want to click on the radio button “Allow multiple values”

  

 

 

 

  

We then use the CountryPrompt dataset as the source for available values:

In addition to our pCountry parameter, we will create a parameter called pCntryCnt that references the CntryCnt dataset. Note that we’ve selected “Internal” for parameter visibility.

Because we are essentially using this parameter as a variable, we keep “None” as the Available Values option. We then click on the Default Values tab and use our CntryCnt dataset as the source for the query:

 

 

 

 

 

 

 

 

 

 

We then click on our Main dataset and select the Parameters tab. We enter the stored procedure parameter name, in this case @CountryCode, and select the function button, where we enter this statement:

=iif(Parameters!pCountry.Count=Parameters!pCntryCnt.Value, “ALL”, join(Parameters!pCountry.Label, “,”))

This statement compares the count of the elements selected in the drop down list. If that equals the total value of the countries from CntryCnt, then “All” is returned to the stored procedure. If not, then the vales selected are returned.

Advertisements
Posted in: SQL, SSRS