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
Getting all the children of an element
-
- Posts: 59
- 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
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
how to use this MDX from TI to iterate through all the elements in the subset
{TM1DRILLDOWNMEMBER( {[Dimension].[Element]}, ALL, RECURSIVE )}
Thanks
-
- MVP
- Posts: 3105
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Getting all the children of an element
Hello,
If you have an MDX expression, you can use SubsetCreateByMDX to create a subset with said MDX, and then loop through the subset:
If you don't have an MDX expression, you can loop over all dimension elements and see if they belong to the chosen element:
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;
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
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Regular Participant
- Posts: 159
- 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
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, '');
-
- Posts: 2
- 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
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
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
Cheers
Samba
Samba
-
- Posts: 59
- 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
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;
-----------------------------------------------------------------------------------------------------------------------------------------------------
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;
-----------------------------------------------------------------------------------------------------------------------------------------------------
- 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: Getting all the children of an element
Glad you found a solution-was going to go ELCOMP direction but saw after posting that you had multiple levels.