[ How to suppress missing rows in a pivottable drill down action ]
Is it possible in icCube version 5.0.2 to have a navigation strategy that drills-down to its children, but only shows children with data (so a NON EMPTY).
I managed to get this working in the previous versin 4.8 using custom MDX but the MDX syntax has changed and my solution does not work anymore!.
Enclosed a picture on the live demo dashboard called "pivottable" indicating what I would like to achieve.
If I verify the MDX that is generated I see a new custom MDX syntax, like:
axis 0 {....}
axis 1 {drill down parent MDX statement}
The text between curly brackets is the drilldown MDX statement. What I want to achieve, MDX-wise, is a NON EMPTY in front of the statement, e.g.:
axis 1 NON EMPTY {drill down parent MDX statement}
Any suggestion how to achieve this is welcomed.
This example can be found here
Answer 1
This issue has been solved in 5.1. I was not looking in the right direction.
To enforce a non empty drilldown on children do the following:
- open the widget
- select the tab Navigation
- select drilldown strategy mdxExpression
- and for the MDX expression type: non empty $member.children
And voila, you only see the rows with data
Checking the generated MDX shows:
SELECT
... ON COLUMNS
... ON ROWS
FROM ...
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORMAT_STRING
axis 0 ...
axis 1 NON EMPTY ...