This section contains a listing of query functions by return value. The following return values are possible:
The following functions return a member or a member value expression.
Ancestor | Returns a member that is an ancestor of the specified member, at a specified generation or level. |
ClosingPeriod | Returns the last descendant of a layer, or the last child of the Time dimension. |
Cousin | Returns a child member at a matching outline level and location as a member from another parent. |
CurrentMember | Returns the current member in the input dimension. Current is in the context of query execution mechanics. Use in combination with iterative functions such as Filter. |
DefaultMember | Returns the default member in the input dimension. |
FirstChild | Returns the first child of the input member. |
FirstSibling | Returns the first child of the input member's parent. |
Lag | Using the default order of members in a database outline, returns a member that is n steps behind the input member. |
LastChild | Returns the last child of the input member. |
LastSibling | Returns the last child of the input member's parent. |
Lead | Using the default order of members in a database outline, returns a member that is n steps past the input member. |
NextMember | Returns the member (in the same layer) that is one step past the input member. |
OpeningPeriod | Returns the first descendant of a layer, or the first child of the Time dimension. |
ParallelPeriod | Returns a member from a prior time period as the specified or default time member. |
Parent | Returns a member's parent. |
PrevMember | Returns the member (in the same layer) that is one step prior to the input member. |
The following categories of functions return a set or a set value expression.
Functions in this category derive their results without getting any further information from the cube.
CrossJoin | Returns a cross-section of two sets from different dimensions. |
Distinct | Deletes duplicate tuples from a set. |
Except | Returns a subset containing the differences between two sets. |
Generate | For each tuple in set1, return set2. |
Head | Returns the first n members or tuples present in a set. |
Intersect | Returns the intersection of two input sets. |
Subset | Returns a subset from a set, in which the subset is a numerically specified range of tuples. |
Tail | Returns the last n members or tuples present in a set. |
Union | Returns the union of two input sets. |
Functions in this category derive their results using metadata information from the cube.
Ancestors | Returns a set of ancestors up to a specified layer or distance. |
Attribute | Returns all base members that are associated with the specified attribute member. |
Children | Returns all child members of the input member. |
Descendants | Returns the set of descendants of a member at specified layers. |
DrilldownByLayer | Drills down members of a set that are at a specified layer. |
DrilldownMember | Drills down on any members or tuples of <set1> that are also found in <set2>. |
DrillupByLayer | Drills up the members of a set that are below a specified layer. |
DrillupMember | Tests two sets for common ancestors, and drills up members in the first set to the layer of the ancestors which are present in the second set. |
Extract | Returns a subset containing only the tuples of a specified dimensionality. |
Hierarchize | Sorts members according to the default member ordering as represented in the database outline. |
LastPeriods | Returns a set of members ending either at the specified member or at the current member in the time dimension. |
MemberRange | Returns the range of members positioned between two input members (inclusive) at the same generation or level. |
Members | Returns a set of all members of a given dimension, hierarchy, or layer. |
PeriodsToDate | Returns a set of dynamic-time-series members from the beginning of a given layer up to a given member in that layer (or up to the default member); or, returns members up to the current member of the Time dimension. |
RelMemberRange | Returns a set based on the relative position of the specified member. |
Siblings | Returns the siblings of the input member. |
Uda | Returns all members that share a specified user-defined attribute. |
WithAttr | Returns all base members that are associated with an attribute member of the specified type. |
xTD | Functions returning period-to-date values. |
Functions in this category derive their results using data values from the cube.
BottomCount | Returns a set of n elements ordered from smallest to largest, optionally based on an evaluation. |
BottomPercent | Returns the smallest possible subset, with elements listed from smallest to largest, of a set for which the total results of a numeric evaluation are at least a given percentage. |
BottomSum | Returns the smallest possible subset, with elements listed from smallest to largest, of a set for which the total results of a numeric evaluation are at least a given sum. |
Case | Performs conditional expressions. |
Filter | Returns those parts of a set which meet the criteria of a search condition. |
IIF | Performs a conditional test, and returns an appropriate numeric expression or set depending on whether the test evaluates to true or false. |
Order | Sorts members of a set in order based on an expression. |
TopCount | Returns a set of n elements ordered from largest to smallest, optionally based on an evaluation. |
TopPercent | Returns the smallest possible subset, with elements listed from largest to smallest, of a set for which the total results of a numeric evaluation are at least a given percentage. |
TopSum | Returns the smallest possible subset, with elements listed from largest to smallest, of a set for which the total results of a numeric evaluation are at least a given sum. |
The following functions return a tuple.
CurrentTuple | Returns the current tuple in a set. Current is in the context of query execution mechanics. Use in combination with iterative functions such as Filter. |
Item | Extracts a member from a tuple. |
The following functions return a value.
Numeric-value functions take a set of values as input,
and return a single value.
Exception: Todate, which returns value from a string.
Avg | Returns the average of values found in the tuples of a set. |
CoalesceEmpty | Returns the first non #Missing value from the given value expressions. |
Case | Performs conditional expressions. |
Count | Returns the count of the number of tuples in a set. |
IIF | Performs a conditional test, and returns an appropriate numeric expression or set depending on whether the test evaluates to true or false. |
Max | Returns the maximum of values found in the tuples of a set. |
Min | Returns the minimum of values found in the tuples of a set. |
NonEmptyCount | Returns the count of the number of tuples in a set that evaluate to nonempty values. |
Sum | Returns the sum of values of tuples in a set. |
ToDate | Converts a date string to a value that is usable in calculations. |
Mathematical functions take a single value as input,
and return a single value.
Exception: Ordinal, which returns a value
from a level reference.
Abs | Returns absolute value of an expression. |
Exp | Returns the exponent of an expression. |
Factorial | Returns the factorial of an expression. |
Int | Returns the next lowest integer value of an expression. |
Ln | Returns the natural logarithm of an expression. |
Log | Returns the logarithm of an expression to a specified base. |
Log10 | Returns the base-10 logarithm of an expression. |
Mod | Returns the modulus (remainder value) of a division operation. |
Ordinal | Returns a number indicating depth in the hierarchy. |
Power | Returns the value of the numeric value expression raised to power. |
Remainder | Returns the remainder value of the numeric value expression. |
Round | Rounds a numeric value expression to the specified number of digits. |
Truncate | Removes the fractional part of a numeric value expression, returning the integer. |
The following function returns a dimension.
Dimension | Returns the dimension that contains the input element. |
The following functions return a layer. A layer is used to group the members of a dimension by hierarchical depth.
In Essbase, a layer is either a generation or a level, indicated by a name or a number.
Generation | Returns the generation of the input member. |
Generations | Returns the generation specified by the input numerical depth and the input dimension or hierarchy. |
Level | Returns the level of the input member. |
Levels | Returns the level specified by the input numerical depth and the input dimension or hierarchy. |
The following functions return a Boolean (TRUE or FALSE).
Is | Returns TRUE if two members are identical. |
IsAccType | Returns TRUE if the current member has the associated accounts tag. |
IsAncestor | Returns TRUE if the first member is an ancestor of the second member. |
IsChild | Returns TRUE if the first member is a child of the second member. |
IsEmpty | Returns True if the value of an input numeric-value-expression is #MISSING. |
IsGeneration | Returns TRUE if the member is in a specified generation. |
IsLeaf | Returns TRUE if the member is a level-0 member. |
IsLevel | Returns TRUE if the member is in a specified level. |
IsSibling | Returns TRUE if the first member is a sibling of the second member. |
IsUda | Returns TRUE if the member has the associated UDA tag (user-defined attribute). |
IsValid | Returns TRUE if the specified element validates successfuly. |
©2004 Hyperion Solutions Corporation. All Rights Reserved. http://www.hyperion.com |