Monday, July 25, 2011

MDX: Replacing Null / Empty Values

In the result of an MDX query, you may get some empty or null values which you'd want to treat differently, such as considering them as 0s. For instance in Reporting Services, you might want to filter out any rows with 0 or empty values.

In such cases, you can use the MDX function CoalesceEmpty, which replaces an empty cell value with a given value. For instance:

WITH MEMBER [Measures].[NonEmpty Discount] AS
     COALESCEEMPTY( [Measures].[Discount Amount], 0 )

will replace any empty values of discount amount with zero.

Very useful in certain scenarious.

MSDN Documentation: http://msdn.microsoft.com/en-us/library/ms146080.aspx
Tags: mdx replace replacing get getting null empty values measure zeros

2 comments:

  1. best solution!
    Much better performance than iif isempty...

    ReplyDelete