Page 1 of 1

How to get top parent of an element ?

Posted: Sun Feb 10, 2019 2:48 pm
by 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

Re: How to get top parent of an element ?

Posted: Sun Feb 10, 2019 8:08 pm
by macsir
Use loop to get it

Re: How to get top parent of an element ?

Posted: Mon Feb 11, 2019 6:49 am
by 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.

Re: How to get top parent of an element ?

Posted: Mon Feb 11, 2019 3:17 pm
by CellPutN
Thanks Lotsaram !

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

Re: How to get top parent of an element ?

Posted: Mon Feb 11, 2019 9:10 pm
by macsir
Looks like Ascendants only returns all parents in the first rollup?
1.PNG
1.PNG (2.83 KiB) Viewed 9456 times
2.PNG
2.PNG (2.03 KiB) Viewed 9456 times

Re: How to get top parent of an element ?

Posted: Mon Feb 11, 2019 9:55 pm
by lotsaram
macsir wrote: Mon Feb 11, 2019 9:10 pm 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.

Re: How to get top parent of an element ?

Posted: Tue Feb 12, 2019 8:52 pm
by macsir
lotsaram wrote: Mon Feb 11, 2019 9:55 pm
macsir wrote: Mon Feb 11, 2019 9:10 pm 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
1.PNG (17.66 KiB) Viewed 9423 times

Re: How to get top parent of an element ?

Posted: Wed Feb 13, 2019 12:46 pm
by 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...

Re: How to get top parent of an element ?

Posted: Wed Feb 13, 2019 1:17 pm
by 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)

Re: How to get top parent of an element ?

Posted: Thu Feb 14, 2019 12:59 am
by macsir
PavoGa wrote: Wed Feb 13, 2019 1:17 pm 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. :?

Re: How to get top parent of an element ?

Posted: Thu Feb 14, 2019 2:25 pm
by 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.

Re: How to get top parent of an element ?

Posted: Thu Feb 14, 2019 8:25 pm
by macsir
Yes, you are right. My query is not right. Anyway, learnt how to use TM1TupleSize functon. :P

Re: How to get top parent of an element ?

Posted: Thu Feb 14, 2019 8:34 pm
by PavoGa
macsir wrote: Thu Feb 14, 2019 8:25 pm Yes, you are right. My query is not right. Anyway, learnt how to use TM1TupleSize functon. :P
Good deal! Glad I could help.