How to Get an Element ID from an Alias in an TM1 Excel Report
Have you ever needed to derive the primary element name from a TM1 alias in Excel? Essentially, do the equivalent of DimensionElementPrincipalName (a TI and Rule function) in a TM1 based Excel report? Here his a really simple solution.
DimensionElementPrincipalName in Excel
The DIMNM Part
The DIMNM returns the principal element from a dimension using the Index of the element. For example, if the index number 47 represents “Mar” in the Month dimension, then DIMNM (“servername:Month’,47) will return “Mar”.
The DIMIX Part
The DIMIX portion of the formula returns the index of an element. For example, if a cell has an alias in use that has “March” in it, then DIMIX (“servername:Month”, “March” ) will return 47.
Combined DIMNM and DIMIX into DimensionElementPrincipalName for Excel
Combining these together we have a cell, say cell A3, with “March” in it and then next to it a formula of =DIMNM (“servername:Month”, DIMIX (“servername:Month”, A3 )).
This will give you the element principal name of “Mar” in Excel.
If you’d like some help on how to use this neat trick, please just reach out to us.