Home > SSRS Expert > SSRS #51 – Using Parameters in SSRS – Cascading Parameter (2)

SSRS #51 – Using Parameters in SSRS – Cascading Parameter (2)

In the previous blog post, SSRS #50 – Using Parameters in SSRS (1), I listed 7 different ways you can use report parameters. They are also the skills that report developers need to master in order to develop interactive reports.

In this blog, I’ll show you some simple steps to use cascading parameters.

Goal

You want to allow users to select a supervisor from the first dropdown list, then be able to see the employees who report to the selected supervisor in the second dropdown list.

The end result looks like this. Note that the second dropdown list is dimmed initially. Once the selection is made from the first dropdown list, the second dropdown list will be selectable.

image

 

The screen shots are from BIDS 2008, which will be slightly different from BIDS 2005. The concept and the steps are the same though.

Step 1 – Create two datasets: One for supervisors, one for team members

In my example, they are DATASET_SUP and DATASET_UW. I have other datasets in the screen shots, but they are irrelevant for the purpose of this post. 

image

Examine the properties for the DATASET_SUP, you can see that the SQL query just simply retrieves the unique supervisor names and their IDs (within the selected date range).

image

Examine the properties for the DATASET_UW, you can see that the SQL query now has a where clause WHERE SUPCCRID IN (@SUP_CCRID).

image

The at sign @ represents a report parameter. At this point, do not worry about if parameter @SUP_CCRID has been created or not. If not, BIDS will automatically create one for you and you can modify its properties later.

Note1: make sure you have both the ID and the name fields in your queries for both the supervisors and the team members. See my blog post, SSRS #46 – A case against using character field in multi-value parameters, for the reasons why you want to use ID field to pass between queries or stored procedures.

Note 2: the where clause WHERE SUPCCRID IN (@SUP_CCRID) is the trick that makes the cascading happen.

Note 3: as observant as you are, you must have noticed that I didn’t write my where clause as this:

WHERE SUPCCRID = @SUP_CCRID

With WHERE SUPCCRID IN (@SUP_CCRID) , I am allowing multiple values of SUP_CCRID passing to my query, instead of a single value.

Step 2 – Create two parameters: one for supervisors, one for team members

In my example, @SUP_CCRID is the parameter that allows users to select a supervisor from a list, and @CCRID is the parameter that allows users to select team members from a list.

image

Let’s examine their properties.

For parameter @SUP_CCRID, this is what you will need to do on the Available Values tab:

  • 1) you need to get values from a dataset. Select DATASET_SUP from the dropdown.
  • 2) Select the ID field for the Value field.
  • 3) Select the name field for the Label field.

image

For parameter @CCRID, you will do the same as for @SUP_CCRID.

  • 1) you need to get values from a dataset. Select DATASET_UW from the dropdown.
  • 2) Select the ID field for the Value field.
  • 3) Select the name field for the Label field.

image

You are done. Preview your report, you will see the result as shown in the first screen shot in this post.

  1. Rachelen Hut
    May 31, 2011 at 3:42 pm

    Thank you so much! This is perfect! It works and I can use this in several reports.

    Like

    • Sherry
      May 31, 2011 at 4:12 pm

      I am glad it worked. Thanks for the feedback.

      Sherry

      Like

  2. DEREK
    June 7, 2011 at 1:37 pm

    It is all fine if using SQL text as the data source for parameters. Yet, it goes tricky if someone wanna use proc to be the data source for parameters. Then passing a string of values to proc challenges us a little bit. Of course, you can find the some workarounds for this. Here I would say, this would also be a good topic.

    –Derek D. From China

    Like

    • Sherry
      June 10, 2011 at 4:26 pm

      Hi Derek,

      Using SQL procedures as data source sgives us flexibility to handle parameters and the data set. I have several posts on this topic, especially on how to pass a string of values to a procedure, and that is the best practice for doing that.

      You can search listtotable on my blog to see all the related posts.

      Thanks for your coments.

      Sherry

      Like

      • June 26, 2011 at 8:03 am

        Check that post out. You basically suggest to interpret the string in the server side via a function. As I remember, we also can make this happen by reporting engine mechanism as well.

        Like

      • Sherry Li 李雨
        June 26, 2011 at 7:27 pm

        Derek, 99% of my blog posts are just tips, experiences and notes. They are not recommendations one way or another. It’s up to the developers to make the right choice when it comes to their own project.

        Sherry

        Like

      • DEREK
        June 27, 2011 at 2:27 am

        Yes, I know and no offensive:)
        I love the threads you share, from which I learnt a lot. My comments is only for discussion purpose.

        Like

  3. Richard McBride
    June 24, 2011 at 2:48 am

    Hi Sherry

    I want to do something similar to this but with a date range. My report has Date Names as a Dimension. All days within the cube are named and the datename list is a text list. I want the users to select a StartDate for their report, and then I want the EndDate list to be limited to 90 days beyond the StarteDate. In other words I don’t want the users selecting a date range greater than 90 days for the report.

    My MDX select for the StartDate contains all members. I think I need the MDX for the EndDate to look something like STRTOMEMBER(@StartDate):STRTOMEMBER(@StartDate) + 90. But I don’t know how to get that StartDate into the EndDate query, especially since the parameter will want to parse the query when the report loads, and since there is no value for the StartDate the query parser will error out.

    How can I do this?

    Like

    • Sherry
      June 24, 2011 at 4:25 am

      Hi Richard,

      As far as I know standard MDX does not provide special date/time calculation functionality. There are probably several ways to do what you want to do outside the realm of MDX. One way I think can be very easily implemented is to create a new named calculation inside the Analysis Services. You can do so in the DSV (data source view) using a SQL expression. Then use the date attribute and the new calculated attribute as the available values for your start and end date in SSRS.

      Sherry

      Like

  4. Jeff
    January 26, 2012 at 5:04 pm

    Just set the paramter order with Supervisor above Employee, and SSRS will automatically cascade the parameters run time.

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: