SUBNM is one of the most used Excel functions in the TM1 toolkit. It can allow you to use a subset from a TM1 dimension for selection purposes in an Excel report, TM1Web or Planning Analytics Workspace analysis. For example, there might be a regional dimension that has All Countries, Countries and then states or territories. Then, in Excel, there may be a form that is specific to a country, say Australia. The Canadian provinces or German states are probably not interesting for Aussies. If this was the case, 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.
Additionally, SUBNM results in the subset editor opening 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])
Where:
- 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 default element when the report is opened in Excel. 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.
Example
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.
Finally, on the example above, we have “TM1Model:Month” where we are referring ot the TM1 Model directly in the formula. Remember that we could change this to a dynamic formula that pulls the model name into the formula – very useful when you have non-production and production environments!
Notes re SUBNM
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!
IBM’s Post
Here is a link to IBM’s post on SUBNM. If this topic is still a bit unclear, go check out the IBM link. They have a few useful examples over the sample TM1 models that might be useful.
