SSRS and MDX: Detecting Missing Fields

For this example I’ll be using the Adventure Works cube running on SQL Server Analysis Services 2008 and Reporting Services 2008.

In case you don’t have the AdventureWorks databases and cubes yet, they’re available at CodePlex.

Scenario

The sales department has asked for a report that displays the number of product items sold during a selected period.  As the company is active in two different markets, both the internet and reseller numbers should be shown.  The figures need to be grouped by product category, with drilldown to product level through subcategory.

Besides the period filter, it should be possible to filter on product category to limit the number of items shown.

Also, the background of the numeric cells should get a color depending on the value in the cell.  Colors range from red for low sales figures to green for high sale volumes.  The ranges are variable and should thus be configurable using 3 threshold parameters.  Following table shows the ranges as the department has requested them:

Value X Background Color
X < LowThreshold Red
LowThreshold <= MiddleThreshold Orange
MiddleThreshold <= X < HighThreshold Yellow
HighThreshold < X Green

 

Selecting The Data

At first sight this seems like a fairly simple report.  So you start building your MDX query using the Query Designer:

MDX Query Designer

Two filters have been specified: one of them is a date range and the other is based on product category.

Visualizing The Data

Then you drag a tablix onto the report body and play around with it until you get to the following:

Tablix in design mode

This is what it looks like when rendered, all seems to work fine:

Rendered report

To get the textbox background coloured based on the thresholds, you’ve produced an expression.  This expression is specified in Textbox Properties > Fill > Fill Color and looks like the following:

=Switch
(
    Fields!Internet_Order_Quantity.Value < Parameters!LowThreshold.Value, "#ff0e0e",
    Fields!Internet_Order_Quantity.Value >= Parameters!LowThreshold.Value
        and Fields!Internet_Order_Quantity.Value < Parameters!MiddleThreshold.Value, "#ff922d",
    Fields!Internet_Order_Quantity.Value >= Parameters!MiddleThreshold.Value
        and Fields!Internet_Order_Quantity.Value < Parameters!HighThreshold.Value, "#fff70f",
    Fields!Internet_Order_Quantity.Value >= Parameters!HighThreshold.Value, "#5cff21"
)

It’s a simple Switch statement using the threshold parameters.

A Missing Field Issue

So you deploy your report to the server for the users to test.  All is quiet, until someone starts complaining that the colouring doesn’t always work, for instance when filtering on Components.  Of course, you don’t always believe what the user says and try it out for yourself:

Rendered report with missing field issue

Indeed, the background is no longer coloured for the internet sales.  On top of that, the BIDS shows a couple of warnings in its output window:

[rsMissingFieldInDataSet] The dataset ‘ProductSales’ contains a definition for the Field ‘Internet_Sales_Amount’. This field is missing from the returned result set from the data source.

[rsErrorReadingDataSetField] The dataset ‘ProductSales’ contains a definition for the Field ‘Internet_Sales_Amount’. The data extension returned an error during reading the field. There is no data for the field at position 4.

Hang on, but I am selecting the field in my dataset, how can it be missing?  Except, this is MDX and OLAP, not SQL and OLTP.  By default, the MDX Query Designer uses NON EMPTY in the SELECT statement.  This means that the rows where there are no values for the selected measures will not be contained in the result set.  It also means that the complete measure will be omitted in the case that there are no values for it in any of the rows, which is the reason for our problem.

You could choose to not use NON EMPTY in the query.  To achieve this using the designer, right-click in the results pane and click the Include Empty Cells item.

MDX Query Designer result pane popup menu

Keep in mind that this will result in more rows in your result set because you’re now selecting all the empty measure cells as well.  Depending on your report requirements this may not be the desired effect.  On the other hand, it could be exactly what you want.  If our sales department had asked that the report should always show all products, even when there are no sales for the period, then we’d need to query the cube in this way.

For the sake of the example (and to save some trees in case the sales department is going to print the report :-) ) we will not choose this option.

Attempt to fix #1

As the field does not always exist, you decide that it’s a good idea to test for its existence.  A field in a resultset has an IsMissing property which serves that purpose.  So you adapt your expression to the following:

=IIF(Fields!Internet_Order_Quantity.IsMissing, Nothing,
    Switch
    (
        Fields!Internet_Order_Quantity.Value < Parameters!LowThreshold.Value, "#ff0e0e",
        Fields!Internet_Order_Quantity.Value >= Parameters!LowThreshold.Value
            and Fields!Internet_Order_Quantity.Value < Parameters!MiddleThreshold.Value, "#ff922d",
        Fields!Internet_Order_Quantity.Value >= Parameters!MiddleThreshold.Value
            and Fields!Internet_Order_Quantity.Value < Parameters!HighThreshold.Value, "#fff70f",
        Fields!Internet_Order_Quantity.Value >= Parameters!HighThreshold.Value, "#5cff21"
    )
)

However, when filtering on Components the same problem still occurs.  How can this be?  Expressions in SSRS are built using Visual Basic where expressions are evaluated completely.  In our case both the True and the False part of the IIF function are evaluated even when it will always be true.

On to another attempt to get this working.

(Attempt to) fix #2

The previous fix attempt has shown that it’s not possible to use an expression for the field validity test.  At least, not in the way we’ve tried until now.  Let’s try using custom code.

Custom code can be added to a report through the Code page in the Report Properties dialog box (accessible through the menu Report > Report Properties… or by right-clicking the report’s yellow background).

Report Properties > Code dialog box

Let’s start with a small extra requirement.  When a measure is not present in a row, such as the Internet Order Quantity for the products in the Components category, the report should display a zero instead of blank space.  To get this done we again need to test on whether or not the field exists in the result set.

