MDX: Showing a zero count for non-existing members

Question

I'm still a newbie at MDX, so maybe this will be a simple question.

I want to do something like see sales revenue by product: more specifically, products are classified in "classes" (where class can be A,B or C), so I want to see the amount of sales of class A products, class B and so on. If the Dimension "Product" has one Hierarchy "Classification" with level "Class", suppose the MDX query, for the sake of simplicity, is as follows:

SELECT 
[Measures].[Sales] on COLUMNS,
[Product].[Classification].[Class].members on ROWS
FROM [Cube]

Problem is, if there's no record with class='C',for example, in Data table Product, there will be no member 'C', right? In that case, I'd like to show a zero count for that. So, instead of having:

    | Sales
  A    1000
  B    200

I'd like:

    | Sales
  A    1000
  B    200
  C    0

Any help? Thanks in advance!

Marc Polizzi Marc Polizzi · Accepted Answer · 2013-10-04T00:26:40

Problem is, if there's no record with class='C',for example, in Data table Product, there will be no member 'C', right?

As your select does not contain NON EMPTY ... ON ROWS, then 'C' should be returned with an empty cell as its 'Sales' value. It is possible that your 'client' tool is then ignoring it.

What you can do is define a calculated measure using the CoalesceEmpty() function to return a ZERO value instead of empty :

WITH    
   MEMBER [Measures].[Sales - X] as 
            CoalesceEmpty( ( [Measures].[Sales] ).Value, 0 )

SELECT    
    [Measures].[Sales - X] on COLUMNS,  
    [Product].[Classification].[Class].members on ROWS 
FROM [Cube]

MDX: Showing a zero count for non-existing members

1 Answers