The TM1SET Excel formula is similar to the SUBNM formula except that TM1SET is hierarchy aware and has formula reference traversal.
The syntax for TM1set is:
TM1set(uri_identifier,serverName,dimensionName,hierarchyName,setExpression,selectedElement,sessionSetOut,activeDisplay,displayModeEnum)
Follow these steps to build a file with the TM1SET formula.
1 – In a new workbook, set up the following labels:
2 – Uri Identifier: the URL to the TM1 server
In cell c2, enter =TM1PRIMARYDATASOURCE()
3 – Server Name: the name of the TM1 server
In cell C3, enter the name of the TM1 server (Demo2 in this example)
4 – Dimension Name: The dimension TM1SET will use
In cell C4, enter the name of the dimension (Account in this example)
5 – Hierarchy Name: The name of the hierarchy TM1SET will use. If not using hiearchies, repeat the dimension name.
In cell C5, enter the name of the hierarchy (Account in this example)
6 – Set Expression: The Set expression for TM1Set. This expression can be hardcoded or copied from the subset editor.
In cell C6, input the set expression. In this example, we are looking for all children of the Advertising parent, the set expression is:
{DRILLDOWNMEMBER({[Account].[Account].[Net Income After Allocations^Net Income^EBITDA^Operating Expenses^Advertising]} , {[Account].[Account].[Net Income After Allocations^Net Income^EBITDA^Operating Expenses^Advertising]} , RECURSIVE)}
7 – Selected Element: The initial element to display
In cell C7, enter Advertising
8 – Session Set Out: TM1SET will fil this in with a unique identifier
Leave cell C8 blank
9 – Active Display: The alias to use for element display
In cell C9, enter the alias name (CodeName in this example)
10 – Display Mode Enum: You can alter the output of the TM1SET formula. Valid options for this parameter are:
SESSIONSET: a unique session set ID created by the server
MUN: the MDX member unique name
MEMBERDISPLAY: the element name or alias
In cell C10, enter MemberDisplay
Once filled in, your template should look like this:
Build the TM1SET formula using the parameters. In cellB14, input this TM1SET formula:
=@TM1SET(C2,C3,C4,C5,C6,C7,C8,C9,C10)
The TM1SET formula will show a drop down which is the sets of elements returned by the Set expression parameter.
Set Editor
TM1SET will not automatically open the subset editor when double clicked. To enable this functionality, you will need to assign the TM1SET formula to a specific named range
To enable the Set Editor:
1 – Select the TM1SET formula
2 – Go to Formulas > Name Manager
3 – Click on New
4 – Create a new name range, named tm2\\_tracked
5 – The scope must be the sheet name
6 – Click OK
7 – Close the Name Manager
Now you can double click on the TM1Set formula and the set Editor will appear
Formula Reference Traversal
This is a feature where TM1 set will change any cell reference that it is using. In this example, we are using the CodeName alias in C9.
1 – Double click the TM1SET formula
2 – In the set editor change the alias to Member ID
3 – Click on Apply
Note how C9 now shows MEMBER_NAME and the Session Set Out cells have been filled out. TM1SET will ‘reach back’ to the source cells and fill them out with the selections made in the set editor.
Revelwood is an IBM Gold Business Partner with more than 30 years of experience designing, developing, implementing and maintaining IBM Planning Analytics environments. We focus on solutions for the Office of Finance and have partnered with clients of all sizes across all industries to optimize, enhance and expand their use of Planning Analytics. Revelwood’s Planning Analytics team consists of PA experts with decades of experience, and we have been recognized via awards including IBM Champion status.
Stay up to date with PA – sign up for our weekly Planning Analytics Tips & Tricks newsletter, subscribe to our YouTube channel, and join our IBM Planning Analytics All-Stars group on LinkedIn.
Read more IBM Planning Analytics Tips & Tricks:
IBM Planning Analytics Tips & Tricks: Learn the Excel Focus Cell
IBM Planning Analytics Tips & Tricks: Making Pivot Tables More Interactive
