Member-Related Functions
Use Essbase member functions in formulas to specify member lists and ranges, generate member lists, manipulate member names, and work with member combinations across dimensions.
This section discusses creating formulas that refer to members.
Specify Member Lists and Ranges
When using some Essbase calc functions, you may need to input a list of outline members, and/or a range of members. Lists can be comma-separated lists of literal member names, or can be returned by calc functions. Ranges can be specified using colon syntax, or can be returned by calc functions.
For example, the @ISMBR function tests to see if a member that is currently being calculated matches any of a list or range of specified members.
The following table lists the syntax for specifying members:
Table 17-8 Syntax for Specifying Member Lists and Ranges
Member List or Range | Syntax |
---|---|
One member |
The member name. For example:
|
A list of members |
A comma-delimited (,) list of member names. For example:
|
A range of all members at the same level, between and including the two defining members |
The two defining member names separated by a colon (:). For example:
|
A range of all members in the same generation, between and including the two defining members |
The two defining member names separated by two colons (::). For example:
|
A function-generated list of members or a range of members |
For a list of member list contents and corresponding functions, see Generate Member Lists. |
A combination of ranges and list |
Separate each range, list, and function with a comma (,). For example:
or
|
If you do not specify a list of members or a range of members in a function that requires either, Essbase uses the level 0 members of the dimension tagged as time. If no dimension is tagged as time, Essbase displays an error message.
Generate Member Lists
Essbase member set functions allow you to generate member lists that are based on a specified member or member list.
Table 17-9 List of Member Set Functions
Function | Contents of Member List |
---|---|
All ancestors of the specified member, including ancestors of the specified member as a shared member. This function does not include the specified member. |
|
All ancestors of the specified member, including ancestors of the specified member as a shared member. This function includes the specified member. |
|
The ancestor of the specified member at the specified generation or level |
|
All ancestors of the specified member (optionally, up to the specified generation or level), but not the specified member |
|
All ancestors of the specified member (optionally, up to the specified generation or level), including the specified member |
|
All ancestors of the specified list of members (optionally, up to the specified generation or level), but not including the specified members |
|
All ancestors of the specified list of members (optionally, up to the specified generation or level), including the specified members |
|
All base-dimension members that are associated with the specified attribute-dimension member |
|
All base members that are associated with attributes that satisfy the specified conditions |
|
All members whose name string value fall between, and are inclusive of, two specified string tokens |
|
All children of the specified member, but not including the specified member |
|
All children of the specified member, including the specified member |
|
The current member being calculated for the specified dimension |
|
All descendants of the specified member (optionally, up to the specified generation or level), but not the specified member nor descendants of shared members |
|
All descendants of the specified member (optionally, up to the specified generation or level), including the specified member, but not descendants of shared members |
|
All descendants of the specified list of members (optionally, down to the specified generation or level), but not including the specified members |
|
All descendants of the specified list of members (optionally, down to the specified generation or level), including the specified members |
|
All descendants of the specified member (optionally, up to the specified generation or level), including descendants of shared members, but not the specified member |
|
All descendants of the specified member (optionally, up to the specified generation or level), including the specified member and descendants of shared members |
|
Member names that match the specified token name |
|
Member names that do not match the specified token name |
|
Expands a member search by calling a member set function for each member in a member list |
|
All members of the specified generation in the specified dimension |
|
All members of the specified level in the specified dimension |
|
Member names that match the specified pattern. |
|
Separate lists of members to be processed by functions that require multiple list arguments |
|
All members that match the specified wildcard selection |
|
Member names that match the comparison criteria |
|
Parent of the specified member |
|
The member with the name that is provided as a character string |
|
A merged list of two member lists to be processed by another function |
|
The parent of the current member being calculated in the specified dimension |
|
A member list that crosses the specified member from one dimension with the specified member range from another dimension |
|
A list of members from which some members have been removed |
|
All members of the specified generation or level that are above or below the specified member |
|
A member list that identifies all shared members among the specified members |
|
All siblings of the specified member, but not the specified member |
|
All siblings of the specified member, including the specified member |
|
All siblings that precede the specified member in the database outline, but not the specified member |
|
All siblings that follow the specified member in the database outline, but not the specified member |
|
All siblings that precede the specified member in the database outline, including the specified member |
|
All siblings that follow the specified member in the database outline, including the specified member |
|
The sibling at the specified distance from the member |
|
The next, or right-most, sibling of the member |
|
The previous, or left-most, sibling of the member |
|
All members that have a common UDA defined on Essbase Server |
|
A member list that identifies the range of members between (and inclusive of) two specified single or cross-dimensional members at the same level |
Manipulate Member Names
You can work with Essbase member names as character strings in your block storage formulas and calc scripts.
The following table lists character string manipulation functions:
Table 17-10 List of Character String Manipulation Functions
Function | Character String Manipulation |
---|---|
Create a character string that is the result of appending a member name or specified character string to another member name or character string |
|
Return a member name as a string |
|
Return a substring of characters from another character string or from a member name |
Work with Member Combinations Across Dimensions
Cross dimensional operators ->
in Essbase formulas are one way to reference multidimensional data values of specific member combinations. The FIX command may be better for performance.
Use the cross-dimensional operator to point to data values of specific member combinations. Create the cross-dimensional operator using a hyphen (-) and a greater-than symbol (>). Do not include a space between the cross-dimensional operator and members.
Note:
Using the cross-dimensional operator can have significant performance implications. For optimization guidelines, see Cross-Dimensional Operators in Member Formulas.
Below is a simplified illustration of a multidimensional cube, in which Jan is the first column on the X axis, Sales is the fourth and top-most row on the Y axis, and Actual is the first row on the Z axis. In this example, Sales -> Jan -> Actual is the intersection of a single data value.
Figure 17-2 Specifying a Single Data Value

The following example, which allocates miscellaneous expenses to each product in each market, illustrates how to use the cross-dimensional operator. The value of Misc_Expenses for all products in all markets is known. The formula allocates a percentage of the total Misc_Expenses value to each Product -> Market combination. The allocation is based on the value of Sales for each product in each market.
Misc_Expenses = Misc_Expenses -> Market -> Product *
(Sales / ( Sales -> Market -> Product));
Essbase cycles through the database, performing these calculations:
-
Divides the Sales value for the current member combination by the total Sales value for all markets and all products (Sales -> Market -> Product).
-
Multiplies the value calculated in step 1 by the Misc_Expenses value for all markets and all products (Misc_Expenses -> Market -> Product).
-
Allocates the result to Misc_Expenses for the current member combination.