THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences the Microsoft Data Platform

Reporting Services, MDX and the (unwanted) cascading parameters

Reporting Services can be used in conjunction with Analysis Services, creating powerful reporting solution that can satisfy almost  any customer needs for “standard” reporting. For “standard” I mean solutions that has not be created using Excel and a Cube to give the user complete freedom (and responsibility) over data.

In one of our current project a customer has the need to allow all subsidiaries around Italy to access a report through their web-based extranet. As a consequence the report must be accessible via web and needs to be exported in Excel and PDF (plus several other constraints that are not interesting for the discussion so I’ll omit them).

Reporting Services is surely the ideal solution for these requests. The data is stored in a Analysis Service 2008 Cube. Easy. The Report will have a little bit more that 10 parameters. Again, easy.

Well…it SHOULD be easy. And it will be, at least ‘till the point where you start to struggle with MDX and parameters. It’s a common knowledge that Reporting Services can work quite well, though with several limitations, with Analysis Services: Report Parameters can take their value from an MDX query, and the values can be passed back to another MDX query in order to get the correct data for the report.

Unfortunately, by default (tested on SQL Server 2008) all Report Parameters that uses MDX and support Single & Multi-Value selection, will act as a “cascading” parameters, which means when you run the report, all parameters except first will be disabled:


You have to select the value for the first parameter and than the report will load the value for the second. Then you select the value for the second and the third will get enabled…and so on.

Of course, with several parameters this can be quite frustrating. This situation also make harder the development of a custom web page that has to purpose to make more user-friendly the selection of all the values when you have a high number of parameters.

So, how to solve this problem? First, it’s imperative to understand why parameters become “cascaded”. If you have created the MDX query using the Query Editor, you also have specified what Dimension Attributes (or Hierarchies) has to be turned into parameters, simply flagging the related checkbox:


Behind the scenes this will create hidden dataset used be the automatically created Report Parameters


All the created dataset, with the exception of the one used by the first Report Parameter, have an explicit reference to others parameters. In particular the MDX slicer look like the following:





        ( STRTOSET(@ProductProductCategories, CONSTRAINED) ) ON COLUMNS
            ( STRTOSET(@CustomerCustomerGeography, CONSTRAINED) ) ON COLUMNS
            [Adventure Works]

This can lead to think that simply removing these references (which, in my experience, are not useful anyway), you can solve your problem. If you do so, just remember also to remove the associated Dataset Parameter. Anyway – and unfortunately – it’s  not so easy.

In fact, the culprit of the problem is in the indentation of the parameter caption:


The hierarchical look is generated by a calculated field in the parameter’s dataset:


As one can guess, the field ParameterCaptionIndented is the one that is used in the Parameter, to make it look like a hierarchy.

The Expression of that field is very simple:

=Space(3*Fields!ParameterLevel.Value) + Fields!ParameterCaption.Value

but this is enough to make the parameter “cascaded”.

If you remove this, you won’t have any look-like hierarchy parameter, but you’ll also get rid of the “cascade” behavior in the Report Parameters.

Now the we have solved the problem, we have to figure out how to have the hierarchy again, which is very comfortable from a user perspective. We can’t do it Reporting Services, so we have to move on Analysis Services. MDX doesn’t offer any kind of advanced string manipulation function, so we have to use .NET to create a MDX User Defined Function that can be called within our MDX Query so that the field ParameterCaptionIndentation will be generated correctly directly by Analysis Services, without requiring us to create a Calculated Field anymore.

Said and done:

using System;
using Microsoft.AnalysisServices.AdomdServer;

namespace SolidQ.AnalysisServices.Utils.Functions
    public class StringHelper
        public string IndentString(string source, int indentationLevel, int indentationSize)
            string indentation = new string(' ', indentationLevel * indentationSize);
            return indentation + source;

        public string IndentString(string source, int indentationLevel)
            return IndentString(source, indentationLevel, 4);

Very simple but very useful! After having compiled the code and loaded the generated Assembly into Analysis Services


the function can be used directly in the MDX query. The following code is the one used by Customer Geography Report Parameter:

