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 method involves using a combination of DIMNM and DIMIX.

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.

Further Information

If you need further discussion on how to use either DIMNM or DIMIX, please see these posts.

If you’d like some help on how to use this neat trick, please just reach out to us.