SSRS Parameter to Stored Procedure

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.

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

USE AdventureWorks2008R2;

-- Product

SELECT DISTINCT ProductID AS [Value], Name AS [Label]

FROM Production.Product

ORDER BY [Label];

-- Customer

SELECT DISTINCT BusinessEntityID AS [Value], FirstName + ' ' + LastName AS [Label]

FROM Person.Person

ORDER BY [Label];

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

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

USE [master];

IF (DB_ID('PL') IS NOT NULL)

BEGIN

ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE PL;

END

CREATE DATABASE PL;

USE PL;

Create stored procedure

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

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

)

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

Test stored procedure

EXEC usp_SSRSMultiValueParameters
	@Product = '988¤712¤880'
	, @Customer = '8034¤13577¤641'
	, @DueDateStart = '2007-03-01'
	, @DueDateEnd = '2007-09-30';
GO

EXEC usp_SSRSMultiValueParameters

@Product = '988¤712¤880'

, @Customer = '8034¤13577¤641'

, @DueDateStart = '2007-03-01'

, @DueDateEnd = '2007-09-30';

Delete database

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

USE [master];

IF (DB_ID('PL') IS NOT NULL)

BEGIN

ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE PL;

END

udf_SplitString() is a great resource when CHAR(164) –> ¤ is not a good delimiter.

Keep it simple :-)