Have you ever needed to derive the primary element name from a TM1 alias in Excel or in a Rule? Essentially, do the equivalent of DimensionElementPrincipalName (a TI function only) in a TM1 based Excel report or in TM1 Rules? Here is a really simple solution.
Common Components for Excel and Rule
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.
DimensionElementPrincipalName in Excel
Combining the DIMNM and DIMIX together to get the equivalent of DimensionElementPrincipalName in Excel. In TM1 we have
- a Month dimension with elements of Jan, Feb, Mar, Apr etc., to give the months of the year.
- an alias setup called “Long Name”, which has January, February, March, April etc.
Then, in Excel we have a cell, say cell A3, with the full name of the month “March” in it and we want to derive the principal name “Mar” from that. To do this we cerate a formula combining the DIMNM and DIMIX to get the answer:
=DIMNM ("servername:Month", DIMIX ("servername:Month", A3)). This will give you the element principal name of “Mar” in Excel.
DimensionElementPrincipalName in a TM1 Rule
For the TM1 rule equivalent of DimensionElementPrincipalName, we will be combining the DIMNM and DIMIX with an additional DB function into a rule. In the following rule we have:
- a Period dimension, with elements that are in the syntax of YYYYMM, so for example, “202405” for May 2024. We then have an alias on the Period dimension which is in the syntax MMM-YYYY, so this will display the period as “May-2024”.
- a System cube, with Parameter and Measure dimensions. In Measures we have String, Numeric and ElementID measures. In Parameters we have Reporting Period (amongst others).
- a picklist on the Reporting Period, String cell that displays the current year from the Period dimension in the form of the alias, so Jan-2024, Feb-2024, Mar-2024 etc.
What we want is to populate the Element ID in the System cube for the Reporting Period so it can be used in MDX in other locations.
The syntax will look like this:
['Rebate Period', 'ElementID'] = S: DIMNM ('Period', DIMIX ('Period', DB('System','Rebate Period', 'String')));When, for example, “May-2024” is chosen from the picklist, the rule will put 202405 into the ElementID measure.
Further Information
If you need further discussion on how to use either DIMNM or DIMIX, please see these posts.
Do You Need Advice on Rules or Excel Functions for TM1?
If you’d like some help on how to use these neat tricks, please just reach out to us.
