SSRS Cascading Multi-Value Parameters

16 August, 2013 (11:00) | SSRS | By: Mark V

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

 

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

image

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

image

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

Figure 3

image

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

SNAGHTML23ede7

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

SNAGHTML2e6863

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

SNAGHTML2bb1d1

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

SNAGHTML2ddb03

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

image

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

image

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

image

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.

Comments

Comment from MarkNTejas
Time April 15, 2014 at 8:43 am

great educational article Mark! Thx!

Comment from Mark V
Time April 15, 2014 at 9:13 am

Thanks, Mark. :)

Comment from Adnan
Time June 4, 2014 at 5:46 am

Thanks, Mark. It was really nice, but i am facing issue here. Report works fine on local host. But when i deploy RDL file on production server, cascading parameters don’t work. Report just hangs after i select first parameter. Any help?

Comment from Mark V
Time June 5, 2014 at 12:50 pm

That is interesting, Adnan. Do you see any errors in the ssrs log files? http://technet.microsoft.com/en-us/library/cc512029.aspx The logs may have information to lead you to the issue. Off hand, I have no idea. How about actually deploying it to a Dev/Test server and not just working with it locally in SSDT/BIDS?

Comment from kaarthik
Time June 19, 2014 at 4:22 am

this is what i have been looking for….very thank u amigo…

Comment from Irene
Time February 2, 2015 at 2:09 pm

Thanks for a great article, Mark. I am an SSRS newbie and could really use a little more help. I repeated your example to the letter and it works, but I have not been able to show PersonName and RaceName in separate columns. How do I go from your example, which shows a one-column report, to a matrix that shows separate columns for values from 5 cascading parameters?

I have built a matrix report without any parameters at all, but it is cumbersome for my end users. I created a Document Map but, even so, there is too much un-needed “stuff” in the report. The database contains millions of rows, so processing speed is important (the use of SPs attracted me to your article).

I need the following parameters: Country, City, Department, Product and Item. My matrix has 13 columns for the numbers of items sold each month along with a total. The end user needs to have options such as:
• COUNTRY=USA, CITY=All, DEPARTMENT=Camping, PRODUCT=Tents, ITEM=T123 (for all Tents T123 sold by a camping Department in any City in the USA); or
• COUNTRY=Australia, France and Italy, CITY=All, DEPARTMENT=Fishing, PRODUCT=Rods, ITEM=All (for all fishing rods sold in any of those countries).

I would greatly appreciate any help you can offer.
Regards,
Irene

Comment from Mark V
Time February 2, 2015 at 8:35 pm

Hi, Irene. The layout of your matrix is up to how you arrange the columns in your dataset. While the values themselves can be filtered using Parameters, typically the arrangement of fields is a design time decision. I think I am confused a bit by your question. Do you want your parameter values to be columns in the output or just the months and total to be the columns?

Comment from Irene
Time February 3, 2015 at 8:48 am

Hi, Mark. Thank you for responding to my question. Yes, the parameter values need to be columns in the output, unless there is another way for the user to know what they are looking at. I would like to keep the report as uncluttered as possible.

Comment from Mark V
Time February 3, 2015 at 9:28 am

If you only want to return the values chosen so that users know what the values were, you can display textboxes for the parameters using the Parameters collection: https://msdn.microsoft.com/en-us/library/dd255264.aspx

Then you can keep the matrix itself clean and tidy.

Comment from Irene
Time February 3, 2015 at 10:11 am

Thanks, Mark. I will experiment.

Comment from Roger Osborne
Time May 22, 2015 at 9:02 am

Hi Mark, I have tried your method, and it works when you choose one parent parameter, but not when selecting more than 1 parent parameter?

Comment from Mark V
Time May 26, 2015 at 10:19 pm

Hi, Roger. I am not clear what you mean by more than 1 parent parameter. Do you mean that you have Parameter A which cascades to Parameter B which then cascades to Parameter C?

Comment from Alicia Meredith-Smith
Time June 4, 2015 at 6:46 am

Works like a bomb! Thanks so much for your help!

Comment from Jack Cooper
Time July 28, 2015 at 10:06 am

