SQL Server Reporting Services and MDX: Detecting Missing Fields

For the following example I'll be using SQL Server Reporting Services 2008 and the Adventure Works cube running on SQL Server Analysis Services 2008.  In case you don't have the AdventureWorks databases and cubes yet, they're available at CodePlex.

I could have called the article "How to implement conditional formatting using SQL Server Reporting Services 2008", but I didn't.  Because that's not the only thing what this article is trying to illustrate.  The initial purpose of this article is to show you how you can detect missing fields when retrieving data from an OLAP cube.  On top of that, the article also shows how thresholds can be used to highlight values in a table.

1. 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:

 

2. Selecting The Data

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

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

3. Visualizing The Data

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

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

 
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"
                      )

Select all Open in new window

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

4. 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:


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.

 
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.

5. 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"
                          )
                      )

Select all Open in new window

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.

6. (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).


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

Select all Open in new window

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)

Select all Open in new window

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

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

Select all Open in new window

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

Select all Open in new window

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)
                      	

Select all Open in new window

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.

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

As usual, if you appreciate what you've just read, don't hesitate to click that YES button down below!

Thank you for reading what I write,
Valentino.