TM1ELLIST function#

TM1ELLIST returns the element names for a dimension and spills them down a column, indented by hierarchy level. Use it to build a dynamic row axis of element names for a report region. The list can come from a named subset or an MDX set.

TM1ELLIST is a native TM1 / Planning Analytics function.

Signature#

=TM1ELLIST("Server:Dimension", subset, elementList, alias, expandAbove, mdxStatement, indentRate, indentCharacter)

Arguments#

#	Argument	Type	Required	Permitted values and notes
1	`Server:Dimension`	String	Yes	`"server:dimension"`.
2	`subset`	String	No	A named subset to list. Leave blank for the "All" subset.
3	`elementList`	Range	No	A cell range of element names. Works with or without the `alias` argument, so a three-argument call is valid. Invalid names are dropped silently (see the note below).
4	`alias`	String	No	Reserved and ignored. The function accepts this argument but does not use it.
5	`expandAbove`	Boolean	No	Whether consolidations expand above their children. Defaults to `FALSE`.
6	`mdxStatement`	String	No	An MDX set expression to list.
7	`indentRate`	Integer	No	Indent steps per hierarchy level. Defaults to `1`. Set to `0` to switch indenting off. A negative value is treated as `0`.
8	`indentCharacter`	String	No	The character repeated for each indent step. Defaults to a space.

TM1ELLIST takes one to eight arguments. When more than one source is supplied, the source is chosen in this precedence: MDX set, then element list, then subset.

Which source to use#

Drive the list from a subset, an MDX set, or a cell range of element names:

=TM1ELLIST("PlanTM1:Region", "Active Regions")
=TM1ELLIST("PlanTM1:Region", , , , , "{TM1SUBSETALL([Region].[Region])}")
=TM1ELLIST("PlanTM1:Region", , A2:A6)

The last form, with no alias argument, is valid: it returns the list in the range and no longer returns #VALUE!.

Note: invalid element names in an element list are dropped from the output silently, with no warning.

What happens when an argument is wrong#

For the shared error model, see how Reportworq handles errors.

Situation	Result
No arguments	Hard error - missing parameters
More than eight arguments	Hard error - too many parameters
Blank server or dimension	Hard error
A non-numeric `indentRate`	Hard error
A negative `indentRate`	Treated as `0` (no indenting)
An invalid element in an explicit element list	Dropped from the output silently, with no warning
An invalid subset (when the subset source is used)	Hard error

Notes and limits#

Indenting adds leading blanks by default. With indentRate at its default of 1, each spilled name is prefixed with one indent character (a space) per hierarchy level. Set the seventh argument to 0 if you do not want the leading blanks. This is worth knowing when moving a report to a Planning Analytics v12 server, where a hierarchy's default display levels can differ from v11 and surface indenting you did not have before.
Leave enough empty rows below the formula for the spilled names.

IBM Planning Analytics functions - the function index and the shared error model.
SUBNM function - return a single element from a subset.

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.