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!
Read more IBM Planning Analytics Tips & Tricks:
IBM Planning Analytics Tips & Tricks: Account Numbering
IBM Planning Analytics Tips & Tricks: Mekko Chart
IBM Planning Analytics Tips & Tricks: PAW Explorations – Row & Column Headers
