This section lists all of the Essbase functions, grouped by function type.
A Boolean function returns TRUE or FALSE (1 or 0, respectively). Boolean functions are generally used in conjunction with the IF command to provide a conditional test. Because they generate a numeric value, however, Boolean functions can also be used as part of a member formula.
Boolean functions are useful because they can determine which formula to apply based on characteristics of the current member combination. For example, you may want to restrict a calculation to those members in a dimension that contain input data. In this case, you preface the calculation with an IF test that is based on @ISLEV(dimName, 0).
If one of the function parameters is a cross-dimensional member; for example, @ISMBR(Sales->Budget), all parts of the cross-dimensional member must match all parts of the current cell to return a value of TRUE.
@ISACCTYPE @ISANCEST @ISCHILD @ISDESC @ISGEN @ISIANCEST @ISICHILD @ISIDESC @ISIPARENT |
@ISISIBLING @ISLEV @ISMBR @ISPARENT @ISSAMEGEN @ISSAMELEV @ISSIBLING @ISUDA |
Relationship functions look up specific values within the database based on current cell location and a series of parameters. You can use these functions to refer to another value in a data series. Relationship functions have an implicit current member argument; that is, these functions are dependent on the current member's position.
@ANCESTVAL @ATTRIBUTEBVAL @ATTRIBUTESVAL @ATTRIBUTEVAL @CURGEN @CURLEV @GEN |
@LEV @MDANCESTVAL @MDPARENTVAL @PARENTVAL @SANCESTVAL @SPARENTVAL @XREF |
These functions perform specific mathematical calculations. Mathematical functions define and return values that are based on selected member expressions. These functions cover many basic statistical functions and return numeric results that are based on supplied member values. Advanced statistical functions are included in the statistical functions category.
@ABS @AVG @EXP @FACTORIAL @INT @LN @LOG @LOG10 @MAX @MAXS |
@MIN @MINS @MOD @POWER @REMAINDER @ROUND @SUM @TRUNCATE @VAR @VARPER |
Member set functions return a list of members. This list is based on the member specified and the function used.
When a member set function is called as part of a formula, the list of members is generated before the calculation begins. The list never varies because it is based on the specified member and is independent of the current member.
If a member set function (for example, @CHILDREN or @SIBLINGS) is used to specify the list of members to calculate in a calculation script, Essbase bypasses the calculation of any Dynamic Calc or Dynamic Calc And Store members in the resulting list.
Only the @ATTRIBUTE and @WITHATTR functions can use attribute members or members of the Attribute Calculations dimension as parameters in member set functions.
You can use cross-dimension expressions such as ("1998":"2001" -> @Levmbrs (Year, 0)). The cross-dimensional operator is associative (x -> y) -> z=x -> (y -> z), but not commutative because x -> y = y -> x is a set, but the order of elements is different.
Range functions take a range of members as an argument. Rather than return a single value, these functions calculate a series of values internally based on the range specified.
Financial functions execute specialized financial calculations.
Some range functions recognize the optional parameter rangeList as the last parameter. If rangeList is not given, the level 0 (leaf) members from the dimension tagged as Time become the default range. If no dimension is tagged as Time and the last parameter is not given, Essbase reports a syntax error.
Examples of valid range lists are:
Mar99 | A single member |
Mar99, Apr99, May99 | A comma-delimited list of members |
Jan99:Dec99 |
A level range. |
Q1_99::Q4_2000 | A generation range. A generation range includes the members defining the range and all members that are within the range and of the same generation. |
Q1_99::Q4_2000, FY98, FY99, FY2000 | A generation range and a comma-delimited list |
@SIBLINGS(Dept01), Dept65:Dept73, Total_Dept |
A member set function and one or more range lists |
Financial functions never return a value; rather, they internally calculate a series of values based on the range specified and write the results to a range of cells. Thus, you cannot apply any operator directly to the function.
These functions allocate values that are input at the parent level. The values are allocated across child members in one or more dimensions, based on specified criteria. These functions consolidate the common tasks that are required to perform allocations in Essbase.
@ALLOCATE @MDALLOCATE |
Forecasting functions manipulate data for the purpose of smoothing, interpolating, or calculating future values. Forecasting functions are often used in planning, analysis, and modeling applications.
@MOVAVG @MOVMAX @MOVMED @MOVMIN @SPLINE @TREND |
Statistical functions calculate advanced statistical values, such as correlation or variance. These functions are often used in sales and marketing applications.
@CORRELATION @COUNT @MEDIAN @MODE @RANK |
@STDEV @STDEVP @STDEVRANGE @VARIANCE @VARIANCEP |
The date function converts date strings to numbers that can be used in calculation formulas.
@TODATE |
Essbase includes two types of miscellaneous functions. Using @CALCMODE, you can specifies whether Essbase calculates a formula in cell mode or block mode and whether Essbase calculates a formula bottom-up or top-down. @CONCATENATE and @SUBSTRING enable manipulation of character strings.
@CALCMODE @CONCATENATE @SUBSTRING @NAME |
This custom-defined group is a category of functions that you develop for calculation operations that are not enabled by the built-in Hyperion Essbase functions. Custom-defined functions are written in the Java programming language and registered on the server. The Essbase calculator framework calls custom-defined functions as external functions.
Copyright 1991-2002 Hyperion Solutions Corporation. All rights reserved.