MDX, SSAS

How To Display 0’s Instead of NULL in your SSAS Cube & MDX Query Results

6 Comments

I’ve done a decent amount of SSAS and MDX query development over my career and one of the requirements that continually pops up is to display zeros instead of NULL when browsing the cube. Take the following query as an example:

Select {[Date].[Calendar Year].Children} on 0,

NON EMPTY {[Geography].[State-Province].members} on 1

From [Adventure Works DW]

Where [Measures].[Reseller Sales Amount]

 

And here are the results:

image

There are a couple ways display 0’s instead of NULL. The first way is to create logic in your MDX script that uses a CASE statement of an IIF expression that manually checks for empty cube space using the ISEMPTY function, similar to the following example:

SCOPE ([Measures].[Reseller Sales Amount]);

THIS=IIF(ISEMPTY([Measures].[Reseller Sales Amount]),0,[Measures].[Reseller Sales Amount]);

END SCOPE;
The problem with this method is that any empty cube space will be populated with 0’s, potentially exploding your results! Rows that should not be displayed are now displayed! In the image below, I’ve highlighted a row that does not have any results that should not be displayed. Queries that usually only return a handful of results could now potentially display thousands or millions of rows! Yikes! And that’s something we definitely want to avoid.
image
There is another way that we can fill the NULL’s with a 0 or any other value we choose: Through the FormatString property. The FormatString property accepts four arguments separated by a semicolon (;). The first argument is how positive value should be formatted, the second is how negative values should be formatted, the third argument is how 0 values should be formatted, and the fourth argument is how NULL should be formatted. The fourth argument is the one we are interested in!
Here is an example of how I am formatting the Reseller Sales Amount: “#,##0.00;-#,##0.00;;0”.
image
By configuring the FormatString property this way, we are no longer filling the empty space in the cube with 0’s, rather we are configuring the measure to only display a 0 should a NULL value appear in the results. Check it out.
image
No more NULL values. If you’d rather display a non-numeric value, you can do this: “#,##0.00;-#,##0.00;;Zero”
image
If you thought that was helpful or enjoyed reading my blog, leave a comment. Feel free to post any questions, as well!

6 thoughts on “How To Display 0’s Instead of NULL in your SSAS Cube & MDX Query Results”

  1. how to handle zero values. i dont want to dispaly zero values. please help me how to avoid 0 values.

    1. Probably the easiest way to handle this is to use Scope to specify this behavior for a specific measure. So something like this:

      SCOPE ([Measures].[Reseller Sales Amount]);

      THIS=IIF([Measures].[Reseller Sales Amount]=0,NULL,[Measures].[Reseller Sales Amount]);

      END SCOPE;

      But just be aware that doing this could hide valid 0 values in your data leaving the users wondering.

  2. I have a calculated measures and i want to display the correct grant total for pivot excel report . So for that what should we do. how to write the scope statement to display the correct grand total in excel pivot report

    1. Hey Rajiv, the grand total line is going to be the All member for the attribute in question, so I would start by writing a scope statement to target the All level and the specific measure. I hope this helps! Thanks for reading. 🙂

  3. Thanks Dustin this was useful in understanding the MDX Format String property. My requirement was to show 0’s as NULL’s so i used the below formatstring “#,##0.00;-#,##0.00;;”. It works fine and 0’s are showing as NULL”s when i browse the cube. But when i use that measure as one of the columns in the MDX drillthrough(when user doubleclicks) its still showing up as 0. Looks when excel is creating the new drilldown worksheet its overwriting any formatting changes in cube. My only option was to manually go to Excel Options > Uncheck “Show a zero in cells that have zero value” property. But this is not at a workbook level, its at a worksheet level only. Any ideas? Thanks

    1. I’m not sure what excel is doing with the mdx when it’s querying the data source when you invoke the drill down. I’d run a profiler trace and invoke the drill down and then look at the query. That may provide some insight into what is happening.

Comments are closed.