How to get top parent of an element ?

[CellPutN]

Hello all,

I wonder if there's a more efficient way to retrieve the top parent (by top parent I mean the 'highest' consolidated element) of an element.

In other word, for all leaf-level elements in a source dimension, I want to retrieve the top parent/ancestor and recreate it in a target dimension.

Code: Select all

#==================================================
#  Retrieve Top Parent and Create it if doesn't exist  
#  Maximum of 4 level is allowed here.
#==================================================

sParent1 = ELPAR( sDimSource, vElement, 1 );
IF(ELPARN( sDimSource, sParent1 ) <> 0 );
    sParent2 = ELPAR( sDimSource, sParent1, 1 );
    IF(ELPARN( sDimSource, sParent2 ) <> 0 );
        sParent3 = ELPAR( sDimSource, sParent2, 1 );
        IF(ELPARN( sDimSource, sParent3 ) <> 0 );
            sParent4 = ELPAR( sDimSource, sParent3, 1 );
            sTopParent= sParent4;
        ELSE;
            sTopParent = sParent3;
        ENDIF;
    ELSE;
        sTopParent = sParent2;
    ENDIF;
ELSE;
    sTopParent = sParent1;
ENDIF;

IF(DIMIX( sDimTarget, sTopParent ) = 0);
    DimensionElementInsertDirect( sDimTarget, '', sTopParent, 'C' );
ENDIF;

Thanks,
Jack

[macsir]

Use loop to get it

[lotsaram]

I think there is a pretty easy way to get your result via MDX
E.g.

Code: Select all

{Tail( {Ascendants([Dimension].[Hierarchy].[Element])}, 1 )} 

In TM1 an element may have multiple parents within a hierarchy so if there are multiple parent branches you may need the intersect between the ancestors and all top node elements in the dimension ...

Code: Select all

{Intersect( 
    {Ascendants([Dimension].[Hierarchy].[Element])}, 
    {Filter( {TM1SUBSETALL( [Dimension].[Hierarchy] )}, [Dimension].[Hierarchy].CurrentMember.Parent.Name="")} 
)}

Replace "Element" with the actual element name you wish to inspect then create a temporary subset with SubsetCreateByMDX. I can't see why this won't work.

But I would also kinda want to ask why do you need to do this? As in this requirement would indicate the dimension structure is a mess. In a well structured system you should KNOW what the top node(s) are for any given element.

[CellPutN]

Thanks Lotsaram !

And it was for a very specific task for one of my customer

[macsir]

Looks like Ascendants only returns all parents in the first rollup?

1.PNG (2.83 KiB) Viewed 11212 times

2.PNG (2.03 KiB) Viewed 11212 times

[lotsaram]

Looks like Ascendants only returns all parents in the first rollup?1.PNG2.PNG

Yes seems to be the case. I just checked with Ancestors function and seems to be the same restriction.

[macsir]

Looks like Ascendants only returns all parents in the first rollup?1.PNG2.PNG

Yes seems to be the case. I just checked with Ancestors function and seems to be the same restriction.

Thanks for confirming. I am thinking if we can get same MDX function as what this "rollup" button does, it would be good. It can return all parents obviously.

1.PNG (17.66 KiB) Viewed 11179 times

[PavoGa]

This will do it:

Code: Select all

FILTER( FILTER( TM1SUBSETALL([test_ANC]), [test_ANC].currentmember.parent.name = ''), 
    TM1TupleSize(INTERSECT({[test_ANC].[e]}, DESCENDANTS({[test_ANC].currentmember})).item(0)) > 0)

This assumes we are looking for all the top level consolidations of 'e'.

And this:

Code: Select all

FILTER( FILTER( TM1SUBSETALL([test_ANC]), [test_ANC].currentmember.parent.name = ''), 
    TM1TupleSize(INTERSECT(TM1FILTERBYLEVEL(TM1SUBSETALL( [test_ANC]), 0), DESCENDANTS({[test_ANC].currentmember})).item(0)) > 0)

returns the top level consolidations for all leaf elements. Would only eliminate top level elements without any leaves...

[PavoGa]

This returns ALL the ancestors of 'e':

Code: Select all

FILTER( EXCEPT(TM1SUBSETALL([test_ANC]), TM1FILTERBYLEVEL(TM1SUBSETALL([test_ANC]), 0)), 
    TM1TupleSize(INTERSECT({[test_ANC].[e]}, DESCENDANTS({[test_ANC].currentmember})).item(0)) > 0)

[macsir]

This returns ALL the ancestors of 'e':

Code: Select all

FILTER( EXCEPT(TM1SUBSETALL([test_ANC]), TM1FILTERBYLEVEL(TM1SUBSETALL([test_ANC]), 0)), 
    TM1TupleSize(INTERSECT({[test_ANC].[e]}, DESCENDANTS({[test_ANC].currentmember})).item(0)) > 0)

Thanks, PavoGo. Learnt a lot from your MDX. Follow your thought, I think we can do it in a simpler way.

Without self

Code: Select all

EXCEPT(  EXCEPT(   TM1SUBSETALL([test2]), TM1FILTERBYLEVEL(TM1SUBSETALL([test2]), 0 ) ),  { DESCENDANTS( {[test2].[d]} )  }   )

With self

Code: Select all

EXCEPT(  EXCEPT(   TM1SUBSETALL([test2]), TM1FILTERBYLEVEL(TM1SUBSETALL([test2]), 0 ) ),  { DESCENDANTS( {[test2].[d]}, 0, AFTER )  }   )

But still, I couldn't find a way to return its all parents only ( excluding any grandparents ), just like the way the button is doing.

[PavoGa]

I thought you were trying to find the top or root level consolidations for a given leaf element. The MDX queries you state as simpler specifically reference a consolidation ('d') according to the example you provided while I thought you were interested in determining ancestors of a leaf element. I believe these may provide what you are looking for (assuming you are really looking for ancestors of a leaf) only if (1) you know the parent of the targeted leaf element, (2) it only has one parent, (3) want all consolidations except the one specified, not just the top level and (4) there are no other consolidation lines without that leaf element. There may be other problems, but this is just off the top of my head.

It seems your queries work specifically for your example. I provided you with queries that work for any given leaf element even if the dimension has a ragged hierarchy.

[macsir]

Yes, you are right. My query is not right. Anyway, learnt how to use TM1TupleSize functon.

[PavoGa]

Yes, you are right. My query is not right. Anyway, learnt how to use TM1TupleSize functon.

Good deal! Glad I could help.