Davide Mauri : Reporting Services

Reporting Services, MDX and the (unwanted) cascading parameters

Davide Mauri — Tue, 10 Aug 2010 19:21:21 GMT

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:

String to be indented
Indent Level
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!

PASS Summit 2010 – BI Workshop

Davide Mauri — Wed, 30 Jun 2010 12:01:10 GMT

PASS Summit 2010 Pre & Post conference are out! This year I’ll deliver the “Creating BI Solution from A to Z” seminar in which, as the title implies, attendees will see how to create a BI solution starting from scratch. Going through the dimensional modeling and the creation of the Datawarehouse, the implementation of the ETL process with SSIS, the creation of cube with Analysis Services and reports with Reporting Services with, if time permits, also a glance at PowerPivot, attendees will get a solid ground on the whole process that drives the creation of a BI solution.

The workshop will also show all the best practices and the best methodological approach matured in more than 5 years of working in the BI field.

If you’ll be working on BI or you already have a created an initial BI solution and you want to be sure you’re following the right path, this is workshop is for you!

Don’t miss it!

Custom Aggregations Values in SQL Reporting Services with Tablix Filters

Davide Mauri — Fri, 24 Jul 2009 13:20:00 GMT

In these last days I’ve been working on a BI solution for a logistic company. One report I have to create has a particular behavior that can make life a little complex if you’re using Reporting Services: the value used in rows and column totals is not calculated in any way, but is just taken from the database (SSAS in this case), just like any other cell value:

I’ve found this business case in a report that shows all the target values that a salesman has to reach. The total value for a month is not the average nor the sum of the target values for all the regions, but is just a fixed value set by someone (his manager, normally :-)) that represents that overall expected target for that month.

The data returned from the database (no matter if using MDX or SQL; in this example I’m using T-SQL but in reality I’m using MDX) look like the following:

The null values in the region column represents the “total” value for that month, while for date (which is stored in a yyyymmdd integer format), as you may guess, the values for year are represented by the rows with value “0” in the month part.

If you just use these values in a Tablix, using the

this is what you obtain:

clearly the column “20090000” and the no-name row are in the wrong place, since they should be shown as the last and the rightmost values, just like what normally happens with totals.

How to do that? The anwser lies in the usage of Adjacent Groups and Group Filters.

The first step is to remove that row and column from the data. This can be done specifying a filter for the existing groups.

For the reference_data group you just have to say that you want to filter out all the data related to the 20090000 value:

For the region, since we’re using a null value, we have to use a little trick since the operator “is” is not available.

In the Filter Expression field we have to specify something that will turn null to True or False:

=(Fields!region.Value Is Nothing)

then in the Value field you just have to specify “True” (or False if you’re using the “=” operator):

=False

Now, the matrix won’t display the “total” row and column. To show where we want we just have to add one Adjacted Group for columns and one for rows:

So that at the end the matrix will look like this:

Now, if you run the report, the result will be the one we expect: