SSRS Gotcha: Multi-value Parameter Design Patterns

Adding a Multi-value parameter to a report is so easy in SSRS.  If you only want one.  If you want more than one things get a whole lot more complicated.  For example, inline SQL is no longer even an option as far as I can tell.  You must create a stored procedure to handle the request.

Recently, I had the pleasure of assisting a cohort with the following dilemma: a stored procedure would serve the correct results when run as inline SQL; (i.e. commenting out the create procedure code) but, incorrect results were retrieved when the stored procedure was executed through SSRS.

I ran a profiler trace and it appeared at first glance as if the SSRS engine was sending the same parameters as the inline SQL.  Example: exec rpt_someSillyCode @variable=N’this,that,the other’

Notice the N?  I think that means NVarchar.

Turns out this situation was caused by the fact that in his procedure declaration the variable lengths were defined as 10 characters; but, when he ran the procedure as inline SQL his test variables were defined as 20 characters.

The whole ordeal reminded me of the fact that multi-value parameters can be a real pain so I wanted to document what I know.

There are at least three different design patterns (that I know about) when dealing with multi-value parameters in SSRS.

——————————————————————————-

Pattern #1, Dynamic SQL:

Example:

SET @variable = CHAR(39) + replace(@variable, ',', CHAR(39) + ',' + CHAR(39)) + CHAR(39)
--@variable is a multi-value parameter sent from SSRS
SET @strSQL = 
	'Select SUM(stuff) as StuffSum  
     into #ReportTempTable
     from #ADifferentTempTable
     Where VariableColumn  IN (' 
	+ @variable + ')';

--print @strSQL
EXECUTE (@strSQL)

——————————————————————————-

Design Pattern #2, Use a function to parse the multi-value:

CREATE FUNCTION SplitParameterValues (
	@InputString NVARCHAR(max)
	,@SplitChar CHAR(5)
	)
RETURNS @ValuesList TABLE (param NVARCHAR(255))
AS
BEGIN
	DECLARE @ListValue NVARCHAR(max)

	SET @InputString = @InputString + ','

	WHILE @InputString <> ''
	BEGIN
		SELECT @ListValue = SUBSTRING(@InputString, 1, CHARINDEX(',', @InputString) - 1)

		INSERT INTO @ValuesList
		SELECT @ListValue

		SELECT @InputString = SUBSTRING(@InputString, CHARINDEX(',', @InputString) + 1, 
				LEN(@InputString) - CHARINDEX(',', @InputString))
	END

	RETURN
END

——————————————————————————-

Design Pattern #3, Parse the multi-value parameters into a #temp table and join to it

This is done in a similar fashion to the function.  Except the parsing is done right in the stored procedure.  This can be used in conjunction with an ‘All’ member.

——————————————————————————-

Click here to download an SSRS solution and related SQL that demonstrates all three design patterns.

——————————————————————————-

The whole multi-value parameter thing can be a huge problem once you dig into it.  Check the links below for the pages that really helped me see the light.  Many think Microsoft should have given developers the option to hide the ‘select all’ option and provide our own ‘all’ solution (see this connect link.) This would prevent SSRS from sending a huge string of values that basically meant all of my values separted by columns as opposed to sending just the word ‘everything.’  These clever technicians have devised a multitude of ways around this.

Related Links:

How to Default to ALL in an SSRS Multi-select Parameter

http://dataqueen.unlimitedviz.com/2011/06/how-to-default-to-all-in-an-ssrs-multi-select-parameter/

Using Multi-Select in SSRS

http://jsimonbi.wordpress.com/2011/01/15/using-multi-select-with-stored-procedures/

Passing multi-value parameter in stored procedure (SSRS report)

http://munishbansal.wordpress.com/2008/12/29/passing-multi-value-parameter-in-stored-procedure-ssrs-report/

——————————————————————————-

Create a Table valued function in SQL Functions as below

Step 1

CREATE FUNCTION [dbo].[FnSplit] (
	@List NVARCHAR(2000)
	,@SplitOn NVARCHAR(5)
	)
RETURNS @RtnValue TABLE (
	Id INT identity(1, 1)
	,Value NVARCHAR(100)
	)
AS
BEGIN
	WHILE (Charindex(@SplitOn, @List) > 0)
	BEGIN
		INSERT INTO @RtnValue (value)
		SELECT Value = ltrim(rtrim(Substring(@List, 1, Charindex(@SplitOn, @List) - 1)))

		SET @List = Substring(@List, Charindex(@SplitOn, @List) + len(@SplitOn), len(
					@List))
	END

	INSERT INTO @RtnValue (Value)
	SELECT Value = ltrim(rtrim(@List))

	RETURN
END

Step 2 in your store procedure change the parameter where condition something like below

CREATE PROCEDURE [dbo].[SomeSP]
	-- Add the parameters for the stored procedure here 
	@CostCentre NVARCHAR(255)
AS
BEGIN
	SELECT [ProjectCode]
		,[ProjectName]
		,[ProjectManager]
		,SUM([Hours]) AS [Hours MTD]
	FROM dbo.Rpt_NRMA_CATS NC
	INNER JOIN PeriodID P
		ON NC.PeriodID = P.PeriodID
	WHERE ([CostCentre]) collate database_default IN (
			SELECT Value
			FROM dbo.FnSplit(@CostCentre, ',')
			)
END

Thanks Desari!

——————————————————————————-

Copied and pasted from: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/0ead7ceb-3fdd-4625-aa82-1d4195f984b1/

——————————————————————————-

Know another way?  Please leave a comment.

Advertisements