How to implement cascading parameters in a SQL Services Reporting Services MDX Report

Posted on May 18, 2008 by ddarden

Background

Last week, I needed to prototype a report against an Analysis Services 2005 cube for a client.  The report wasn’t too complicated… I just had the requirement to allow a user to drill down a hierarchy by selecting a member at one level, then displaying all the children for that member, etc.  This is really easy to do in SSRS… but I wouldn’t go so far as to call it super intuitive.  It had been awhile since I wrote a MDX report, and it took me a few minutes to remember just how to do it… so I thought I’d document the steps, for my own benefit if no one else’s.

The sample requires the Adventure Works AS DB.  You can download the sample here.

Scenario

So, I want to create a report that returns the Order Count by Product.  The users need to select a particular Category, and then Subcategory for the products they want to see.  The report also needs to return summary information for both the Category and Subcategory level, and it needs to contain the Category and Subcategory on the report.  This can be a pretty standard business case for reports where the potential data set is to large to report on.

Underlying Data

In order for this to work, I need a hierarchy to drill down.  Here, I’m going to use the Product Categories hierarchy of the Product dimension in the Adventure Works DW.

Step1_thumb

The Report

Next, I’m going to create a data set to use in my report.  I’m going to go ahead and switch to the MDX view for the data set.  Now, I’m going to go ahead and create a few parameters.

Step2_thumb2

Now, I’m going to go to the Report Layout tab,  right-click off the report, and choose to view my parameters.  Yup, they’re there.  Now, I’m going to go back to the Data tab, and look at my list of available data sets…

Step3_thumb

Cool!  BIDS just created two new data sets for me, based on the parameters I created.  Note that you have to view your Report Parameters on the Layout tab for these data sets to be created (you just have to open the dialog, you don’t have to do anything).  This seems to be the part that I always forget.

Let’s look at the ParameterCategory data set first.  The MDX that BIDS created looks like this:

   1: WITH MEMBER [Measures].[ParameterCaption] AS '[Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION' 
   2: MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME' 
   3: MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL' 
   4: SELECT 
   5:     {
   6:         [Measures].[ParameterCaption]
   7:         , [Measures].[ParameterValue]
   8:         , [Measures].[ParameterLevel]
   9:     } ON COLUMNS , 
  10:     [Product].[Product Categories].ALLMEMBERS ON ROWS 
  11: FROM 
  12:     [Adventure Works]

But when we run that, it returns everything in the hierarchy… including that nasty ‘All Products’, which we don’t want our users selecting.  So, we’ll make a slight modification, and change ‘[Product].[Product Categories].ALLMEMBERS’ to ‘[Product].[Product Categories].Children’.  Now, we get just the Categories that we want our users to select.

Now, we want to look at the ParameterSubCategory dataset.  The MDX looks like this:

   1: WITH MEMBER [Measures].[ParameterCaption] AS '[Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION' 
   2: MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME' 
   3: MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL' 
   4: SELECT 
   5:     {
   6:         [Measures].[ParameterCaption]
   7:         , [Measures].[ParameterValue]
   8:         , [Measures].[ParameterLevel]
   9:     } ON COLUMNS , 
  10:     [Product].[Product Categories].ALLMEMBERS ON ROWS 
  11: FROM 
  12:     [Adventure Works]

Again, not precisely what we want… plus, we want this one to be parameter driven.

First, we need to add the ParameterCategory (re-use the original Parameter name to keep an extra data set from being created later) to the report:

Step4_thumb3

Then, we change ‘[Product].[Product Categories].ALLMEMBERS’ to ‘STRTOMEMBER(@ParameterCategory).Children’.  Now, our MDX looks like this:

   1: WITH MEMBER [Measures].[ParameterCaption] AS '[Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION' 
   2: MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME' 
   3: MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL' 
   4: SELECT 
   5:     {
   6:         [Measures].[ParameterCaption]
   7:         , [Measures].[ParameterValue]
   8:         , [Measures].[ParameterLevel]
   9:     } ON COLUMNS , 
  10:     STRTOMEMBER(@ParameterCategory).Children ON ROWS 
  11: FROM 
  12:     [Adventure Works]

and it will only return the Children of the selected category.  You could also create your MDX using an expression, and directly use the the ‘Parameters!ParameterCategory.Value’ embedded in a quoted string.  There is a performance hit for using anything like STRTOMEMBER, but in this case it isn’t worth the trouble to me to do it that way.

Almost done.  Except that we haven’t gotten around to writing the actual query that drives the report…  so we go back to our main data set.

Now, when we create our query, we might be tempted to return the children of the Subcategory… but this won’t get us summary information at the Category and Subcategory levels.  So we’re going to write some MDX using the Hierarchize function to make sure our data set contains the selection at the Category level, at the Subcategory level, and all of the Products underneath the selected subcategory:

   1: SELECT
   2:     [Measures].[Order Count] ON 0,
   3:     HIERARCHIZE(
   4:         {
   5:             STRTOMEMBER(@ParameterCategory),
   6:             STRTOMEMBER(@ParameterSubCategory),
   7:             STRTOMEMBER(@ParameterSubCategory).Children
   8:         }
   9:     ) ON 1
  10: FROM
  11:     [Adventure Works]

Now I’m going to go back to the Layout tab, right-click off the report, pull up my Report Parameters, and change the Default value to Null (so the user will always have to make a selection… though you can make this part as clever as you’d like).

Step5_thumb4

In your report, you may need to use some expressions, filters or other logic to format your results, suppress rows (i.e., the Category will have a Null for both Subcategory and Product… how you want to handle this is up to you).  For this sample, I just wrote a little VB

   1: Dim _Indent As String = "  "
   2:  
   3: Public Function FormatProduct(ByVal Category As String, ByVal SubCategory As String, ByVal Product As String) As String
   4:     Dim ret As String = String.Empty
   5:  
   6:     If Not String.IsNullOrEmpty(Product) Then
   7:         ret = _Indent & _Indent & Product
   8:     Else If String.IsNullOrEmpty(Product) And Not String.IsNullOrEmpty(SubCategory) Then
   9:         ret = _Indent & SubCategory
  10:     Else
  11:         ret = Category
  12:     End If
  13:     
  14:     Return ret
  15: End Function

And used an expression to format my Product column in the report.

Summary

That’s about it.  We now have a simple report with some cascading parameters.

This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

Comments are closed.