Using a multi-value parameters in SSRS reports is giving the users the flexibility to filter on one or more values. In the following example they can filter on one or more products and on one or more customers.
SSRS is passing the parameters to the stored procedure as joined strings. This means that it is very important to use a “safe” delimiter. Comma (,) or semicolumn (;) are not good enough, because they may exist in the filtered column (we may filter on column Part and a part ABC,DEF is an existing one… :-(). Of cource, the best practice it to filter on “Value-Label” pair where “Value” is the ID column of the structure table, but not always the data is clean enough.
I use CHAR(164) –> ¤ as a delimiter. It is not on the keyboard and most likely doesn’t exist as a data.
Datasets for the SSRS parameters.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
USE AdventureWorks2008R2; GO -- Product SELECT DISTINCT ProductID AS [Value], Name AS [Label] FROM Production.Product ORDER BY [Label]; GO -- Customer SELECT DISTINCT BusinessEntityID AS [Value], FirstName + ' ' + LastName AS [Label] FROM Person.Person ORDER BY [Label]; GO |
This example is just a preview of the logic that is using a dynamic SQL to convert the SSRS multi-value parameter to IN statement.
I will expand the code to check if the passed values are “ALL” and if “ALL”, the parameter will not be included in the WHERE clause.
Create database
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
USE [master]; GO IF (DB_ID('PL') IS NOT NULL) BEGIN ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE PL; END GO CREATE DATABASE PL; GO USE PL; GO |
Create stored procedure
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Peter Lalovsky -- Create date: 2014-12-23 -- Description: Example to convert SSRS multi-value parameter in -- ============================================= CREATE PROCEDURE usp_SSRSMultiValueParameters ( @Product VARCHAR(MAX) -- multi-value, delimited with CHAR(164) --> ¤ , @Customer VARCHAR(MAX) -- multi-value, delimited with CHAR(164) --> ¤ , @DueDateStart DATETIME -- single-value , @DueDateEnd DATETIME -- single-value ) AS BEGIN -- pl.usp_SSRSMultiValueParameters SET NOCOUNT ON; --** Parameters - Start **-- SELECT @Product = REPLACE(@Product, CHAR(164), CHAR(44)) -- ¤ --> , , @Customer = REPLACE(@Customer, CHAR(164), CHAR(44)); --** Parameters - End **-- --** Get the report - Start **-- DECLARE @SQL VARCHAR(MAX); SET @SQL = ' SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT SOH.SalesOrderNumber , SOH.OrderDate , SOH.DueDate , Pe.FirstName + '' '' + LastName AS CustomerName , Pr.Name AS ProductName , SOD.OrderQty , SOD.LineTotal FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS SOH JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID JOIN AdventureWorks2008R2.Production.Product AS Pr ON SOD.ProductID = Pr.ProductID JOIN AdventureWorks2008R2.Sales.Customer AS C ON SOH.CustomerID = C.CustomerID JOIN AdventureWorks2008R2.Person.Person AS Pe ON C.PersonID = Pe.BusinessEntityID WHERE SOD.ProductID IN (' + @Product + ') AND Pe.BusinessEntityID IN (' + @Customer + ') AND SOH.DueDate BETWEEN ''' + CONVERT(CHAR(10), @DueDateStart, 120) + ''' AND ''' + CONVERT(CHAR(10), @DueDateEnd, 120) + ''' ORDER BY SOH.DueDate , SOH.SalesOrderNumber , Pr.Name '; --PRINT @SQL EXEC (@SQL); --** Get the report - End **-- END -- pl.usp_SSRSMultiValueParameters GO |
Test stored procedure
1 2 3 4 5 6 |
EXEC usp_SSRSMultiValueParameters @Product = '988¤712¤880' , @Customer = '8034¤13577¤641' , @DueDateStart = '2007-03-01' , @DueDateEnd = '2007-09-30'; GO |
Delete database
1 2 3 4 5 6 7 8 9 |
USE [master]; GO IF (DB_ID('PL') IS NOT NULL) BEGIN ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE PL; END GO |
udf_SplitString() is a great resource when CHAR(164) –> ¤ is not a good delimiter.
Keep it simple :-)