Dynamic SSRS Reporting for Analysis Services Cubes

Andrew Smith Posted on

SSRS Reports developed for Analysis Services cubes can be parameterised by both dimensional meta data and by the meta data available in Analysis Services DMVs.

In this post I show how to write an MDX query for an SSRS report that’s parameterised by both types of meta data, and which also includes a time-based calculated measure with font colour formatting whose formatting flows through to the final report.

This approach is in contrast to using the MDX auto-generated by the SSRS Query Designer’s query mode drag and drop interface, which is good enough for many uses but which doesn’t allow for parameters based upon DMVs (and I’d rather craft my own MDX anyway!)

The end product of the MDX within its host report is:

parameterisedmdxssrsreport1

Points to note:

  • The measure in the left column of each year’s quarter is parameterised, i.e. selected from the “Select Measure” dropdown above the report content
  • The year that the report runs for is also parameterised
  • There is a Year-Over-Year Growth column for the parameterised measure, which is colour coded to indicate growth above & below 100%

The report uses a data source with a “Microsoft SQL Server Analysis Services” connection to a multidimensional cube built upon the AdventureWorksDW2012 database.

I created a dataset against this data source using the SSRS Query Designer in design mode. The first version of the MDX that I wrote was:

WITH
MEMBER [Measures].[Generic Measure] AS StrToMember("[Measures].[Internet Sales Amount]", Constrained)
MEMBER [Measures].[Generic PP] As ([Measures].[Generic Measure], ParallelPeriod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember))
MEMBER [Measures].[Generic YoY %] As IIF([Measures].[Generic PP] = 0, NULL, ([Measures].[Generic Measure] / [Measures].[Generic PP])), FORMAT_STRING = ‘Percent’, FORE_COLOR = IIF([Measures].CurrentMember < 1, RGB(255, 0, 0), NULL)

SELECT
NON EMPTY {[Measures].[Generic Measure], [Measures].[Generic YoY %]} ON COLUMNS,
NON EMPTY {[Date].[Calendar].[Calendar Year] * [Date].[Calendar Quarter].[Calendar Quarter] * [Customer].[Country].[Country].MEMBERS} ON ROWS
FROM
[Adventure Works]
WHERE (StrToMember(@YearParam, Constrained))
CELL PROPERTIES VALUE, FORE_COLOR

The [Generic Measure] calculated measure sets up the mechanism for the report’s measure parameter, the [Generic PP] calculated measure uses the ParallelPeriod() function with a 1 year offset to calculate the prior year value of the generic measure, and the [Generic YoY %] measure calculates the % year over year growth, including a colour coding for values that are under 100%.

The WHERE clause contains a parameter, which was set up via the Query Parameters dialog in Query Designer’s design mode:

parameterisedmdxssrsparam

The side effects of using this dialog are that a new parameter is created for the report, and a hidden dataset is created to provide values for this parameter. (This can be seen by right-clicking the report’s “Datasets” node in the Report Data window and selecting “Show Hidden Datasets”.)

Once the MDX dataset was created I then used it as the source for an SSRS matrix, which provides the layout for the report.

Next comes the introduction of the parameter for the report’s base measure. This cannot be done via the Query Designer’s Query Parameters dialog, because this dialog cannot define a parameter whose values are supplied by a DMV. Instead we have to create the parameter manually, and then incorporate it into the MDX. First we create a new dataset called MeasuresDMV that supplies values for the parameter, which uses the same Analysis Services data source already created and which is defined by this DMV query:

parameterisedmdxssrsdmv1

This query must be entered by clicking on the expression (fx) icon in the above screenshot of the dataset properties dialog, even though it’s not really an expression. It cannot be executed via the Query Designer, so it’s best checking its operation via an SSMS connection to the Analysis Services database.

Once the dataset exists then the new measures parameter (@MeasureParam) can be created, where the parameter’s “Available Values” property page looks like:

parameterisedmdxssrsmeasuresparam

The parameter can then be included in the MDX. It would be convenient if @MeasureParam could be slotted into the MDX in a similar way to how @YearParam was in the above MDX, i.e. changing this from the original query:

MEMBER [Measures].[Generic Measure] AS StrToMember(“[Measures].[Internet Sales Amount]”, Constrained)

to:

MEMBER [Measures].[Generic Measure] AS StrToMember(@MeasureParam, Constrained)

This isn’t possible though because Query Designer throws an error, saying that the @MeasureParam parameter is not declared, i.e. it hasn’t been defined via Query Designer’s Query Parameters dialog. So instead, the whole query needs to be converted to an expression, and the @MeasureParam parameter slotted in that way.

So after opening the MDX query’s Dataset Properties window and clicking the expression (fx) icon, the query needs to be modified to look like:

=”WITH ” +
“MEMBER [Measures].[Generic Measure] AS StrToMember(“”” + Parameters!MeasureParam.Value + “””, Constrained) ” +
“MEMBER [Measures].[Generic PP] As ([Measures].[Generic Measure], ParallelPeriod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember)) ” +
“MEMBER [Measures].[Generic YoY %] As IIF([Measures].[Generic PP] = 0, NULL, ([Measures].[Generic Measure] / [Measures].[Generic PP])), FORMAT_STRING = ‘Percent’, FORE_COLOR = IIF([Measures].CurrentMember < 1, RGB(255, 0, 0), NULL) " +

“SELECT NON EMPTY {[Measures].[Generic Measure], [Measures].[Generic YoY %]} ON COLUMNS, ” +
“NON EMPTY {[Date].[Calendar].[Calendar Year] * [Date].[Calendar Quarter].[Calendar Quarter] * [Customer].[Country].[Country].MEMBERS} ON ROWS ” +
“FROM ” +
“[Adventure Works] ” +
“WHERE (StrToMember(@YearParam, Constrained)) ” +
“CELL PROPERTIES VALUE, FORE_COLOR”

So we can see that the @MeasuresParam parameter has been introduced like this:

StrToMember(“”” + Parameters!MeasureParam.Value + “””, Constrained)

As can be seen I created each line of the MDX as a separate string and concatenated them all together. This isn’t absolutely necessary, but it makes the query much more readable in the dataset expression editor.

This expression-based technique is also useful for situations where the structure of the MDX query needs to be changed via report parameters, e.g. allowing the user to specify which dimension goes on an axis. In this case the MDX set alternatives for the axis are specified as the values of the parameter options, and as above they are slotted into the query dynamically at runtime.

All that’s left now is the colour coding of YoY Growth measure values, to make them red whenever year-over-year growth is less than 100%. Both forms of the MDX query above include this, setting the FORE_COLOR property of [Measures].[Generic YoY %] to RGB(255, 0, 0) if the percentage division is less than 1 (i.e. equivalent to 100%). This colour value is then incorporated into the dataset returned via the MDX CELL PROPERTIES keywords.

SSRS doesn’t make use of FORE_COLOR automatically though – instead the Font property of the SSRS report text box containing the measure needs to be set with an expression that uses the “Color” pre-defined field property:

parameterisedmdxssrscolor

It took some hunting around to find this solution – it’s documented in BOL here.

The colour coding logic could of course have been implemented using SSRS expressions, but this approach has merit especially when the colour coding is defined within a cube’s MDX script (as opposed to being included within the query as I’ve done here).

As is often the case with SSRS, the final solution was relatively lean in terms of its development content – it took some time figuring everything out though, albeit with particular help from Devin Knight’s blog posts (this one in particular).