Element count in a hierarchy

[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.

[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?

[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;

[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.

[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.

[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;

[Arb1486]

Thank You all for your help!! i was able to get the count of elements under a particular hierarchy