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:

WITH

[…]

SELECT

[…]

FROM
(
    SELECT
        ( STRTOSET(@ProductProductCategories, CONSTRAINED) ) ON COLUMNS
    FROM
    (
        SELECT
            ( STRTOSET(@CustomerCustomerGeography, CONSTRAINED) ) ON COLUMNS
        FROM
            [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:

WITH
MEMBER [Measures].[ParameterCaption] AS
    [Customer].[Customer Geography].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterCaptionIndented] AS
   SolidQUtils.IndentString(
        [Customer].[Customer Geography].CURRENTMEMBER.MEMBER_CAPTION,
        [Customer].[Customer Geography].CURRENTMEMBER.LEVEL.ORDINAL,
        3)
MEMBER [Measures].[ParameterValue] AS
    [Customer].[Customer Geography].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS
    [Customer].[Customer Geography].CURRENTMEMBER.LEVEL.ORDINAL
SELECT
    {
        [Measures].[ParameterCaption],
        [Measures].[ParameterCaptionIndented],
        [Measures].[ParameterValue],
        [Measures].[ParameterLevel]
    } ON COLUMNS,
     [Customer].[Customer Geography].ALLMEMBERS ON ROWS
FROM
    [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!