The following Visual Basic function accepts a Field object and returns the value of the field when the field exists or zero when the field does not exist.

'returns the field's value or zero if the field does not exist
Public Function GetValue(field as Field) as Long
  If (field.IsMissing) Then
    Return 0
  ElseIf (IsNothing(field.Value)) Then
    Return 0
  Else
    Return field.Value
  End If
End Function

This function can now be used in an expression anywhere in the report.  Here’s what the expression looks like for the Value of the textbox that shows the Internet Order Quantity:

=Code.GetValue(Fields!Internet_Order_Quantity)

The same expression is used for the textboxes that display the sum values:

=Sum(Code.GetValue(Fields!Internet_Order_Quantity))

Attention: the function calls above are passing the actual Field object, not the Value property of the field, so not Fields!Internet_Order_Quantity.Value.

So, on to getting our coloring working as required.  For this we need a function that returns the right color for the given amount.  Something like this:

Public Const ColorLow As String = "#ff0e0e"      'red
Public Const ColorLowMid As String = "#ff922d"   'orange
Public Const ColorMidHigh As String = "#fff70f"  'yellow
Public Const ColorHigh As String = "#5cff21"     'green

Public Function GetColor(field as Field, low as Integer, mid as Integer, high as Integer) as String
  If (field.IsMissing) Then
    Return ColorLow
  ElseIf (IsNothing(field.Value)) Then
    Return ColorLow
  Else
    Select Case field.Value
      Case Is < low
        Return ColorLow
      Case Is < mid
        Return ColorLowMid
      Case Is < high
        Return ColorMidHigh
      Case Is >= high
        Return ColorHigh
    End Select
  End If
End Function

This function accepts a field plus the three threshold values.  Depending on the value of the field and the thresholds, the expected color string is returned.  The red color is returned as well when the field does not exist.

As a good coding practice I’ve created constants for the color strings.  This method allows you to define constants that are available in the whole report – could be interesting if the same colors are used in different parts of a report for instance.

This is the expression used for the BackgroundColor property of the TextBox:

=Code.GetColor(Fields!Internet_Order_Quantity,
    Parameters!LowThreshold.Value,
    Parameters!MiddleThreshold.Value,
    Parameters!HighThreshold.Value)

Again the actual Field object gets passed as first parameter, not just the value.

If we now run the report with a filter on Components, the warnings will still appear in the Output window, but the report will function as expected as the following screenshot shows.  (No Photoshop was used in the making of this screenshot.)  Instead of empty cells the report shows zeroes and the background is coloured even when there are no sales.

Rendered report - fully functional

Extra info can be found in the MSDN page about Using Dataset Field Collection References in Expressions.

Share

Tags: , , , , , , ,

  1. Jerry’s avatar

    The “Include Empty Cells” was the key to my problem. Thanks guy!

    Reply

  2. Mesliensfavoris.fr’s avatar

    Nice post, the tips about the colours are very useful, i use them in every report i develop !

    Reply

  3. Mikhail’s avatar

    Thank you very much for this very detailed guide!

    Cheers!

    Reply

  4. James Grayston’s avatar

    Except, this is MDX and OLAP, not SQL and OLTP. By default, the MDX Query Designer uses NON EMPTY in the SELECT statement. This means that the rows where there are no values for the selected measures will not be contained in the result set. It also means that the complete measure will be omitted in the case that there are no values for it in any of the rows, which is the reason for our problem.
    Thanks for such an informative details about the color uses i love to use them in my all upcoming projects.

    James Grayston
    Webmaster,
    Tinnitus Miracle PDF

    Reply

  5. ganesh’s avatar

    Nice Post

    Reply

  6. Jane’s avatar

    Very helpful – it was the ‘NON EMPTY’ feature of the MDX Query Designer that had me stumped – thanks!

    Reply

  7. Carl Thompson’s avatar

    Hi,

    This is a great post and I have no doubt that I will be in need of using this in the not so distant future.

    However, I have produced a report which requires me to “Include Empty Cells” (in a tranche analysis graph with year_month as a column group). UNfortunately, by including empty cells this appears to have a significant effect on the rendering performance of my report. In addition, some of the larger datasets I hope to interrogate will time out when including empty cells. I can only assume that this is due to the numerous calculations the cube is evaluating??

    Is there any way of increasing performance when having to include empty cells in a dataset?

    I am a complete newbie to MDX and cube analysis after learning SQL and OLTP reporting.

    Any help would be greatly appreciated.

    Carl

    Reply

    1. Valentino Vranken’s avatar

      Hi Carl,

      As you’ve discovered, MDX/OLAP is very fast when you want to retrieve/compare certain specific values but not so fast to retrieve full lists.
      The only possible method to make this faster, afaik, is to use T-SQL on the DWH. If you need the “include empty cells” functionality, think about what you really need. Wouldn’t it be possible to retrieve the same data from the DWH instead of the cube? (I know, not always possible but in some cases it can be easier and faster.)

      Perhaps it’s also possible to write the MDX query differently or change the cube design, but that’s really beyond the scope of a comment here…

      Best regards,
      Valentino.

      Reply

  8. Carl Thompson’s avatar

    Hi Valentino,

    Thanks for your reply.

    I had a feeling that this may be the case.

    At present then, if I really need to “include empty cells”, I will have to consider using T-SQL on the DWH. Long term, I have been advised that there may be an opportunity to redesign the cube to facilitate certain things of the nature described above.

    Thanks for your help.

    Carl

    Reply

  9. NJ’s avatar

    Thanks! Great article.

    Reply

© 2008-2014 BI: Beer Intelligence? All Rights Reserved