IBM Planning Analytics Tips & Tricks: TM1Ellist

October 26, 2021

Have you ever wanted to use Excel to create a list of elements within an IBM Planning Analytics dimension? If so, the TM1ELLIST function is your answer! This function is used to return a list of elements with parameters to define the look of the output.

The syntax of the function is:

TM1ELLIST(ServerDimension, [SetName], [ElementList],
[AliasOverride], [ExpandAbove], [MDXOverride], [IndentRate], [IndentCharacter])

ServerDimension (required) –defined in the format of server:dimension
SetName (optional) – an existing subset
ElementList (optional) – an array of values that specifies a list of elements; this can reference a cell range.
AliasOverride (optional) – an existing alias name
ExpandAbove (optional) – 1 = expand upward when drilling; 0 = expand downward
MDXOverride (optional) – an MDX expression
IndentRate (optional) – how many indentations are applied to each level when drilling; 0 = no indentation
IndentCharacter (optional) – a symbol that will be used instead of a blank space

Example: I have dimension called bpmPeriod that contains a subset called “2016Qtrs”. The subset shows the year and the four quarters.

The function could be used to give the following result in Excel:

The TM1ELLIST function allows you to easily see details of your dimension elements within your Excel environment.

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Author

Lee Lazarow

View all posts