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.
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:
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:
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" )
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:
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.
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).
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:
The same expression is used for the textboxes that display the sum values:
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.
Extra info can be found in the MSDN page about Using Dataset Field Collection References in Expressions.
- Book: SQL Server 2012 Reporting Services Blueprints
- SQL Server Days 2013 – Data Cleansing: Download
- SQL Server Days 2013
- Exploring the System.Object Package Variable [SSIS]
- T-SQL Tuesday 46: Contraptions!
- Creating Multi-Column Reports: The Top-Down Version [SSRS]
- Formatting Dates [SSRS]
- Community Day – Data Visualization Tips & Tricks: Download
- Formatting Numbers [SSRS]
- Community Day 2013: Data Visualization Tips & Tricks
- November 2013 (2)
- October 2013 (1)
- September 2013 (2)
- July 2013 (2)
- June 2013 (2)
- May 2013 (3)
- March 2013 (3)
- February 2013 (2)
- January 2013 (2)
- December 2012 (2)
- November 2012 (3)
- October 2012 (2)
- August 2012 (2)
- July 2012 (2)
- June 2012 (2)
- May 2012 (2)
- April 2012 (3)
- March 2012 (4)
- February 2012 (4)
- January 2012 (2)
- December 2011 (2)
- November 2011 (2)
- October 2011 (1)
- September 2011 (3)
- August 2011 (2)
- June 2011 (2)
- May 2011 (3)
- April 2011 (3)
- March 2011 (3)
- February 2011 (2)
- January 2011 (5)
- December 2010 (1)
- November 2010 (3)
- October 2010 (3)
- September 2010 (2)
- August 2010 (4)
- July 2010 (2)
- June 2010 (4)
- May 2010 (6)
- April 2010 (3)
- March 2010 (3)
- February 2010 (11)
- January 2010 (9)
- December 2009 (2)
- November 2009 (3)
- October 2009 (3)
- September 2009 (4)
- August 2009 (6)
- July 2009 (2)
- June 2009 (3)
- May 2009 (7)
- April 2009 (3)
- March 2009 (3)
- February 2009 (5)
- January 2009 (4)
- December 2008 (2)
- November 2008 (3)
- October 2008 (1)
- September 2008 (1)
- August 2008 (4)
- July 2008 (3)