Tag: ssrs parameters

SSRS Cascading Multi-Value Parameters

By Mark on August 16, 2013 • ( 32 Comments )

Not too long ago, there was a question posted to #ssrshelp on Twitter regarding having multi-value parameters in SSRS 2005 that ALSO cascade. While I don’t have access to a SQL 2005 instance anymore, I wanted to try it in 2012 since I had an idea how to accomplish it. My idea worked just fine in 2012 and uses functionality that, if I recall correctly, should be available in 2005 as well.

To start, I want to define these two types of parameters so we are all on the same page.

Multi-Value parameters allow the user to select one, some, or ALL values from a drop-down list. This is native SSRS functionality and it creates the Select ALL option for you. The result(s) of the user selection are placed into a comma separated list of the values chosen, which you can then pass to a dataset or use in some other way.

Cascading parameters allow the value(s) select by one parameter (the Parent, if you will) to control the available values presented in another parameter (the Child, if you will). An example would be a list of US States in one parameter and a list of Counties in the second. The Counties parameter would limit its available values to the Counties that are in the State(s) chosen in the State parameter. With Cascading parameters, the Child parameter is not activated until a valid choice has been made on the Parent parameter.

I pretty much always use stored procedures for my datasets with reporting. Now, natively, passing multi-value parameters to a stored procedure is not supported. I know of two ways around that, both of which I have used at clients.

The XML Method

I wrote about this method myself in this post.

The Table-Value Function Method

This one is demonstrated in a great post by David Leibowitz (Blog|Twitter) here.

I will be using the Table-Value Function method in this post.

We start by creating a quick database and a few objects. Feel free to use an existing database to house these objects instead.

USE master;

GO

CREATE DATABASE SSRSParmDemo

GO

USE SSRSParmDemo;

GO

CREATE TABLE dbo.Race

      RaceID int IDENTITY(1,1) NOT NULL

    , RaceName varchar(50) NOT NULL

    , CONSTRAINT PK_Race PRIMARY KEY CLUSTERED (RaceID)

GO

INSERT INTO dbo.Race VALUES

  ('Dwarves')

, ('Elves')

, ('Men')

GO

CREATE TABLE dbo.Person

      PersonID int IDENTITY(1,1) NOT NULL

    , RaceID int NOT NULL

    , PersonName varchar(100) NOT NULL

    , CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (PersonID)

    , CONSTRAINT FK_Person_Race FOREIGN KEY (RaceID) REFERENCES dbo.Race (RaceID)

);

GO

INSERT INTO dbo.Person VALUES

  (1, 'Balin')

, (1, 'Gloin')

, (1, 'Thorin')

, (2, 'Elrond')

, (2, 'Legolas')

, (2, 'Buddy?')

, (3, 'Aragorn')

, (3, 'Boromir')

, (3, 'Faramir')

GO

/**

-- http://blog.summitcloud.com/2010/01/multivalue-parameters-with-stored-procedures-in-ssrs-sql/

**/

CREATE FUNCTION dbo.fn_String_To_Table (

   @String VARCHAR(max), /* input string */

   @Delimeter char(1),   /* delimiter */

   @TrimSpace bit )      /* kill whitespace? */

RETURNS @Table TABLE ( [Val] VARCHAR(4000) )

AS

BEGIN

    DECLARE @Val    VARCHAR(4000)

    WHILE LEN(@String) > 0

    BEGIN

        SET @Val    = LEFT(@String,

             ISNULL(NULLIF(CHARINDEX(@Delimeter, @String) - 1, -1),

             LEN(@String)))

        SET @String = SUBSTRING(@String,

             ISNULL(NULLIF(CHARINDEX(@Delimeter, @String), 0),

             LEN(@String)) + 1, LEN(@String))

  IF @TrimSpace = 1 Set @Val = LTRIM(RTRIM(@Val))

    INSERT INTO @Table ( [Val] )

        VALUES ( @Val )

END

    RETURN

END;

GO

CREATE PROC dbo.getRace

AS

SELECT

      RaceID AS Value

    , RaceName AS Label

FROM dbo.Race

GO

CREATE PROC dbo.getPerson

    @RaceList varchar(max)

AS

SELECT

      p.PersonID AS Value

    , p.PersonName AS Label

FROM dbo.Person p

WHERE p.RaceID IN

        SELECT Val

        FROM dbo.fn_String_To_Table (@RaceList,',',1)

GO

CREATE PROC dbo.getPersonReport

      @PersonList varchar(max)

AS

SELECT

      p.PersonName + ' is of the Race of ' + r.RaceName AS PersonRace

FROM dbo.Person p

INNER JOIN dbo.Race r

ON p.RaceID = r.RaceID

WHERE p.PersonID IN

        SELECT Val

        FROM dbo.fn_String_To_Table (@PersonList,',',1)

GO

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

The purpose of each procedure is as follows:

getRace provides a list of the Races available in the dbo.Race table. This will be the parent parameter whose selected values will cascade as a filter for the Person parameter.

getPerson provides a list of the folks in the dbo.Person table based on the list of Races passed in to its @RaceList parameter. This will be the child parameter whose values will be pre-filtered by those selected in the Race parameter.

getPersonReport returns the folks from the Person table based on the list of PersonID values passed in via the @PersonList parameter. This will be the dataset for the report.

