SUBNM is one of the most used Excel functions in the TM1 toolkit. It allows you to use a subset from a TM1 dimension for selection in an Excel report. For example, you might have a regional dimension that has All Countries, Countries and then states or territories. Then, in Excel, you might have a form that is specific to a country, say Australia. The Aussies aren’t interested in Canadian provinces or German states, so you’d create a subset in TM1 and then use that in a SUBNM in Excel to show a drop-down filter of only Australian states.
SUBNM also results in the subset editor being opened when the cell is double-clicked in Planning Analytics for Excel or Planning Analytics Workspace.
Syntax of SUBNM
The syntax of SUBNM is:
SUBNM(Dimension, Subset, Name, [Alias])
- Dimension – is the source dimension, in the example above it would be “Region”.
- Subset – the subset from the dimension, from above it could be “Aus_States”.
- Name – the element you want to default to when the Excel report is opened. We could choose “NSW”, so it defaulted always to the state of New South Wales.
- Alias – defines an alias from the dimension to be used in the SUBNM when the subset editor is opened.
Another example is this:
=SUBNM("TM1Model:Month", "Months", "Sep")
This will put a drop-down into a cell in Excel and populate it with the the months from Month dimension, where the default display is Sep.
It also allows the user to open the subset editor by double-clicking and then select an element from the dimension.
It is important to note that if someone changes the subset, then the SUBNM in Excel will automatically change. This is both good and bad. Good, in that you can make the subset dynamic. Bad, in that, if someone changes it to something irrelevant to the form, it will display elements that are not useful. So be careful! Consider using a prefix, like a “z” in front of the subset name to indicate that you don’t want someone to change it!