Performs conditional tests within a formula. Using the IF statement, you can define a Boolean test, as well as formulas to be calculated if the test returns either a TRUE or FALSE value.
Syntax
IF( condition ) statement ; [ ...statement ; ] [ ELSEIF...statement | ELSE...statement] ENDIF;
Parameter | Description |
---|---|
condition | Formula or function that returns a Boolean value of TRUE (a nonzero value) or FALSE (a zero value). |
statement | Operations to be performed depending on the results of the test. |
Notes
The IF statement block can also use the ELSE and ELSEIF statements as part of its decision syntax.
For information about using ENDIF statements and semicolons with IF, ELSE, and ELSEIF statements, see ENDIF.
In calculation scripts, IF statements must be placed within parentheses and associated with a specific database member. They must also be closed with ENDIF statements. For more information, see the Oracle Essbase Database Administrator's Guide.
You can specify attributes in IF statements using the @ATTRIBUTE and @WITHATTR functions; for example IF (@ISMBR(@ATTRIBUTE(Can))) .... You must use these functions; IF(@ISMBR(Can)) is not supported.
Example
IF( @ISMBR(@DESCENDANTS(Europe)) OR @ISMBR(@DESCENDANTS(Asia)) ) Taxes = "Gross Margin" * "Foreign Tax Rate"; ELSE Taxes = "Gross Margin" * "Domestic Tax Rate"; ENDIF;
This test checks to see if the current cell includes a member that is a descendant of either the Europe or Asia members. If it does, the formula calculates the taxes for the member based on the foreign tax rate. If the current cell does not include a member from one of those groups, then the domestic tax rate is used for the tax calculation.
When you use an IF statement as part of a member formula in a calculation script, you need to perform both of the following tasks:
Associate the IF statement with a single member
Enclose the IF statement in parentheses
A sample IF statement is illustrated in the following example:
Profit (IF (Sales > 100) Profit = (Sales - COGS) * 2; ELSE Profit = (Sales - COGS) * 1.5; ENDIF;)
Essbase cycles through the database and performs the following calculations:
The IF statement checks to see if the value of Sales for the current member combination is greater than 100.
If Sales is greater than 100, Essbase subtracts the value in COGS from the value in Sales, multiplies the difference by 2, and places the result in Profit.
If Sales is less than or equal to 100, Essbase subtracts the value in COGS from the value in Sales, multiplies the difference by 1.5, and places the result in Profit.
The whole of the IF ... ENDIF statement is enclosed in parentheses and associated with the Profit member, Profit (IF(...)...).
See Also