SQL Server Reporting Services provides a great way to allows report users to select more than one value from the available values list in parameters, creating a multivalue parameter.

SSRS dynamically creates a comma separated list of the values selected by the user to pass in to the data source.

Great.

It also dynamically creates the Select All option at the top of the available values list.

Also great.

And, it is really easy to enable this feature.

Also also great.

But this great functionality does have a limitation right out of the box. You cannot pass a multivalue parameter to a stored procedure (see Using Single-Value and Multivalue Parameters).

Ruh roh. Not great.

In the past, I have had the data source sproc return all of the records and then used a filter on the data region to only show the records matching the values selected in the parameter. It got the job done, but returning records you are not going to use is suboptimal, to be sure.

Now, you may be thinking, dear reader, that I am a moron for choosing the above title for this post and then almost right away saying that you cannot do it. Well, provided that your database source is SQL 2005 or later, you actually CAN do it through the beauty of XML. And what is really really cool to me is that the amount of work you must perform to achieve this is miniscule. AND, it does not require much XML expertise, which I must confess, I currently lack. I have seen solutions for this obstacle involving the creation of user-defined functions, but I find this XML solution far more elegant and so easy to implement.

As I often do, I must shout out some thanks to Jason Strate (blog: www.jasonstrate.com, twitter: @stratesql) for showing me this.

You may be asking why I am so fond of having reporting data sources be stored procedures instead of just writing the query right into the report. While that is not truly the scope of this post, I will say that I really like having data access code in the database. Also, the security implications are great, since with sprocs, users need only Execute permission on the sproc and none against the underlying tables. There are other reasons, too, but those two are enough for now.

Setting Up The Data

Let’s start off by creating a table to house our data. I am using AdventureWorks (to make it easy) but creating my own schema (to keep things clean). NOTE: I looked but could not find any ScoobyDoo references in AdventureWorks.

USE AdventureWorks

GO

CREATE SCHEMA MarkVSQL

GO

CREATE TABLE MarkVSQL.ScoobyDooCharacter

(

      CharacterID int not null IDENTITY(1,1)

    , CharacterName varchar(10) not null

)

INSERT INTO MarkVSQL.ScoobyDooCharacter VALUES('Fred')

INSERT INTO MarkVSQL.ScoobyDooCharacter VALUES('Daphne')

INSERT INTO MarkVSQL.ScoobyDooCharacter VALUES('Velma')

INSERT INTO MarkVSQL.ScoobyDooCharacter VALUES('Shaggy')

INSERT INTO MarkVSQL.ScoobyDooCharacter VALUES('Scooby Doo')

NOTE: You may be thinking that Scrappy Doo is conspicuously absent from my list. There is a good reason for that. I can’t stand Scrappy Doo. He drives me nuts. I would rather bite down on a metal fork really hard with my front teeth than watch Scrappy Doo. </Scrappy Doo>

Creating Available Values List

Let’s create the stored procedure that will supply our Character parameter with available values. This is where we start to really have some fun.

CREATE PROCEDURE MarkVSQL.GetMysteryIncMemberList

AS

SELECT

      CharacterName

    , '<Character ID="'

        + CONVERT(varchar,CharacterID)

        + '"/>' AS CharacterIDXML

FROM MarkVSQL.ScoobyDooCharacter

This stored procedure returns the CharacterName along with the CharacterID field that has been modified to return the CharacterID value formatted as an xml node instead of just the int. Here is the fabulous output of our MarkVSQL.GetMysteryIncMemberList sproc.

CharacterName CharacterIDXML

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

Fred          <Character ID="1"/>

Daphne        <Character ID="2"/>

Velma         <Character ID="3"/>

Shaggy        <Character ID="4"/>

Scooby Doo    <Character ID="5"/>

Creating Data Source Sproc 

Now we create the stored procedure that will supply our report with data.

CREATE PROCEDURE MarkVSQL.GetMysteryIncMembers

    @CharacterID varchar(max)

AS

DECLARE @CharacterIDXML xml

SET @CharacterIDXML = REPLACE(@CharacterID,',','')

SELECT

      c.CharacterName

FROM MarkVSQL.ScoobyDooCharacter c

WHERE c.CharacterID IN

    (SELECT c.value('@ID','int') FROM @CharacterIDXML.nodes('//Character') AS x(c))

There are several things to note about this proc:

• The parameter is a varchar(max) to allow as much space as we can for the string of comma separated XML nodes that SSRS will pass in.
• We then declare an XML typed variable, @CharacterIDXML.
• We remove the commas (which SSRS automatically adds as value separators) from the contents of the @CharacterID parameter and put the resulting list of XML nodes in the @CharacterIDXML variable.
• Our WHERE clause uses the XML “nodes” method to shred out the CharacterID values from the XML.

Let’s run our MarkVSQL.GetMysteryIncMembers sproc with various parameters as they would be passed in from SSRS.

In this example, we just want to return Fred.

EXEC MarkVSQL.GetMysteryIncMembers '<Character ID="1"/>'

We get the following results:

CharacterName

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

Fred

(1 row(s) affected)

Now let’s get Fred and Daphne together. I think it is no secret that there is a spark between them.

EXEC MarkVSQL.GetMysteryIncMembers '<Character ID="1"/>,<Character ID="2"/>'

Here they are.

CharacterName

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

Fred

Daphne

(2 row(s) affected)

Now let’s get all of those meddling kids and their dog.

EXEC MarkVSQL.GetMysteryIncMembers '<Character ID="1"/>,<Character ID="2"/>,<Character ID="3"/>,<Character ID="4"/>,<Character ID="5"/>'

Here they are, in all of their mystery solving glory.

CharacterName

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

Fred

Daphne

Velma

Shaggy

Scooby Doo

(5 row(s) affected)

The configuration of the parameter in SSRS is nothing out of the ordinary, so I don’t feel it is necessary to go into the step by step process here. You can go here if you need more guidance on the SSRS piece, you can go here: Adding Parameters to Your Report. The article in this hyperlink is for 2008 but includes a hyperlink to the 2005 version as well. The configuration goals are the same, but interface differences between 2005 and 2008 make the exact steps a little different.

You just configure the @CharacterID parameter of the report to do the following:

• Allow multiple values
• Get its available values from the MarkVSQL.GetMysteryIncMemberList sproc.
• Use the CharacterName field as the “Label” field so that the user is presented with the list of names.
• Use the CharacterIDXML field as the “Value” field so that our XML nodes in a comma separated string are passed to the stored MarkVSQL.GetMysteryIncMembers sproc, just like in our examples above.

That’s really all there is to it. Roh boy!