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.
