INFOR.GET.ELEMENTS

This function is an enhanced variant of the INFOR.GET.ELEMENT function. It returns one or more attribute values for one or more elements. You can retrieve, for example, children of a certain element and ask for both the caption, MEMBER_CAPTION, and id, MEMBER_UNIQUE_NAME.

INFOR.GET.ELEMENTS supports these features:

  • Dynamic arrays

    The retrieved values are spilled to the adjacent cells.

  • Multiselection

    You can specify multiple unique names of elements from the same hierarchy

  • Retrieval of all attributes
  • MDX expressions

    You can, for example, specify [HierarchyUN].DefaultMember to return the default element attributes or elementUN.Children to return multiple child elements.

    Note: If a set of elements is returned from an MDX expression, the values are automatically spilled and the corresponding attributes are returned for each element separately.
  • Indention

    You can specify string that is used for level indention of elements and indicates which attributes should be indented.

    By default, only the first attribute is indented, and two spaces are used.

  • Paging

    There are two arguments that can be used for paging behavior. You can limit the number of records shown and define which page is used.

Formula spilling

If the function returns multiple values, the values are spilled into adjacent blank cells. The default spilling direction is for elements to spill vertically downwards and the attribute values to spill horizontally to the right.

Syntax

The function has this syntax:

=INFOR.GET.ELEMENTS("spillByColumns", "data_connection", "cube", [attribute], "hierarchy" {, "number_of_records", "page", "indentString", [indentIndices], [element], ...})

This table shows the parameters and their descriptions:

Parameters Description
spillByColumns An optional Boolean value for spill direction. Specify true to spill by columns or false to spill by rows. Default value: false.
data_connection Represents the database unique name. It is a string or a cell reference.
cube Represents the cube unique name. It is a string or a cell reference.
attribute Represents the attribute unique name. It is a matrix of strings or cell-references. If not specified, MEMBER_CAPTION is the default value.
hierarchy Represents the hierarchy unique name. It is a string or a cell reference. If the hierarchy is not specified, the default value is an empty string and the hierarchy is parsed from the element's unique name.
number_of_records A numeric value or a cell reference that serves as a limit of number of records that are shown. Optional parameter, with the default value: 0 that represents no limit.
page Numeric value or the cell reference that defines which page to show. Optional parameter, with the default value: 1.
indentString String used for indentation. Optional parameter, with the default value: ' ', two spaces.
indentIndices Matrix of indices or cell references that represents one or more attributes to be indented. Optional parameter with the default value 1. By default, the first attribute is indented. Other possible values: 0 to indent all Attributes. -1 to use no indention.
element Any number of matrices of element unique names or cell references from the selected hierarchy. The maximum number is 249 due to Excel limit.
Note: If the hierarchy is not specified, the element unique name must be specified in its long form, for example, [PERIOD].[ALL YEARS].[2018].

Return values

This function returns string values that represent one or more element attributes such as caption, MEMBER_CAPTION, id, MEMBER_UNIQUE_NAME for one or more elements. If you provide 2 element unique names and two attributes it will return a two by two matrix as a result.

For example, if you specify Sales from North America for 2016, 2017, and 2018, the function returns a one by three matrix of summary values for selected years.