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:
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.
See About Multidimensional Calculation Concepts. For information on the @ISIDESC function, see the Oracle Essbase Technical Reference.