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