THE SQL Server Blog Spot on the Web

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

Davide Mauri

A place for my thoughts and experiences on SQL Server, Business Intelligence and .NET

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:

image

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:

clip_image001[1]

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

image

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:

image

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

clip_image001

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

image

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!

clip_image001[3]

With many thanks from users and developers!

Smile

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

Comments

 

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: http://www.ssas-info.com/analysis-services-articles/50-mdx/2075-vba-functions-in-as2005-mdx (there's a direct link).

Usage for SPACE() and other functions can be found on many places, here for example: http://www.techonthenet.com/excel/formulas/index_vba.php .

Regards.

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,

Carl

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:

http://msdn.microsoft.com/en-us/library/aa337169(v=sql.100)

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

Thanks

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]

       AS [Dim Time].[Year].CURRENTMEMBER.UNIQUENAME

MEMBER [Measures].[ParameterCaptionIndented] AS

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

[Dim Time].[Year].CURRENTMEMBER.MEMBER_CAPTION  )

MEMBER [Measures].[ParameterLevel] AS 0

SELECT {[Measures].[ParameterCaption],

      [Measures].[ParameterCaptionIndented],

      [Measures].[ParameterValue],

      [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?

Tnamx

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

Leave a Comment

(required) 
(required) 
Submit

About Davide Mauri

Davide Mauri - MCP, MCAD, MCDBA, MCT, MVP on SQL Server - has worked with SQL Server since version 6.5, and his interests cover the whole platform, from the Relational Engine to Analysis Services, from architecture definition to performance tuning. He also has a strong knowledge of XML, .NET and the Object Oriented Design principles, which allows him to have the correct vision and experience to handle development of complex business intelligence solutions. Having worked as a Microsoft Certified Teacher for many years, Davide is able to pass all his knowledge to his co-workers, allowing his team to deliver high-quality solutions. He currently works as a Mentor for SolidQ and can be found speaking in many Italian and internationals events.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement