Getting all the children of an element

Post Reply
Rtel
Posts: 49
Joined: Tue Nov 13, 2018 10:15 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2013

Getting all the children of an element

Post by Rtel » Tue May 28, 2019 10:27 pm

Hi, In a TI, I want to loop through all the child elements of an element passed as an argument.
For eg if Element name argument to TI is 'Element 1' , TI should iterate through

Element 1.1
Element 1.2
Element 1.3
Element 1.3.1
Element 1.3.2

I tried ElementCount function, but it is throwing error as 'variable ElementCount is UNDEFINED' !!!

I tried getting count using ELCOMPN but it returns 3 for 'Element 1' (instead of 5 that I need) and I want to loop through all the children under Element 1.

If I pass 'Element B' as an argument then it should return only 'Element B' since it does not have any child element.

Logic that I thought of ..
----------------------------------------------
IF Element consolidated

Get the Count of all the children (say iAllChild)

While iCounter < iAllChild

loop thorugh all the Children using the Index

End

else

return same element

endif
----------------------------------------------

Please advise how to accomplish above or if there is a better way.

Thanks,

Rtel
--------------------------------------------------------------------
Sample Dimension

Element A
Element 1
Element 1.1
Element 1.2
Element 1.3
Element 1.3.1
Element 1.3.2
Element 2

Element B

Element C

Rtel
Posts: 49
Joined: Tue Nov 13, 2018 10:15 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2013

Re: Getting all the children of an element

Post by Rtel » Tue May 28, 2019 10:51 pm

To add to my previous question

how to use this MDX from TI to iterate through all the elements in the subset

{TM1DRILLDOWNMEMBER( {[Dimension].[Element]}, ALL, RECURSIVE )}

Thanks

Wim Gielis
MVP
Posts: 2196
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Getting all the children of an element

Post by Wim Gielis » Tue May 28, 2019 11:17 pm

Hello,

If you have an MDX expression, you can use SubsetCreateByMDX to create a subset with said MDX, and then loop through the subset:

Code: Select all

sDim = '...';
sSub = '...';
nMax = SubsetGetSize( sDim, sSub );
nCtr = 1;
While( nCtr <= nMax );

  sEl = SubsetGetElementName( sDim, sSub, nCtr );
  # ...
  nCtr = nCtr + 1;
End;
If you don't have an MDX expression, you can loop over all dimension elements and see if they belong to the chosen element:

Code: Select all

sDim = '...';
nMax = Dimsiz( sDim );
nCtr = 1;
While( nCtr <= nMax );

  sEl = Dimnm( sDim, nCtr );
  If( Elisanc( sDim, 'the consolidated element', sEl ) = 1 );
     # ...
  EndIf;
  nCtr = nCtr + 1;
End;
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

Drg
Posts: 132
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: Getting all the children of an element

Post by Drg » Wed May 29, 2019 10:30 am

Rtel wrote:
Tue May 28, 2019 10:51 pm
To add to my previous question

how to use this MDX from TI to iterate through all the elements in the subset

{TM1DRILLDOWNMEMBER( {[Dimension].[Element]}, ALL, RECURSIVE )}

Thanks
Best solution to create dynamic subset:

Code: Select all

MDX='your mdx';
vLastDim='DimensionName';
vSubset='randomName';
SubsetCreate(vLastDim, vSubset, 1 );
SubsetMDXSet(vLastDim, vSubset, MDX );		
SubsetMDXSet(vLastDim, vSubset, '');
how to iterate subset answered Wim

samba.kesinakurthi
Posts: 1
Joined: Tue May 02, 2017 5:30 pm
OLAP Product: TM1
Version: 10.2.2, PA2.0
Excel Version: 2013

Re: Getting all the children of an element

Post by samba.kesinakurthi » Wed May 29, 2019 2:47 pm

Hello,

You can also try the below as MDX to create the subset for your requirement

SubsetCreateByMDX(SubsetName, '{Descendants([Dimension].[Element 1])}');

Then you can loop through the subset to access the elements

Rtel
Posts: 49
Joined: Tue Nov 13, 2018 10:15 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2013

Re: Getting all the children of an element

Post by Rtel » Wed May 29, 2019 3:56 pm

Hello All, thanks for all your help. Following is the working code that I would like to share. From the responses I understand that there are

1) two diff MDX to create subset
2) two way to create Dynamic subset

( I will try both to understand the difference)

Thanks,

Rtel


-----------------------------------------------------------------------------------------------------------------------------------------------------
sDim = 'TestMember' ;
sElement = '0246 LLC ' ;

sSubSet = ' sub_Member ' ;

SubsetCreateByMDX ( sSubSet , ' {TM1DRILLDOWNMEMBER( {[ ' | sDim | ' ].[ ' | sElement | ' ]}, ALL , RECURSIVE )} ' ) ;

nSubSetSize = SubsetGetSize( sDim, sSubSet ) ;

nSubSetCounter = 1 ;

While ( nSubSetCounter <= nSubSetSize ) ;

sEl_Member = SubsetGetElementName ( sDim, sSubSet , nSubSetCounter ) ;

Asciioutput ('test.cma', 'Element Name', sEl_Member );

# Here I intend to change one of the measure for each of these members using CellPutS

nSubSetCounter = nSubSetCounter + 1;

End;
-----------------------------------------------------------------------------------------------------------------------------------------------------

User avatar
gtonkin
MVP
Posts: 710
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Getting all the children of an element

Post by gtonkin » Wed May 29, 2019 4:54 pm

Glad you found a solution-was going to go ELCOMP direction but saw after posting that you had multiple levels.

Post Reply