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:


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


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

	SET @InputString = @InputString + ','

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

		INSERT INTO @ValuesList
		SELECT @ListValue

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



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

Using Multi-Select in SSRS

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)
	Id INT identity(1, 1)
	,Value NVARCHAR(100)
	WHILE (Charindex(@SplitOn, @List) > 0)
		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(

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


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

	-- Add the parameters for the stored procedure here 
	@CostCentre NVARCHAR(255)
	SELECT [ProjectCode]
		,SUM([Hours]) AS [Hours MTD]
		ON NC.PeriodID = P.PeriodID
	WHERE ([CostCentre]) collate database_default IN (
			SELECT Value
			FROM dbo.FnSplit(@CostCentre, ',')

Thanks Desari!


Copied and pasted from:


Know another way?  Please leave a comment.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s