You can add a function source to a formula using the Add Function toolbar button. The following types of functions are supported: Financial (Range) functions, Statistical functions, Math functions using Expression, Functions using ExpList, Functions using Variance, and Attribute Functions. For more information on these functions, see the Hyperion Essbase - System 9 Database Administrator’s Guide and the following topics:
To add a function to a formula:
If it is not open, open the graphical business rule to which you want to add a function.
If the BR Language tab is not selected, select it in the bottom of the left frame of the Analytic Administration Services window.
Expand the Formulas folder. Select the type of formula to add, and double-click the formula or drag it to the desired location in the business rule on the Graphical tab.
On the Formula tab, double-click the Function toolbar button on the Custom Toolbar.
Select the function you want to add.
The list of available functions depends on what version of Analytic Services you are using. For a brief overview of each function, see the following sections:
Click OK to add the function.
The function is inserted to the right of the last component in the formula.
The following functions cannot be used with another source type in the formula without receiving an error: @NPV, @ACCUM, @COMPOUND, @COMPOUNDGROWTH, @DISCOUNT, @INTEREST, @IRR, @SLN. For example, the function source should be the only block to the right of the equal sign. These financial functions never return a value. They calculate a series of values internally based on the range specified.
@ATTRIBUTEVAL(attDimName)—Returns the numeric value that is associated with a level 0 attribute member from the numeric, Boolean, or date attribute dimension, for the current member being calculated.
@ACCUM—Accumulates values across a time period. For example, calculate year-to-date values, or accumulated depreciation.
@COMPOUND—Calculates the proceeds of a compounded interest calculation based on the balances of a specified member at a specified rate across a specified range.
@COMPOUNDGROWTH—Models compound growth.
@DISCOUNT—Financial discounting as applied to calculating present value (PV).
@GROWTH—Models linear growth of a value.
@INTEREST—Calculates simple interest.
@IRR—Calculates the Internal Rate of Return of an investment.
@NPV—Calculates the Net Present Value.
@SLN—Calculates the amount per period that an asset in the current period may be depreciated, calculated across a range of periods.
@ABS(expression)—Returns the absolute value of the expression.
@AVG(SKIP*,expList)—Returns the average of all the values of expList. SKIPNONE includes all cells specified in the average operations regardless of content. SKIPMISSING excludes all values that are #MISSING in the average operation. SKIPZERO excludes values of zero from the average calculation. SKIPBOTH excludes all values of zero or #MISSING from the average calculation.
@INT(expression)—Returns the next lowest integer value of the expression.
@MIN(expList)—Returns the minimum value with the results of the expressions in expList.
@MAX(expList)—Returns the maximum value with the results of the expressions in the member list.
@ROUND(expression)—Rounds the expression to an integer value.
@SUM(expList)—Returns the summation of all the values in expList.
@TRUNCATE(expression)—Removes the fractional part of the expression, returning the integer.
@VAR(mbrname1, mbrname2)—Calculates the variance (difference) between two members.
@VARPER(mbrname1, mbrname2)—Calculates the percent variance (difference) between two members.
@COUNT—Counts a member selection in a formula. For example, the headcount of the admin department may be calculated as a multiple of the number of other non-admin departments.
@RANK—Runs a calculation for those meeting a certain rank. For example, those departments that are in the top ten most profitable departments.