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

embedded picture

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