IF

The IF calculation command for Essbase 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;

Parameters

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.
You can specify attributes in IF statements using @ATTRIBUTE and @WITHATTR; for example: IF (@ISMBR(@ATTRIBUTE(Can))) .... You must use the attribute functions; IF(@ISMBR(Can)) is not supported.

Example

Example 1

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.

Example 2

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(...)...).