This section contains a listing of query functions by return value. The following return values are possible:
Functions that Return a MemberThe 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. |
Functions that Return a SetThe 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. |
Functions that Return a TupleThe 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. |
Functions that Return a NumberThe 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. |
Functions that Return a DimensionThe following function returns a dimension.
| Dimension | Returns the dimension that contains the input element. |
Functions that Return a LayerThe 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. |
Functions that Return a BooleanThe 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 |