How to fill out result in MDX - Mondrian

Question

In MDX how do you fill out with zeros the null/empty values, example:

If I have a dimension A and two measures X and Y, after MDX query I got this:
("-" represents "null" or "empty" values)

       X   Y
A.A1   1   - 
A.A2   -   2
A.A3   -   -

If I use "NON EMPTY" A.A3 is gone, but when trying to use CoalesceEmpty to fill out with zeros I got this:

       X   Y
A.A1   1   0 
A.A2   0   2
A.A3   0   0

Suppose that there are too many tuples like A.A3 and I don't want to show then, how do i fill out the empty values with zero and do not show the tuples that are empty in all the columns.

the result must be:

       X   Y
A.A1   1   0 
A.A2   0   2

Note: I did try with filter but I didn't get any improvement

Still fairly unclear what you are trying to say or do. please try to clarify what you are trying to do? Change empty things to 0's? remove them entirely if they are all empty? etc? — UpAndAdam
i clarify my question a little, i want to "Change empty things to 0's" and "remove them entirely if they are all empty". thanks — aptiliux

FrankPl FrankPl · Accepted Answer · 2013-10-30T10:08:16

I am not sure if that is supported in Mondrian, but in Analysis Services, you could use HAVING on the row axis like this:

SELECT {Measures.X, Measures.Y}
       ON COLUMNS,
       A.Members
       HAVING Measures.X <> 0 AND Measures.Y <> 0
       ON ROWS
  FROM MyCube

In contrast to NON EMPTY which only looks at empty (i. e. NULL) values, for HAVING, you can state the condition yourself. And in Analysis Services a numeric comparison treats NULL the same way as zero, hence teh above condition excludes null and zero.

Another approach - slightly depending on the client tool - would be to stay with the MDX using NON EMPTY and not to use CoalesceEmpty, but convert the NULL values to 0 via the FORMAT_STRING. This requires the client tool to use the format string or formatted value as delivered by the cube, and not to implement its own number formatting.

How to fill out result in MDX - Mondrian

2 Answers