Excel Functions

TM1 Function – TM1ELLIST

Available In

Excel

Definition

TM1ELLIST returns elements based on a list of elements, MDX or set.

Syntax

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:

  1. MDX
  2. ElementList
  3. SetName
Example

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

This is the All Products by Category set from the Product Type dimension.

1st Demo

First, set up a column as an index.

Go to Name Manager.

Select New.

Enter a name for the range as Set then write out the TM1ELLIST function using the Default subset.

Refers to = TM1ELLIST(“AdvenutureWorks:Product Type”,”All Products by Category”)

Close the Name Manager.

To obtain each field from TM1ELLIST, the Excel function INDEX is used by referencing the named range and an index.

INDEX(Set, A2)

This returns the first element in the list as defined in the set.

By copying down the formula, this will return other elements in the set.

2nd Demo

To get all descendants from the consolidation, All Products by Category. I can use the MDX {DESCENDANTS([Product Type].[Product Type].[All Products by Category])}. I’ll add this in the sheet and name it as a range called MyMDX.

Create a new range via Name Manager.

Set the fields to

  • Name = GetMDX
  • Refers to = TM1ELLIST(“AdventureWorks:Product Type”,””,””,MyMDX)

Use INDEX to obtain each element from the list.

INDEX(GetMDX,A2)

The first element in the query is returned.

Copy the formula down and the rest of the elements will populate

Video Demo