Page 1 of 1
Element count in a hierarchy
Posted: Tue Apr 07, 2020 5:49 pm
by Arb1486
Hi All,
Is there a way to get the sum of elements under a hierarchy. I used ELEMENTCOUNT function but it is not working.
Re: Element count in a hierarchy
Posted: Tue Apr 07, 2020 6:01 pm
by ascheevel
You mean COUNT of elements in a hierarchy and by hierarchy you mean a dimension hierarchy and not a consolidation right? What does ElementCount return that you're saying doesn't work, post actual code?
Re: Element count in a hierarchy
Posted: Tue Apr 07, 2020 6:07 pm
by Arb1486
I would like to get count of all the elements under a hierarchy. Based on below example country hierarchy should return element count(Clevel & Nlevel) 8.
Country - Hierarchy
- A - C level
A1 - N level
A2 - N level
-B - C level
B1 - N level
1 - Leaf Level
2 - Leaf Level
3 - Leaf Level
i am actually setting up counter to loop through to dimension hierarchy to unwind elements only under one particular hierarchy.
DimName = 'DimName';
DimCount = DimSiz ( DimName ); ##- Here instead of setting up counter based on dimension size i would need total element count of a dimension hierarchy
i = 1;
While ( i <= DimCount ) ;
DimCount = DimSiz (DimName);
El = DimNm ( DimName , i);
ChildCount = ElCompN(DimName, El) ;
If ( ChildCount <> 0);
y = 1;
While ( y <= ChildCount );
ChildEl = ElComp ( DimName , El, 1);
DimensionElementComponentDelete(DimName, El, ChildEl);
y = y +1 ;
End ;
EndIf ;
i = i + 1 ;
End;
Re: Element count in a hierarchy
Posted: Tue Apr 07, 2020 6:39 pm
by ascheevel
I should have looked at what TM1 version you're on: 10.2. I thought you were referring to dimension hierarchies released in PA2.0. ElementCount is a function used for those hierarchies and is not a valid function in 10.2. You may want to use the
10.2 reference guide for function support.
If all you're trying to do is unwind a consolidation hierarchy, there's a
bedrock process for that to save you from reinventing the wheel.
Re: Element count in a hierarchy
Posted: Tue Apr 07, 2020 7:07 pm
by PavoGa
I believe Bedrock offers a process that does exactly this:
bedrock.hier.unwind.pro. Might want to check it out.
However, to answer the question of determining the number of elements in a consolidation or hierarchy, this will do it efficiently:
Code: Select all
dimDimName = 'SomeDimensionName';
subDimName = EXPAND('tmp_%dimDimName%');
sMDX = 'tm1drilldownmember( {[DimName].[consol]}, ALL, RECURSIVE)';
SubsetCreateByMDX(subDimName, sMDX, 1);
nElementCount = SubsetMDXSet(dimDimName, subDimName, '');
That should do it and leave you with a subset of said elements.
Re: Element count in a hierarchy
Posted: Tue Apr 07, 2020 7:16 pm
by tomok
Here is the code from a process I use to unwind specific nodes in a dimension (where pDim and pNode are parameters):
Code: Select all
#### Define Subset and View Names ###
sSub = 'z_' | sProcess | '_sub';
# Set record count to 0
nCount = 0;
#### Define Subset ####
IF(SUBSETEXISTS(pDim, sSub) = 1) ;
SUBSETDESTROY(pDim, sSub) ;
ENDIF;
sMDX = '{TM1DRILLDOWNMEMBER( {[' | pDim | '].[' | pNode | ']}, ALL, RECURSIVE)}';
SUBSETCREATE(pDim, sSub, 1);
SUBSETMDXSET(pDim, sSub, sMDX);
SUBSETMDXSET(pDim, sSub, '');
#### Step Through Subset ####
nSize = SUBSETGETSIZE(pDim, sSub);
WHILE(nSize > 0);
sElem = SUBSETGETELEMENTNAME(pDim, sSub, nSize);
nParentCount = ELPARN(pDim, sElem);
WHILE(nParentCount > 0);
sParent = ELPAR(pDim, sElem, nParentCount);
IF(ELISANC(pDim, pNode, sParent) = 1);
DIMENSIONELEMENTCOMPONENTDELETE(pDim, sParent, sElem);
ENDIF;
nParentCount = nParentCount - 1;
END;
IF(ELISPAR(pDim, pNode, sElem) = 1);
DIMENSIONELEMENTCOMPONENTDELETE(pDim, pNode, sElem);
ENDIF;
nSize = nSize - 1;
END;
Re: Element count in a hierarchy
Posted: Thu Apr 09, 2020 10:52 am
by Arb1486
Thank You all for your help!! i was able to get the count of elements under a particular hierarchy
