TM1SET function#

TM1SET is the hierarchy-aware equivalent of SUBNM and DIMNM, with additional output modes. It defines an MDX set for a dimension hierarchy and returns the selected member, its unique name, or the session set id.

TM1SET is a native TM1 / Planning Analytics function.

Signature#

=TM1SET(host, server, dimension, hierarchy, setExpression, [selectedElement], [sessionSetOut], [activeDisplay], [displayMode])

Arguments#

#	Argument	Type	Required	Permitted values and notes
1	`host`	String	Yes	The host environment. Ignored by Reportworq, but must be non-blank and not an Excel error value.
2	`server`	String	Yes	The server instance.
3	`dimension`	String	Yes	The dimension. A non-existent dimension is a hard error.
4	`hierarchy`	String	Yes	The hierarchy. A non-existent hierarchy is a hard error.
5	`setExpression`	String	Yes	The MDX set expression.
6	`selectedElement`	String	No	The member to return. Blank returns the first set member; a member not in the set returns the first member. Case- and space-sensitive.
7	`sessionSetOut`	Range	No	An output cell for the session set id. Referencing more than one cell is a hard error. See the limitation below.
8	`activeDisplay`	String	No	An alias attribute to display when `displayMode` is `MEMBERDISPLAY`.
9	`displayMode`	String	No	`MEMBERDISPLAY` (default), `MUN`, or `SESSIONSET`. An unrecognized value falls back to `MEMBERDISPLAY`.

TM1SET takes five to nine arguments.

What happens when an argument is wrong#

Situation	Result
Fewer than five arguments	Hard error - too few parameters
More than nine arguments	Hard error - too many parameters
Blank `host`, `server`, `dimension`, `hierarchy`, or `setExpression`	Hard error
A non-existent dimension or hierarchy	Hard error
`sessionSetOut` references more than one cell	Hard error - the cell shows `#VALUE!`

Notes and limits#

The sessionSetOut cell is not written in the current build. The session set id is computed and can be returned by setting displayMode to SESSIONSET, but Reportworq does not write it into the sessionSetOut output cell. Read the id from the function result rather than relying on the output cell.

Examples#

=TM1SET("*", "PlanTM1", "Region", "Region", "{[Region].[Region].Members}", "Northeast", , , "MEMBERDISPLAY")

IBM Planning Analytics functions - the function index and the shared error model.
SUBNM function - the subset-based member selector.

Feedback on this page

Comments, questions, requests, or something missing or unclear? Email us - the page you are on is filled in for you.

Email feedback on this page

Or write to support@reportworq.com directly.