Search code examples
mdxiccubeiccube-reporting

IcCube Reporting : How to stop drill in a hierarchy?

When we query a dimension/hierarchy with multiple levels, the drill is by default allowed thru all the levels of that hierarchy... For example, in a hierarchy made of : Continent/Country/State/City.

What is the solution to restrict the drill to the State level (i.e. not showing the city level) without having to create a new hierarchy without City ?

I heard that this can be done using fonctions in the schema scripts to "flatten" the hierarchy.

Can someone give me a live example of that ?

Solution

  • The first is mapping our navigation as an MDX function (you could also copy&paste the code but there is no reuse). Somethink like this (Sales example) :

    CREATE FUNCTION navigationDemo(_x) AS 
            {
            CASE  
            WHEN _x.hierarchy is [Customers].[Geography] THEN [Product].[Product].[Category]
            WHEN _x.hierarchy is [Product].[Product] THEN [Time].[Year].[Year]
            ELSE {}
            END
            }

    Once this is done we can go to our widget, in our example a table, and define a Drilldown Strategy. It should be of type mdxExpression and we can insert into the MDX Expression our newly created function

    navigationDemo( $member )   // where $member is the clicked member in the table

    Do no forget to set the 'Filter by' as we want the new data to be filtered by the clicked member.

    enter image description here