TM1 Function for Excel, TM1ELLIST
Excel
12
TM1ELLIST returns elements based on a list of elements, MDX or set.
TM1ELLIST(TM1ServerName:Dimension, SetName, ElementList, AliasOverride, ExpandAbove, MDXOverride, IndentRate, IndentCharacter);
– TM1ServerName = Name of the TM1 Server
– Dimension = Name of the Dimension (REQUIRED)
– SetName = Set name (OPTIONAL)
– ElementList = An array of elements. For example {“4”;”40”;”400”} (OPTIONAL)
– AliasOverride = Name of an alias (OPTIONAL)
– ExpandAbove = A Boolean field. 1 for consolidations to expand above or 0 to expand below. As a default, it is set to 0 (OPTIONAL)
– MDXOverride = An MDX expression (OPTIONAL)
– IndentRate = Enter a number to indicate the indentation applied to each level. A value of 0 applies no indentation. As a default, the indentation of 1 is applied (OPTIONAL)
– IndentCharacter = Set a character for the indentation. As a default, a space is used. (OPTIONAL)
NOTE: There are three methods to set the list: SetName, ElementList and MDX. The order in precedence is MDX, ElementList then SetName
1st Demo – return the set All Products by Category from the Product Type dimension
2nd Demo – return all descendants from the consolidation All Products by Category from the Product Type dimension