Alternate Hierarchies using same name

[jimicron]

Hi all!

I've been working on alternate hierarchies using code on the Prolog tab of a TI process. I have done this a fair amount in the past with just one layer in the alternate hierarchy but using multiple layers is new. I create the alternate hierarchies by looking at the attributes of an "Expense Account" dimension.

I'm including a screenshot. I create the alternate hierarcy based on the "Total Expense 1" and "Total Expense 2" columns. They are both different ways to roll up the accounts. We currently have a PowerPlay model with these hierarchies and that is what I am trying to mimic - but not sure I'm going to be able to do this with code and/or TM1 due to some layers being named the same thing To rename creates issues in itself due to ETL processes, etc. that are dependent on this moving forward. I can get the Labor one just fine where "Direct Labor" and "Indirect Labor" roll up under it. But, for "Materials" I dont' get the second layer (personally, I like this but I have to have the layer for the future b/c we have a PowerPlay model dependent on this). Is this even possible being the same name? I am getting a circular reference error when I run the process.

In the screenshots below, the left hand side is a view of the attributes and then the upper right (green star) is how it's turning out in TM1 but how I need it to turn out is where the blue star is (key into "Materials" and notice it's named the same thing on both layers (levels).

Thanks for your input as always!

Here is the code:

Code: Select all

#****Begin: Generated Statements***
#****End: Generated Statements****

###########################################################################
######################   SORT AND CREATE HIERARCHIES    #########################
###########################################################################

# Sort Elements and Consolidated and then dynamically add consolidated elements based on attribute of DimName

DimName = 'Expense Account';
DimensionSortOrder ( DimName, 'ByName' , 'Ascending' , 'ByHierarchy' , 'Ascending' );

####################   Total Expense with Interco and Prod Output   #######################

Index = 1;
ElmCount = dimsiz(DimName);
While (index <= ElmCount );
sElm = DIMNM(DimName,Index);

TotalExpense2 = ATTRS ( DimName, sElm, 'Total Expense 2' );
TotalExpense1 = ATTRS ( DimName, sElm, 'Total Expense 1' );
Combined = 'Total Expense with Interco and Prod Output';

AltHier1 = Combined;
AltHier2 = TotalExpense1;
AltHier3 = TotalExpense2;
    If( TotalExpense2 @<> '' );
       If( TotalExpense1 @<> '' );
           DimensionElementInsertDirect ( DimName , '' , AltHier1 , 'c' );
           DimensionElementInsertDirect ( DimName , '' , AltHier2 , 'c' );
           DimensionElementInsertDirect ( DimName , '' , AltHier3 , 'c' );
           DimensionElementComponentAdd ( DimName, AltHier1 , AltHier2 , 1 );
           DimensionElementComponentAdd ( DimName, AltHier2 , AltHier3 , 1 );
           DimensionElementComponentAdd ( DimName, AltHier3 , sElm, 1 );
       EndIf;
    EndIf;
Index = Index + 1;
END; 
DimensionUpdateDirect ('Expense Account');

And here are the screenshots:

[declanr]

There are many topics in the forum about just this subject.

Standard approaches can be prefixing each element name with the level it is at e.g.

Code: Select all

0 - Total Expense w/ interco...
   1 - Total Expenses
      2 - Labour
         3 - Direct Labour
         3 - Indirect Labour
      2 - Materials
         3 - Materials

Or prefixing the element name with the name of its parent so you would have "Materials - Materials" as a child of "Materials".

There are a number of other options that you will find by searching the forums, my preference would usually be the number prefixes.