TM1ELLIST

TM1ELLIST returns a downward array vector of values. It is useful because you can get a set of element values from a TM1 model by using a single formula.

Note:

TM1ELLIST does not overwrite or insert into populated cells. It is up to the workbook designer to make sure that a multiple value response is displayed correctly.
TM1ELLIST returns an array of values. However, only the first element displays if the function is entered into a singular value store.
IBM® Planning Analytics for Microsoft Excel does not support default aliases for subsets and the AliasOverride argument. TM1ELLIST does not return alias names when you use the AliasOverride argument or if a subset is defined with an alias.

This worksheet function is valid in worksheets only.

Syntax

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

Argument	Description	Required or Optional
ServerDimension	A dimension that is specified by using the format server:dimension.	Required
SetName	A named set. If this argument is empty, all elements of the dimension are used.	Optional
ElementList	An array of values, which specifies a list of elements to constitute a set. For example, ElementList can reference a cell range. When this argument is supplied, the named set that is specified by the SetName argument is ignored. If this argument is empty, the elements from the set that is specified by the SetName argument are used.	Optional
AliasOverride	A string that defines the alias that is used for the set. When this argument is supplied, it overrides the default alias property that is defined by the subset specified by the SetName argument. If this argument is empty, the alias from the set that is specified by the SubsetName argument is used.	Optional
ExpandAbove	A Boolean flag to turn on or off the set Expand Above property. When this argument is supplied, it overrides the default Expand Above property that is defined by the subset specified by the Set argument. If the argument value is 1, consolidated members expand upward when drilling. If the argument value is 0, consolidated members expand downward when drilling. If this argument is empty, the Expand Above property from the subset that is specified by the Subset argument is used.	Optional
MDXOverride	An MDX statement that applies to the subset specified by the SubsetName or ElementList argument. When this argument is supplied, it overrides the default MDX filter that is defined by the subset that is specified by the SetName argument. If this argument is empty or omitted, the members from the set that is specified by the SetName argument are used.	Optional
IndentRate	An integer value that indicates how many indentations are applied to each level when you drill down on a consolidated member. If the argument value is 0, no auto-indentation is done. IndentRate is relative to the set level of the set elements. If this argument is empty or omitted, one indentation is applied to each level as you drill down on a consolidated member.	Optional
IndentCharacter	IndentChar sets the symbol that is used to provide in-string indentation. The default is the en-space character (the normal space symbol).	Optional

Example

TM1ELLIST("PlanSamp:plan_currency","All currencies")

TM1ELLIST returns an array of elements based on given arguments.

To retrieve all the returned values, create a named range in Excel and enter the TM1ELLIST formula in the Refers To column.

Select the number of cells (based on the return array size) in Excel, type =[namedrange], and press Ctrl+Shift+Enter.

The Excel INDEX function can then be used to extract a single element for the range.