MDX Help needed: rollup recursively from element to root level?

Post Reply
foogy
Posts: 37
Joined: Fri Nov 16, 2012 5:44 pm
OLAP Product: TM1
Version: 10.2.2 FP6
Excel Version: 2016
Location: Germany

MDX Help needed: rollup recursively from element to root level?

Post by foogy »

Hi there,

I was wondering if there is some smart way to achieve kind of a"reversed" TMDRILLDOWNMEMBER (..., ALL, RECURSIVE) for a given dimension element.

The idea is to let the user drill through a hierarchy via Hyperlinks, and on each click, the active form will use the clicked element as new entry point and drill down the next 2 levels or so. On reaching the end of the new drill, the user can expand further, or - if list of elements gets too long - can click an element and set it as new entry point.

However, this works fine. The thing is, I would like to show the path from hierarchy root down to selected entry element as well, to always keep the top sum visible and visualize the changes on elements on a higher level, once the user changes a figure.

So, given a number of Levels (7 Levels in my case), how would I roll up from let's say an element on Level 4 up to the root element? I could nest several ROLLUP statements, however I do not really like that approach, as the nesting level is dependent on the number of levels and dynamic code generation for the MDX would be rather tricky. A TM1ROLLUPMEMBER (..., ALL, RECURSIVE) would be great, but doesn't exist :roll:

Any suggestion or similar situation to share your experiences? Thanks a lot!
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: MDX Help needed: rollup recursively from element to root level?

Post by gtonkin »

Try something like this:

Code: Select all

HIERARCHIZE({Ascendants([<dim>].[<element>])})
If the elements exists in multiple hierarchies, only the first is returned.
foogy
Posts: 37
Joined: Fri Nov 16, 2012 5:44 pm
OLAP Product: TM1
Version: 10.2.2 FP6
Excel Version: 2016
Location: Germany

Re: MDX Help needed: rollup recursively from element to root level?

Post by foogy »

Thanks, that did exactly what I need. I never used Ascendants command, and obviously thinking how to solve that in a nested way drove me in the wrong direction.

Rgds
Post Reply