Hey Mark, My issue is when you pick multiple values from the first drop down. So using your example if I selected Dwarves,Men then it errors (I’d be expecting the PersonList to bring through all of the Dwarves and all of the Men). There isn’t much point having a multi-value parameter if you can only select one value! This might be what Roger is referring to. Jack

Comment from Roger Osborne
Time July 28, 2015 at 10:12 am

Hi Mark, yes I have the same issue as Jack, so if you choose more than 1 value in the ‘parent’ parameter the cascading does not work for the child parameter values?

Comment from Mark V
Time July 28, 2015 at 4:01 pm

Hi, Roger and Jack. I’m sorry you’re having issues with this solution. I just opened this up in SSDT for SQL 2014 (I don’t have an older version installed anymore) and I am not having any issues when selecting multiple options from Race List and Person List. What is the error that you are getting?

Comment from Roger Osborne
Time July 29, 2015 at 3:04 am

Hi Mark, I’ve tried in 2005 and 2010 (don’t have 2014) and it will work when you choose 1 parent parameter, but when you change the selection in the parent parameter to more than 1 selection, the child parameter values available do not get refreshed, Thanks, Roger

Comment from Mark V
Time July 30, 2015 at 8:27 am

Hi, Roger. Have you tried deploying to an instance of SSRS? I would be curious if that behavior persists there. That would also provide the opportunity to check out the SSRS logs and see if there is any info in there.

Comment from Roger Osborne
Time July 30, 2015 at 10:08 am

Hi Mark, yes same issue on report server, cannot choose more than 1 ‘parent’ parameter. Works fine with just 1 parent parameter selected. Error reported is “Procedure or function fn_split_multi_parms has too many arguments specified” .. however when you run the same code in query analyser it works fine. I can provide the details of fn_split_multi_parms if required, Thanks Rog

Comment from Roger Osborne
Time July 30, 2015 at 10:12 am

Essentially though it is the same as your dbo.fn_String_To_Table

Comment from Roger Osborne
Time July 30, 2015 at 10:21 am

Might have had a breakthrough here give me a few mins!

Comment from Roger Osborne
Time July 31, 2015 at 2:45 am

It works! Thank you so much Mark, not quite sure what I was doing wrong I will post back when I find out!

Comment from Mark V
Time July 31, 2015 at 7:52 am

Woohoo! That’s outstanding, Roger. Please do share once you know so that Jack and others can get guidance if they have the same issue. Thanks.

Comment from Irene
Time July 31, 2015 at 7:52 am

Looking forward to your solution, Roger.

Comment from Roger Osborne
Time October 13, 2015 at 10:53 am

Hi there, sorry for delay. The only difference I can see is that I moved the sql for the child parameter into a stored procedure (rather than embedded in the definition of the child parameter dataset) .. and it now works great, so thanks again Mark

Comment from Mark V
Time October 13, 2015 at 12:10 pm

That’s great news, Roger. Glad it worked out for you.

Comment from scott
Time February 14, 2017 at 3:40 pm

Great article! I’m having an issue using cascading parameters that I’m stuck on. I have state and county cascading parameters that are both pulled from stored procedures(stp_state and stp_county). However, if I select both Florida and California the report will only pull back records where the county matches(Orange County is in both states). I’m having the same issue if I select All states.

Any ideas on how to fix this?

Comment from Mark V
Time February 15, 2017 at 9:30 am

Hi, Scott. Thanks for the kind words. :) It’s been a while since I have dealt with this, but my first gut feeling is to check to make sure you are using the IN operator when filtering Counties based on the State. That behavior seems like what you would get if you were using AND instead, or possibly putting the State values in a table and INNER JOINing to it.

Comment from scott
Time February 15, 2017 at 9:59 am

Hi,
I actually figured it out last night. I needed to increase the size of the county stored procedure to MAX. The string passing the counties had gotten larger with the addition of new counties to the database.

Thanks.

Comment from Mark V
Time February 15, 2017 at 10:08 am

Ah. Good deal. Have fun.

Comment from kduncan1
Time May 30, 2017 at 12:26 pm

I do not agree, read:
http://abeautifulmess.com/2014/07/weaving-class-creating-shapes-and-getting-fancy.html

Write a comment