How To Pass A Multi-Select Parameter to Stored Procedure from Reporting Services (SSRS)

I've seen this question asked about a million times on different message boards, so I figured I'd do a walk through on one of the ways we might solve this problem. The main problem people run into is that Reporting Services (SSRS) passes the entire set of selected values from a multi-select box as one comma separated string into their stored procedures parameter. This usually results in the report working fine as long as only one option is chosen, but it typically returns nothing if more than one value is selected, or you get an error because your parameter was probably expecting a key (int) value but it got a string instead.

There are a few different ways we can get around this problem (using scripting in SSRS and table-value parameters etc), but the easiest way I've found is to create a UDF that splits the string into a table of values, and then call that UDF in your report stored procedure. Some of the other ways like using table-value parameters are probably going to be faster, but if you're only going to be passing a few values this method performs pretty well.

I'm not going to go into the specifics of how you tie a report parameter to a stored procedure parameter in this post. I'm assuming anyone who's reading this probably already knows how to do that...otherwise you wouldn't be looking up how to do a multi-select parameter in your report. The only thing that should change about your parameter in SSRS is that you'll want to check that box that says "Allow Multiple Values" when you create it, so that your report allows the user to select multiple values. Everything else will stay the same in SSRS. What we'll be changing is how our stored procedure handles the value passed to it from SSRS...and that'll be done in the DB itself.

First off, we're going to create a new User Defined Function (UDF) in our database. This UDF will take a string of delimited values, and separate them into a table based on whatever character we tell it to split on, and then return the table to us. In the case of Reporting Services, we'll want to split our values using a comma ",". Here's the code to create our UDF we'll need to call:

/*================================================================

created by: Tavis Lovell

created on: 2009.07.01

description: This UDF is used when you need a stored procedure to

take in multiple values for a parameter. This UDF allows you to

split a comma (or anything else) seperated list of values and

returns then as a table so they can be compared againsts in the stored

procedure.

the udf should be called like this:

ThemeID IN (SELECT Convert(INT, VALUE) FROM dbo.Split(@p_ThemeID,','))

ModifiedBy        Date              Desc

--------------------------------------------------------------------

Tavis Lovell      2009.07.02        Initial Creation

--================================================================*/

      CREATE FUNCTION [dbo].[Split]

      (

            @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

GO

Just copy the above code into query analyzer and run it in the same DB that the stored procedure for your report resides in.

Once we've created our UDF we'll need to make a few modifications to our main stored procedure the report calls. First and foremost you'll want to change your parameter that is receiving the multi-select values from SSRS to something that can handle a string. You also want to make sure it's long enough to handle the amount of values you'll be passing in. In my case I'm only going to be passing two values, so VARCHAR(50), will be fine for me.

Currently my procedure looks like this (pre multi-select changes)

CREATE PROCEDURE [dbo].[usp_GetGamesMulti]

@p_GameSystem VARCHAR(50) = NULL

AS

BEGIN --proc

      SELECT

            vg.GameTitle,

            vg.GameSystem

      FROM

            VideoGames vg

      WHERE

            vg.GameSystem = @p_GameSystem

END --proc 

Next we'll need to change our WHERE statement so that it can compare to multiple values. There are several different ways of doing this, but for simplicity sake, we're just going to change the "=" to an "IN" and then call our UDF to return a table that we can compare against. See the yellow highlighted code below for the changes.

CREATE PROCEDURE [dbo].[usp_GetGamesMulti]

@p_GameSystem VARCHAR(50) = NULL

AS

BEGIN --proc

      SELECT

            vg.GameTitle,

            vg.GameSystem

      FROM

            VideoGames vg

      WHERE

            vg.GameSystem IN (SELECT VALUE FROM dbo.Split(@p_GameSystem,','))

END --proc 

NOTE HOWEVER that if you’re comparing against an INT value you'll need to convert your values in the select....if that's the case, our "IN" statement would look something more like this:

IN (SELECT CONVERT(INT, VALUE) FROM dbo.Split(@p_GameSystem,','))

That should do it. Your procedure is now ready to handle the string value that an SSRS multi-select parameter will pass to it. Hopefully this helps. If there's a strong need from people to see more details around how we tie the parameters in SSRS to the proc etc I might do a more in depth post, so send me an email if that's the case. Email address is on my contact page.

 

Where did my PowerPivot tab go?

I opened up Excel 2010 today, and noticed that my PowerPivot tab was suddenly gone. A bit of searching led me to this very good post on how you can get your PowerPivot menu items back in the event this has happened to you as well.

http://denglishbi.wordpress.com/2010/06/21/where-did-my-powerpivot-excel-2010-tab-go/

Turns out closing excel before it completely loads can come back to bite you, easy enough fix though.