MEMBER [Measures].[ParameterCaption] AS
    [Customer].[Customer Geography].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterCaptionIndented] AS
        [Customer].[Customer Geography].CURRENTMEMBER.MEMBER_CAPTION,
        [Customer].[Customer Geography].CURRENTMEMBER.LEVEL.ORDINAL,
MEMBER [Measures].[ParameterValue] AS
    [Customer].[Customer Geography].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS
    [Customer].[Customer Geography].CURRENTMEMBER.LEVEL.ORDINAL
     [Customer].[Customer Geography].ALLMEMBERS ON ROWS
    [Adventure Works]

The highlighted section shows that the ParameterCaptionIndeted member simply call the IndentString function that takes 3 parameters:

  1. String to be indented
  2. Indent Level
  3. Indent Size (Optional. If omitted indentation size is 4 spaces)

Using the same approach for all the Report Parameters, we can finally solve the “cascaded” problem!


With many thanks from users and developers!


Published Tuesday, August 10, 2010 10:21 PM by Davide Mauri
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



RicoFSbr said:

The code exampe and how to use it are very cool and usefull, but there is something that doesn't match for me.

This default behavior (cascade) occurs beyond the defined report parameters. The identation refers to the hierarchy level within a dimension (parameter). Touching the cascade behavior won't mess up the hierarchy identation.

August 16, 2010 5:02 AM

RicoFSbr said:

To be fair....

Unless you use attributes from diferent dimensions in one parameter, which means that you ought to deal with the parameters' identation through different dimensions.

August 16, 2010 5:07 AM

Davide Mauri said:

Hi RicoFSbr

AFAIK Reporting services has no idea of what a hiearchy is. The hierarchical look-and-feel is obtained by padding values on the right, putting spaces in at the beginning of each value's label.

As I showed the post, this is done with the help of the "xxxIndented" calculated field.

If you remove this, you won't have any look-like-hierarchy parameter anymore.

Or maybe I dind't get what you're telling me? :)

August 16, 2010 9:24 AM

RicoFSbr said:

Yes, the hierarchical look-and-feel is obtained by padding values rigth based on the hierarchical level of a given dimension member.

If you just delete the "from" references of other parameters from one given parameter's MDX, which means to remove the "cascade" behaviour (one parameter depends to the previous one), you still have the hierarchical look-and-feel.

August 16, 2010 9:55 AM

Davide Mauri said:

Hi RicoFSbr

I've tried but still I get the "cascade" behaviour (tested on SQL Server 2008). It seems that the behaviour is bound to the Calculated Field, as I said in the post :)

August 16, 2010 10:39 AM

Tomislav Piasevoli said:

Hi Davide,

true, MDX doesn't have many string handling functions, but VBA library does and that assembly is already registered and available on every SSAS server. What you need in this case is a SPACE() function. The appropriate code would have this instead:

MEMBER [Measures].[ParameterCaptionIndented] AS

  Space( 3 * [Measures].[ParameterLevel] ) +

  [Customer].[Customer Geography].CURRENTMEMBER.MEMBER_CAPTION

