Using Functions in Formulas
Functions are predefined routines that perform specialized calculations and return sets of members or data values. You can use the following types of functions in formulas:
Table 18-2 List of Function Types
| Function Type | Description |
|---|---|
|
Boolean |
Provide a conditional test by returning a TRUE (1) or FALSE (0) value. For example, you can use the @ISMBR function to determine whether the current member matches any members specified. See Conditional Tests. |
|
Mathematical |
Perform specialized mathematical calculations. For example, you can use the @AVG function to return the average value of a list of members. |
|
Relationship |
Look up data values within a database during a calculation. For example, you can use the @ANCESTVAL function to return the ancestor values of a specified member combination. |
|
Range |
Declare a range of members as an argument to another function or command. For example, you can use the @SUMRANGE function to return the sum of all members within a specified range. See Range Functions. |
|
Financial |
Perform specialized financial calculations. For example, you can use the @INTEREST function to calculate simple interest or the @PTD function to calculate period-to-date values. See Financial Functions. |
|
Specifying member lists and ranges |
Specify multiple members or a range of members. 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. |
|
Generating member lists |
Generate a list of members that is based on a specified member. For example, you can use the @ICHILDREN function to return a specified member and its children. |
|
Character string manipulation |
Manipulate character strings for member and dimension names. For example, you can generate member names by adding a character prefix to a name or removing a suffix from a name, or by passing the name as a string. |
|
Member combinations across dimensions |
Point to data values of specific member combinations by using the cross-dimensional operator (->). |
|
Interdependent values |
For formulas that require values from members of the same dimension, but for which the required values have not yet been calculated. |
|
Variances and variance percentages |
Calculate a variance or percentage variance between budget and actual values. See Calculating Variances or Percentage Variances Between Actual and Budget Values. |
|
Allocation |
Allocate values that are input at a parent level across child members. You can allocate values within the same dimension or across multiple dimensions. For example, you can use the @ALLOCATE function to allocate sales values that are input at a parent level to the children of the parent; the allocation of each child is determined by its share of the sales of the previous year. See Allocating Values. |
|
Forecasting |
Manipulate data for the purposes of smoothing or interpolating data, or calculating future values. For example, you can use the @TREND function to calculate future values that are based on curve-fitting to historical values. |
|
Statistical |
Calculate advanced statistics. For example, you can use the @RANK function to calculate the rank of a specified member or a specified value in a data set. |
|
Date and time |
Use date and time characteristics in calculation formulas. For example, you can use the @TODATE function to convert date strings to numbers that can be used in calculation formulas. |
|
Calculation mode |
Specify calculation modes that Essbase is to use to calculate a formula—cell, block, bottom-up, and top-down. |
Note:
Abbreviations of functions are not supported. Some commands may work in an abbreviated form, but if another function has a similar name, Essbase may use the wrong function. Use the complete function name to ensure correct results.
Conditional Tests
You can define formulas that use a conditional test or a series of conditional tests to control the flow of calculation.
The IF and ENDIF commands define a conditional block. The formulas between the IF and the ENDIF commands are executed only if the test returns TRUE (1). If the test returns FALSE (0), you can use the ELSE and ELSEIF commands to specify alternative actions. The formulas following each ELSE command are executed only if the previous test returns FALSE (0). Conditions following each ELSEIF command are tested only if the previous IF command returns FALSE (0).
When you use a conditional formula in a calculation script, enclose it in parentheses and associate it with a member in the database outline, as shown in the examples in this section.
In conjunction with an IF command, you can use functions that return TRUE or FALSE (1 or 0, respectively) based on the result of a conditional test. These functions are known as Boolean functions.
Use Boolean functions to determine which formula to use. The decision is based on the characteristics of the current member combination. For example, to restrict a certain calculation to the members in the Product dimension that contain input data, preface the calculation with an IF test based on @ISLEV(Product,0).
If one of the function parameters is a cross-dimensional member, such as @ISMBR(Sales -> Budget), all of the parts of the cross-dimensional member must match the properties of the current cell to return a value of TRUE (1).
The following Boolean functions specify conditions:
Table 18-3 List of Boolean Functions That Test Conditions
| Function | Condition |
|---|---|
|
@ISACCTYPE |
Current member has a specified accounts tag (for example, an Expense tag) |
|
@ISANCEST |
Current member is an ancestor of the specified member |
|
@ISIANCEST |
Current member is an ancestor of the specified member, or the specified member itself |
|
@ISCHILD |
Current member is a child of the specified member |
|
@ISICHILD |
Current member is a child of the specified member, or the specified member itself |
|
@ISDESC |
Current member is a descendant of the specified member |
|
@ISIDESC |
Current member is a descendant of the specified member, or the specified member itself |
|
@ISGEN |
Current member of the specified dimension is in the generation specified |
|
@ISLEV |
Current member of the specified dimension is in the level specified |
|
@ISMBR |
Current member matches any of the specified members |
|
@ISPARENT |
Current member is the parent of the specified member |
|
@ISIPARENT |
Current member is the parent of the specified member, or the specified member itself |
|
@ISSAMEGEN |
Current member (of the same dimension as the specified member) is in the same generation as the specified member |
|
@ISSAMELEV |
Current member (of the same dimension as the specified member) is in the same level as the specified member |
|
@ISSIBLING |
Current member is a sibling of the specified member |
|
@ISISIBLING |
Current member is a sibling of the specified member, or the specified member itself |
|
@ISUDA |
A specified UDA exists for the current member of the specified dimension |
When you place formulas on the database outline, you can use only the IF, ELSE, ELSEIF, and ENDIF commands and Boolean functions to control the flow of the calculations. You can use additional control commands in a calculation script.
For information about how to develop calculation scripts and how to use them to control how Essbase calculates a database, see Developing Calculation Scripts for Block Storage Databases.
Examples of Conditional Tests
You can apply the following formula to a Commission member in the database outline.
In the following example, the formula calculates commission at 1% of sales if the sales are greater than 500000:
IF(Sales > 500000)
Commission = Sales * .01;
ENDIF;If you place the formula in a calculation script, you must associate the formula with the Commission member as shown:
Commission (IF(Sales > 500000)
Commission = Sales * .01;
ENDIF;)Essbase cycles through the database, performing these calculations:
-
The IF statement checks to see if the value of Sales for the current member combination is greater than 500000.
-
If Sales is greater than 500000, Essbase multiplies the value in Sales by 0.01 and places the result in Commission.
In the next example, the formula tests the ancestry of the current member and then applies the appropriate Payroll calculation formula:
IF(@ISIDESC(East) OR @ISIDESC(West))
Payroll = Sales * .15;
ELSEIF(@ISIDESC(Central))
Payroll = Sales * .11;
ELSE
Payroll = Sales * .10;
ENDIF;If you place the formula in a calculation script, you must associate the formula with the Payroll member as shown:
Payroll(IF(@ISIDESC(East) OR @ISIDESC(West))
Payroll = Sales * .15;
ELSEIF(@ISIDESC(Central))
Payroll = Sales * .11;
ELSE
Payroll = Sales * .10;
ENDIF;)Essbase cycles through the database, performing the following calculations:
-
The IF statement uses the @ISIDESC function to check whether the current member on the Market dimension is a descendant of either East or West.
-
If the current member on the Market dimension is a descendant of East or West, Essbase multiplies the value in Sales by 0.15 and moves on to the next member combination.
-
If the current member is not a descendant of East or West, the ELSEIF statement uses the @ISIDESC function to check whether the current member is a descendant of Central.
-
If the current member on the Market dimension is a descendant of Central, Essbase multiplies the value in Sales by 0.11 and moves to the next member combination.
-
If the current member is not a descendant of East, West, or Central, Essbase multiplies the value in Sales by 0.10 and moves to the next member combination.
Mathematical Operations
The following mathematical functions allow you to perform many mathematical operations in formulas:
Table 18-4 List of Mathematical Functions
| Function | Operation |
|---|---|
|
@ABS |
Return the absolute value of an expression |
|
@AVG |
Return the average value of the values in the specified member list |
|
@EXP |
Return the value of e (the base of natural logarithms) raised to power of the specified expression |
|
@FACTORIAL |
Return the factorial of an expression |
|
@INT |
Return the next-lowest integer value of a member or expression |
|
@LN |
Return the natural logarithm of a specified expression |
|
@LOG |
Return the logarithm to a specified base of a specified expression |
|
@LOG10 |
Return the base-10 logarithm of a specified expression |
|
@MAX |
Return the maximum value among the expressions in the specified member list |
|
@MAXS |
Return the maximum value among the expressions in the specified member list, with the ability to skip zero and #MISSING values |
|
@MIN |
Return the minimum value among the expressions in the specified member list |
|
@MINS |
Return the minimum value among the expressions in the specified member list, with the ability to skip zero and #MISSING values |
|
@MOD |
Return the modulus produced by the division of two specified members |
|
@POWER |
Return the value of the specified member raised to the specified power |
|
@REMAINDER |
Return the remainder value of an expression |
|
@ROUND |
Return the member or expression rounded to the specified number of decimal places |
|
@SUM |
Return the summation of values of all specified members |
|
@TRUNCATE |
Return the truncated value of an expression |
|
@VAR |
Return the variance (difference) between two specified members. See Calculating Variances or Percentage Variances Between Actual and Budget Values. |
|
@VARPER |
Return the percentage variance (difference) between two specified members. See Calculating Variances or Percentage Variances Between Actual and Budget Values. |
Member Relationship Functions
The following relationship functions allow you to use the member combination that Essbase is currently calculating to look up specific values:
Table 18-5 List of Member Relationship Functions
| Function | Look-up Value |
|---|---|
|
@ANCESTVAL |
Ancestor values of the specified member combination |
|
@ATTRIBUTEVAL |
Numeric value of the attribute from the specified numeric or date attribute dimension associated with the current member |
|
@ATTRIBUTESVAL |
Text value of the attribute from the specified text attribute dimension associated with the current member |
|
@ATTRIBUTEBVAL |
Value (TRUE or FALSE) of the attribute from the specified Boolean attribute dimension associated with the current member |
|
@CURGEN |
Generation number of the current member combination for the specified dimension |
|
@CURLEV |
Level number of the current member combination for the specified dimension |
|
@GEN |
Generation number of the specified member |
|
@LEV |
Level number of the specified member |
|
@MDANCESTVAL |
Ancestor values of the specified member combination across multiple dimensions |
|
@SANCESTVAL |
Shared ancestor values of the specified member combination |
|
@PARENTVAL |
Parent values of the specified member combination |
|
@MDPARENTVAL |
Parent values of the specified member combination across multiple dimensions |
|
@SPARENTVAL |
Shared parent values of the specified member combination |
|
@XREF |
Data value from another database to be used for calculation of a value from the current database |
|
@XWRITE |
Used to write values to another Essbase database, or to the same database |
Range Functions
The following range functions allow you to execute a function for a range of members:
Table 18-6 List of Range Functions
| Function | Calculation |
|---|---|
|
@AVGRANGE |
The average value of a member across a range of members |
|
@CURRMBRRANGE |
A range of members that is based on the relative position of the member combination Essbase is currently calculating |
|
@MAXRANGE |
The maximum value of a member across a range of members |
|
@MAXSRANGE |
The maximum value of a member across a range of members, with the ability to skip zero and #MISSING values |
|
@MDSHIFT |
The next or nth member in a range of members, retaining all other members identical to the current member across multiple dimensions |
|
@MINRANGE |
The minimum value of a member across a range of members |
|
@MINSRANGE |
The minimum value of a member across a range of members, with the ability to skip zero and #MISSING values |
|
@NEXT |
The next or nth member in a range of members |
|
@NEXT |
The next or nth member in a range of members, with the option to skip #MISSING, zero, or both values |
|
@PRIOR |
The previous or nth previous member in a range of members |
|
@PRIORS |
The previous or nth previous member in a range of members, with the option to skip #MISSING, zero, or both values |
|
@SHIFT In some cases, @SHIFTPLUS or @SHIFTMINUS |
The next or nth member in a range of members, retaining all other members identical to the current member and in the specified dimension |
|
@SUMRANGE |
The summation of values of all specified members across a range of members |
Financial Functions
The following financial functions allow you to include financial calculations in formulas:
Table 18-7 List of Financial Functions
| Function | Calculation |
|---|---|
|
@ACCUM |
An accumulation of values up to the specified member |
|
@COMPOUND |
The proceeds of a compound interest calculation |
|
@COMPOUNDGROWTH |
A series of values that represent the compound growth of the specified member across a range of members |
|
@DECLINE |
Depreciation for a specific period, calculated using the declining balance method |
|
@DISCOUNT |
A value discounted by the specified rate, from the first period of the range to the period in which the amount to discount is found |
|
@GROWTH |
A series of values that represents the linear growth of the specified value |
|
@INTEREST |
The simple interest for a specified member at a specified rate |
|
@IRR |
The Internal Rate of Return on a cash flow that is calculated across the time dimension or a specified range of members and must contain at least one investment (negative) and one income (positive). Includes an initial guess of 0.07 (the initial guess cannot be configured). |
|
@IRREX |
The Internal Rate of Return on a cash flow that is calculated across the time dimension or a specified range of members and must contain at least one investment (negative) and one income (positive). Includes functionality to configure the initial guess and the number of iterations the algorithm can make. |
|
@NPV |
The Net Present Value of an investment (based on a series of payments and incomes) |
|
@PTD |
The period-to-date values of members in the dimension tagged as time |
|
@SLN |
The amount per period that an asset in the current period may be depreciated (calculated across a range of periods). The depreciation method used is straight-line depreciation. |
|
@SYD |
The amount per period that an asset in the current period may be depreciated (calculated across a range of periods). The depreciation method used is sum of the year's digits. |
Note:
One member formula cannot contain multiple financial functions (for example, @NPV and @SLN, or multiple instances of @NPV). A member formula that requires multiple financial functions must be broken into separate formulas so that each formula contains only one financial function (for example, MemberName(@NPV(...));Membername(@NPV(...))).
Member-Related Functions
This section discusses creating formulas that refer to members.
Specifying Member Lists and Ranges
In some functions, you may need to specify multiple members, or you may need to specify a range of members. 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 18-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 Generating 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.
Generating Member Lists
Member set functions allow you to generate member lists that are based on a specified member or member list.
Table 18-9 List of Member Set Functions
| Function | Contents of Member List |
|---|---|
|
@ALLANCESTORS |
All ancestors of the specified member, including ancestors of the specified member as a shared member. This function does not include the specified member. |
|
@IALLANCESTORS |
All ancestors of the specified member, including ancestors of the specified member as a shared member. This function includes the specified member. |
|
@ANCEST |
The ancestor of the specified member at the specified generation or level |
|
@ANCESTORS |
All ancestors of the specified member (optionally, up to the specified generation or level), but not the specified member |
|
@IANCESTORS |
All ancestors of the specified member (optionally, up to the specified generation or level), including the specified member |
|
@LANCESTORS |
All ancestors of the specified list of members (optionally, up to the specified generation or level), but not including the specified members |
|
@ILANCESTORS |
All ancestors of the specified list of members (optionally, up to the specified generation or level), including the specified members |
|
@ATTRIBUTE |
All base-dimension members that are associated with the specified attribute-dimension member |
|
@WITHATTR |
All base members that are associated with attributes that satisfy the specified conditions |
|
@BETWEEN |
All members whose name string value fall between, and are inclusive of, two specified string tokens |
|
@CHILDREN |
All children of the specified member, but not including the specified member |
|
@ICHILDREN |
All children of the specified member, including the specified member |
|
@CURRMBR |
The current member being calculated for the specified dimension |
|
@DESCENDANTS |
All descendants of the specified member (optionally, up to the specified generation or level), but not the specified member nor descendants of shared members |
|
@IDESCENDANTS |
All descendants of the specified member (optionally, up to the specified generation or level), including the specified member, but not descendants of shared members |
|
@LDESCENDANTS |
All descendants of the specified list of members (optionally, down to the specified generation or level), but not including the specified members |
|
@ILDESCENDANTS |
All descendants of the specified list of members (optionally, down to the specified generation or level), including the specified members |
|
@RDESCENDANTS |
All descendants of the specified member (optionally, up to the specified generation or level), including descendants of shared members, but not the specified member |
|
@IRDESCENDANTS |
All descendants of the specified member (optionally, up to the specified generation or level), including the specified member and descendants of shared members |
|
@EQUAL |
Member names that match the specified token name |
|
@NOTEQUAL |
Member names that do not match the specified token name |
|
@EXPAND |
Expands a member search by calling a member set function for each member in a member list |
|
@GENMBRS |
All members of the specified generation in the specified dimension |
|
@LEVMBRS |
All members of the specified level in the specified dimension |
|
@LIKE |
Member names that match the specified pattern. |
|
@LIST |
Separate lists of members to be processed by functions that require multiple list arguments |
|
@MATCH |
All members that match the specified wildcard selection |
|
@MBRCOMPARE |
Member names that match the comparison criteria |
|
@MBRPARENT |
Parent of the specified member |
|
@MEMBER |
The member with the name that is provided as a character string |
|
@MERGE |
A merged list of two member lists to be processed by another function |
|
@PARENT |
The parent of the current member being calculated in the specified dimension |
|
@RANGE |
A member list that crosses the specified member from one dimension with the specified member range from another dimension |
|
@REMOVE |
A list of members from which some members have been removed |
|
@RELATIVE |
All members of the specified generation or level that are above or below the specified member |
|
@SHARE |
A member list that identifies all shared members among the specified members |
|
@SIBLINGS |
All siblings of the specified member, but not the specified member |
|
@ISIBLINGS |
All siblings of the specified member, including the specified member |
|
@LSIBLINGS |
All siblings that precede the specified member in the database outline, but not the specified member |
|
@RSIBLINGS |
All siblings that follow the specified member in the database outline, but not the specified member |
|
@ILSIBLINGS |
All siblings that precede the specified member in the database outline, including the specified member |
|
@IRSIBLINGS |
All siblings that follow the specified member in the database outline, including the specified member |
|
@SHIFTSIBLING |
The sibling at the specified distance from the member |
|
@NEXTSIBLING |
The next, or right-most, sibling of the member |
|
@PREVSIBLING |
The previous, or left-most, sibling of the member |
|
@UDA |
All members that have a common UDA defined on Essbase Server |
|
@XRANGE |
A member list that identifies the range of members between (and inclusive of) two specified single or cross-dimensional members at the same level |
Manipulating Member Names
You can work with member names as character strings. The following table lists character string manipulation functions:
Table 18-10 List of Character String Manipulation Functions
| Function | Character String Manipulation |
|---|---|
|
@CONCATENATE |
Create a character string that is the result of appending a member name or specified character string to another member name or character string |
|
@NAME |
Return a member name as a string |
|
@SUBSTRING |
Return a substring of characters from another character string or from a member name |
Working with Member Combinations Across Dimensions
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.
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 18-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.
Using the cross-dimensional operator can have significant performance implications. For optimization guidelines, see Using Cross-Dimensional Operators.
Forecasting Functions
Forecasting functions allow you to manipulate data for the purposes of interpolating data or calculating future values.
Table 18-11 List of Forecasting Functions
| Function | Data Manipulation |
|---|---|
|
@MOVAVG |
Apply a moving average to a data set and replace each term in the list with a trailing average. This function modifies the data set for smoothing purposes. |
|
@MOVMAX |
Apply a moving maximum to a data set and replace each term in the list with a trailing maximum. This function modifies the data set for smoothing purposes. |
|
@MOVMED |
Apply a moving median to a data set and replace each term in the list with a trailing median. This function modifies the data set for smoothing purposes. |
|
@MOVMIN |
Apply a moving minimum to a data set and replace each term in the list with a trailing minimum. This function modifies the data set for smoothing purposes. |
|
@MOVSUM |
Apply a moving sum to a data set and replace each term with a trailing sum. This function modifies the data set for smoothing purposes. |
|
@MOVSUMX |
Apply a moving sum to a data set and replace each term with a trailing sum. Specify how to assign values to members before you reach the number to sum. This function modifies the data set for smoothing purposes. |
|
@SPLINE |
Apply a smoothing spline to a set of data points. A spline is a mathematical curve that is used to smooth or interpolate data. |
|
@TREND |
Calculate future values and base the calculation on curve-fitting to historical values. |
Statistical Functions
Statistical functions allow you to calculate advanced statistics in Essbase.
Table 18-12 List of Statistical Functions
| Function | Calculated Value |
|---|---|
|
@CORRELATION |
The correlation coefficient between two parallel data sets |
|
@COUNT |
The number of values in the specified data set |
|
@MEDIAN |
The median, or middle number, in the specified data set |
|
@MODE |
The mode, or the most frequently occurring value, in the specified data set |
|
@RANK |
The rank of the specified member or value in the specified data set |
|
@STDEV |
The standard deviation, based upon a sample, of the specified members |
|
@STDEVP |
The standard deviation, based upon the entire population, of the specified members |
|
@STDEVRANGE |
The standard deviation, crossed with a range of members, of the specified members |
|
@VARIANCE |
The variance, based upon a sample, of the specified data set |
|
@VARIANCEP |
The variance, based upon the entire population, of the specified data set |
Date and Time Function
The date function allows you to use dates with other functions.
@TODATE: Convert date strings to numbers that can be used in calculation formulas
Calculation Mode Function
The calculation mode function allows you to specify which calculation mode that Essbase uses to calculate a formula.
@CALCMODE: Specify the calculation mode (cell, block, bottom-up, or top-down) that Essbase uses to calculate a formula
Note:
You can also use the configuration setting CALCMODE to set calculation modes to BLOCK or BOTTOMUP at the database, application, or server level.