1. Calculation and Query Reference for Oracle Essbase
  2. Calculation Functions
  3. Calculation Function List
  4. @EXPAND

@EXPAND

The @EXPAND calculation function for Essbase expands a member search by calling a member set function for each member in the list. The returned members are added to the existing set. Duplicate members are not removed. This function can be used on unique and duplicate-name outlines.

Syntax

@EXPAND (mbrSetFunction, mbrList,[, genLevNum][, LAYERONLY | ALL][, topMbrinHierarchy])

Parameters

mbrSetFunction

One of the following member set functions, which return a list of members:

mbrList

A comma-delimited list of members grouped together using @LISTor a member set function (such as @DESCENDANTS) that returns a list of members.

genLevNum

Optional: This argument applies only if you specify @ANCESTORS, @IANCESTORS, @DESCENDANTS, or @IDESCENDANTS for mbrSetFunction.

The integer value that defines the absolute generation or level number up to which to select members. A positive integer defines a generation number. A value of 0 or a negative integer defines a level number.

LAYERONLY

Optional: This argument applies only if you specify @ANCESTORS, @IANCESTORS, @DESCENDANTS, or @IDESCENDANTS for mbrSetFunction.

Returns only those members at the specified generation or level (genLevNum) that match the selection criteria.

If you specify this argument, you must specify genLevNum.

ALL

Optional: This argument applies only if you specify @ANCESTORS, @IANCESTORS, @DESCENDANTS, or @IDESCENDANTS for mbrSetFunction.

Returns all of the members that match the member selection criteria, starting with the specified top member (topMbrinHierarchy).

If you specify this argument, you must specify topMbrinHierarchy.

topMbrinHierarchy

Optional: This argument applies only if you specify @EQUAL for mbrSetFunction.

A fully qualified member name on which to base the member search. The specified member and its aliases, and all of its descendants, are included in the search.

If you specify @EQUAL for mbrSetFunction, and you do not specify topMbrinHierarchy, Essbase searches the entire outline.

Example

The following examples are based on the following duplicate-name outline:

Product
   100
      100–10
         100–10–10
      100–20
      100–30
   200
      200–10
      200–20
      200–30
   300
      300–10
      300–20
   Diet
      100–10
         100–10–11
      200–10
      300–10
   Bottle
      200–10
      300–20
@EXPAND("@DESC", @LIST("Product"), -1, LAYERONLY)

Returns all of the members under the Product dimension that are at level 1, which are [100].[100-10], [Product].[200], [Product].[300], [Diet].[100-10], and [Product].[Bottle].

@EXPAND("@EQUAL", @EXPAND("@CHILDREN", @LIST("[product].[100]", "[product].[200]")), , ,"Product")

Essbase first executes the inner @EXPAND function—@EXPAND("@CHILDREN", @LIST("[product].[100]", "[product].[200]"))—which expands the member list to include all of the children of members 100 and 200 (a total of six members). Then Essbase executes the outer @EXPAND function, which searches the Product hierarchy for a match with any of the six members.

See Also