Full list of VBA functions can be found here: (there's a direct link).

Usage for SPACE() and other functions can be found on many places, here for example: .


August 20, 2010 1:14 PM

Davide Mauri said:

Hi Tomislav

yes, you're right :) I completely forgot to tell about it. I choose the .NET UDF function just beacuse in that way I can make the MDX code simpler, since in my case I had a more complex requirement where I a have to put other information into the displayed label, which is consequently consumed by another application.

Thanks for the feedback!

August 22, 2010 9:20 AM

Mattia said:

Thanks Mauro, very interesting article. We were experiencing the problem of the "unwanted cascade" due to the ParameterCaptionIndented, as you explained. Hardly any other explanation out there on the net..  Now it looks alright! Grazie!

April 3, 2012 12:15 PM

Carl Thompson said:

Hi Davide,

Thanks for the write up.

In your post you have said the following:

"The Expression of that field is very simple:

=Space(3*Fields!ParameterLevel.Value) + Fields!ParameterCaption.Value

but this is enough to make the parameter “cascaded”.

If you remove this, you won’t have any look-like hierarchy parameter, but you’ll also get rid of the “cascade” behavior in the Report Parameters."

Can I ask how you remove this?  Is it just a case of removing the field from the parameter query?

When I do this IS till have cascading parameters.

I also tried amending the parameter query dataset to remove reference to the parent parameter.  However, the cascading parameters still occur.

If I'm not fussed about the hierarchical look and feel how do I solve the problem of the "unwanted cascade"?

Thanks in advance for any help you can provide.

Best Regards,


July 25, 2012 7:17 AM

Davide Mauri said:

Hi Carl

you should simply remove the mentioned Expression from the label field of the parameter.

you can refer to the help page that explains how to ADD Cascading Parameters in order to understand how to remove them:

July 31, 2012 12:22 PM

Biggel said:

I do not have programming backgound. Is there a free software that you can recommend that I can use to compile the MDX User Defined Function as given


October 25, 2012 12:13 AM

Solomon said:

Hey Davide, Thanks a bunch for this article. Its the only one of its kind on all google.  

i followed your method, deleted the ParameterCaptionIndented field then didnt use UDFs but rather followed Tomislav Piasevoli's way and used the built in function space. My query looked like this:

MEMBER [Measures].[ParameterValue]


MEMBER [Measures].[ParameterCaptionIndented] AS

 (Space( 3 * [Measures].[ParameterLevel] ) +


MEMBER [Measures].[ParameterLevel] AS 0

SELECT {[Measures].[ParameterCaption],



      [Measures].[ParameterLevel]} ON COLUMNS


filter([Dim Time].[Year].ALLMEMBERS - [Dim Time].[Year].[All],StrToValue([Dim Time].[Year].CURRENTMEMBER.MEMBER_CAPTION) <= Cint(vba!Year(Now())) AND StrToValue([Dim Time].[Year].CURRENTMEMBER.MEMBER_CAPTION) > Cint(vba!Year(Now()))-3 ) ON ROWS

FROM [Environ DW]

In the end, i get an error on trying to view the report. Error says An unexpecter eror occured while compiling expression. Native compiler return value :'[BC30481]'Class' statement must end with a matching 'Endclass'.'

can u help?


October 25, 2012 3:42 PM

Pete said:

I found that by just making sure none of the parameters have default values (Specify Value option) then I dont experience the cascading issue.

December 19, 2012 7:28 PM

Ani said:

Hi Davide, thanks for the fabulous article. I have deleted the ParameterCaptionIndent and parameter panel has no unwanted refreshing property.

But while I have parameters where Available Values are coming from SSAS Cube and DefaultValues are coming from SQL server database table, the method doesn't work, parameter panel get refreshed every time a parameter selection is being changed. But removing Default value from db, the parameter panel again works right. What will be the approach in this scenario?

October 17, 2013 4:26 AM

Sagar said:

I spent couple of hrs on google finding the cause of it until I came across your blog.

Thanks a lot Davide.

December 8, 2015 12:26 PM

Davide Mauri said:

You're welcome! :)

December 9, 2015 4:03 AM

ram said:

hi guys any possibulity is there hirarchy taken as a parameter in ssrs mdx query

March 16, 2017 4:58 AM

Phelisa said:


I am new with creation of reports using MDX and m having trouble with the functionality.  Not sure if what I am doing is correct or not but I have a parameter where a user can select whether they want to the report according to Financial Period or Calendar Period (User request), so I would like to keep Calendar option/parameter disabled if the Financial Period is selected.  How can I accomplish this using mdx, considering that it has a default cascade.  Is this at all possible?

Your assistance will be greatly appreciated, thanks.

July 4, 2017 9:16 AM

Leave a Comment


About Davide Mauri

Director of Software Development & Cloud Infrastructure @ Sensoria, an innovative smart garments and wearable company. After more than 15 year playing with the Microsoft Data Platform, with a specific focus on High Performance databases, Business Intelligence, Data Science and Data Architectures, he's now applying all his skills to IoT, defining architectures to crunch numbers, create nice user experiences and provide meaningful insights, all leveraging Microsoft Azure cloud. MVP on Data Platform since 2006 he has a very strong background development and love both the ER model and OO principles. He is also a fan of Agile Methodology and Automation, which he tries to apply everywhere he can, to make sure that "people think, machines do".

This Blog


Privacy Statement