TM1SET

TM1SET is a powerful function that supports alternate hierarchies in IBM® Planning Analytics for Microsoft Excel and IBM Planning Analytics TM1® Web.

A TM1SET is a type of function that is similar to DBRWs and SUBNMs in the sense that the function can have an input of a set expression and return a member of a set. Not only is TM1SET hierarchy aware, but it is also aware of any cell references that are being used in it. When you modify an argument in a TM1SET that references other cells, the referenced cells also update.

The output of a TM1SET is configurable, which makes it dynamic and powerful. You can embed TM1SET functions in new and existing reports for greater versatility and customization.

Member selector and set editor integration

Depending on the Planning Analytics component you are using, when you click a TM1SET cell, a drop-down member selector or set editor displays that allows you to change the definition of the set. You can also edit the TM1SET formula manually.

Note: If you are using Planning Analytics Workspace 2.0.85 and TM1 Web 2.0.85, you can use the new experience member set menu from TM1SET cells to select members and open the set editor. To use the member set menu, enable the feature flag EnableCarbonMemberSelector in the features.json file.

The drop-down member selector, set editor, or member set menu are available only if the TM1SET cell is in a tracked area named range (apply tm2\\_tracked). You cannot open any of these items from untracked TM1SET cells in Planning Analytics for Excel, TM1 Web, or Planning Analytics for Workspace websheets. You also cannot see the TM1SET title dimensions in untracked cells when you export websheets in TM1 Web.

Refer to Embedding a TM1SET function for how to apply tracked area named range to TM1SET.

True reference traversal

TM1SET has true reference traversal capability in Planning Analytics for Excel and TM1 Web. TM1SET’s arguments can be written inline, or they can be written in other cells and referenced in the TM1SET function. When you change the TM1SET function in the set editor (for example, when you select a different hierarchy) that change applies to TM1SET’s output values and to any cells that might be referenced in the formula.

Syntax

The arguments in the following syntax link to the corresponding argument descriptions in Table 1. 

=@TM1SET(Host, Server, Dimension, Hierarchy, SetExpression, SelectedElement, SessionSetOut, ActiveDisplay, DisplayModeEnum)
Table 1. TM1SET arguments
Argument Description
Host

Enter the data source URI.

The host argument allows for simultaneous use of multiple systems, even when database names would otherwise collide.
Server

Enter the TM1 server name.
Dimension

Enter the dimension name.
Hierarchy

Enter the hierarchy name.
SetExpression

Enter the MDX based dynamic set expression.
SelectedElement

Enter the selected element in the set to display.
SessionSetOut

The given output range for Session Set ID. This value is generated by the server after the set expression is changed in the set editor.

This is an optional argument and can be " " or null.
ActiveDisplay

Enter the corresponding alias for the member element.

This is an optional argument and can be " " or null.
DisplayModeEnum
Output types affect the output value of the formula. Enter one of the following output types:
  • SESSIONSET - Displays the session set ID for the set that is created in TM1 server
  • MUN - Displays the MDX member unique name
  • MEMBERDISPLAY - Displays element name or alias

This is an optional argument and can be " " or null.
Note: For TM1Set to work, the value defined in the ActiveDisplay argument must follow these guidelines:
  • The name must not be longer than 128 characters.
  • The name must start with either an alphabetical character, a unicode character from the categories L or Nl, or an underscore.
  • The name must only contain alphanumeric characters, unicode characters from the category L, Nl, Nd, Mn, Mc, Pc, Cf, or an underscore.

    For more information, see TM1 object naming conventions.

Examples

=@TM1SET("http://mydatasource.ibm.com","Planning Sample", "plan_business_unit","plan_business_unit","{Filter( {TM1SUBSETALL( [plan_business_unit] )}, [plan_business_unit].CurrentMember.Level.Ordinal = 1)}", "10100","","BusinessUnit", "memberdisplay")

Embedding a TM1SET function

The TM1SET function can be added to your report from Planning Analytics for Excel:

  1. Connect to a data source in Planning Analytics for Excel.
  2. Select a cell and enter the TM1SET formula.
    Note: For guidance on arguments, type =TM1SET() in a cell and press Enter. Click Insert function to see all the arguments that you need to enter for TM1SET.
    TM1SET function arguments

You can track a TM1SET cell by applying the tracked area named range to the cell in your report. Gesture support in TM1SET cells, such as double-clicking the cell to open the set editor, is available only if the cell is tracked.

For gesture support to work in Planning Analytics for Excel, TM1 Web, and Planning Analytics Workspace websheets, apply the tm2\\_tracked named range to the TM1SET cell.
  1. In Planning Analytics for Microsoft Excel, go to Formulas and click Name Manager.
  2. Click New and enter the following information:
    1. Name: tm2\\_tracked
    2. Scope: Select the sheet where the cell is located.
    3. Refers to: Enter the cell reference.
  3. Click OK.
Creating the tracked area named range