Let’s add the three datasets to the report, each using the Shared Data Source pointing to the database created above. You can see them in Figure 1.

Figure 1

Each dataset maps to the stored procedure of the same name.

The report for this example is very basic. It is simply a table (tablix) which shows the PersonRace column for each row returned in the getPersonReport dataset. The report layout is shown in Figure 2.

Figure 2

Once all three datasets are added, there will be two report parameters added for you, as shown in Figure 3.

Figure 3

We need to do a little work with each parameter.

Double-click the RaceList parameter, revealing the Report Parameter Properties dialog shown in Figure 4.

Figure 4

Ensure that the Allow Multiple Values option is checked, as shown by the red arrow in Figure 4. Then click the Available Values option shown in the oval in the upper left. This will bring up the Available Values dialog just like Figure 5.

Figure 5

Select the Get Value From a Query option and choose the getRace dataset. From there, just match up the Value and Label fields like in Figure 5. Then click OK.

Double-click the Person parameter to bring up its Properties, as in Figure 6.

Figure 6

Make sure the Allow Multiple Values option is checked. In my report, the Allow Blank Value for this parameter was checked automatically. I tried running the report both with it checked and with it unchecked and noted no difference in functionality. Click the Available Values option to bring up a dialog similar to Figure 7.

Figure 7

Check the Get Value From a Query option and set the Dataset to getPerson. Set the Value and Label fields respectively as shown in Figure 7 and click OK.

Now let’s run the report, which should resemble Figure 8.

Figure 8

Notice the Race List parameter has checkboxes for each Race along with a (Select All) option, which SSRS built for us. Also notice that the Person List parameter is grayed out. Because it requires Races to be passed to it, SSRS knows that we cannot use until at least one Race has been selected. When we choose a Race, perhaps Dwarves, and click away from the Race List parameter, that value is passed to the getPerson stored procedure, producing the available values list for the Person List parameter, as shown in Figure 9.

Figure 9

Notice that we have a list of folks along with the (Select All) option that SSRS created for us. If you are familiar enough with the works of J.R.R. Tolkien, you will recognize that all of these folks are Dwarves. Either way, click (Select All), which will put a check in every checkbox available, and click View Report. Your results will resemble Figure 10.

Figure 10

That’s really all there is to it. Play around with different combinations (you may enjoy the Easter Egg among the Elves; What’s your favorite color?).

Since it has been a long time since I used SSRS 2005, I cannot fully promise this will work. But none of the functionality above is all that fancy, so it seems to me it could possibly work just fine. If you try it in 2005, please let me know how it goes.

Using Bogus Measures in SSRS Parameter Available Values Lists

By Mark on April 3, 2012 • ( 2 Comments )

I am currently working on a project involving many reports against an Analysis Services 2008 R2 cube that we implemented for the client. The available values lists for the report parameters are largely just lists of members of various dimension attribute hierarchies from the cube. Since we are not going to actually use any measures in our available values lists, we decided it was best if we didn’t even hit any measure groups at all. Why waste resources retrieving facts we don’t even want, right?

Let’s take our example from the Adventure Work DW 2008 R2 SSAS database, which contains the Adventure Works cube.

Suppose we have a report parameter that allows the user to select from the list of Product Categories.

We could start by just putting the Product Categories on the COLUMNS axis, thereby specifying only the items we actually want. Since all queries must include the COLUMNS axis, we have to start there.

-- Query 1
SELECT [Product].[Product Categories].children ON COLUMNS 
FROM [Adventure Works]

Query 1 produces the following results:

With this query, we are hitting a measure group even though we didn’t specify one, in this case the Reseller Sales measure group, to retrieve the default measure, Reseller Sales Amount. Not only that, but we have the Product Categories pivoted and would have to un-pivot them to use them in an available values list. That’s not the best way, for sure.

We could just go ahead and resign ourselves to retrieving a measure and specify the Reseller Sales Amount measure since we’re going to get it anyway.

-- Query 2
SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,
[Product].[Product Categories].children ON ROWS
FROM [Adventure Works]

Query 2 produces the following results:

This is less than spectacular as well. While we actually have our Product Categories on the Rows where we want them, we are still hitting the measure group for no reason. We have already seen that we will get the default measure even if we don’t specify one. So, let’s specify one that isn’t in a measure group at all.

In the following query, we declare a new measure called Bogus that is just a constant, “1” and nothing else.

-- Query 3
WITH MEMBER [Measures].[Bogus] AS "1"

SELECT [Measures].[Bogus] ON COLUMNS,
[Product].[Product Categories].children ON ROWS
FROM [Adventure Works]

Query 3 produces the following results:

This query still puts the Product Categories on the rows where we want them. Hazzah. And, since we specified a Measure, even a Bogus one, we didn’t need to hit any measure groups, not even for the default measure. Double Hazzah.

So, when constructing available values lists, follow the lead of modern political discourse in the United States: Don’t muddy things up with facts. Oh snap!

Jinkies! Passing MultiValue SSRS Parameters To Stored Procedures

By Mark on January 5, 2010 • ( 8 Comments )

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!

Mark Vaillancourt

I drink coffee and know things

Tag: ssrs parameters

SSRS Cascading Multi-Value Parameters

Using Bogus Measures in SSRS Parameter Available Values Lists

Jinkies! Passing MultiValue SSRS Parameters To Stored Procedures