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 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.
Using Multi-Select in SSRS
Passing multi-value parameter in stored procedure (SSRS report)
Create a Table valued function in SQL Functions as below
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
Know another way? Please leave a comment.