<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

SQL Server Reporting Services and MDX: Detecting Missing Fields

Published on
18,486 Points
8,586 Views
4 Endorsements
Last Modified:
Awarded
ValentinoV
1998: C++ - SQL Server 6.5
2000-2007: C++, VB6, C#, java - SQL Server 7.0-2005
2008-...: SQL Server 2005-2016
2014-2016: MVP Data Platform
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:

 Threshold ranges as requested by Sales Department

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:

 MDX 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:

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

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:

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.

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

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

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

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)

Open in new window


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

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

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

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)
	

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.

 Rendered report - fully functional
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.
4
Comment
Author:ValentinoV
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
0 Comments

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Join & Write a Comment

This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month