ESSBASE: When to Use FIX vs. IF

Hyperion developers love FIX and IF, both are powerful tools and interchangeable in usage in certain circumncetances. There are also differences between the two. Let's take a look.

FIX...ENFIX

Usages and Rules

• Every FIX must be ended with ENDFIX. But it is optional to end the statement by ";"


• FIX Mbr can be any comma-delimited list of members, or member set functions


• FIX Mbr argument can be a combination of members from multiple dimensions

          FIX(@CHILDREN(Qtr1),"Actual")
                Calc Dim(Accounts);
          ENDFIX

• FIX Mbr list from the same dimension means "OR", cross dimensions means "AND"


• FIX cannot be used inside of formulas in outline, they can ONLY be used in calc scripts


• Cannot consolidate a dimension that is FIXed on. Following will generate an error when run

         FIX(@CHILDREN(Qtr1),"Actual")
                Calc Dim(Accounts, Scenario);
        ENDFIX

• It can have nested FIX


• IF statements can be nested inside of FIX statements. On the other hand, you cannot FIX inside of IF statements.


• It is more efficient to use FIX on Sparse, and use IF on Dense

IF...ELSEIF..ELSE..ENDIF

• IF statements can be used inside of formulas on members or in calc scripts.


• FIX only focus on member sets, IF can restrict on values within the database.


• Syntax of an IF statement differs when used within a calc script vs. a formula in the outline.

/* in calc script, put a member from the DIM that is being calc in front of the parenthesis*/         
              D
          (
           IF (condition1)
               A=
               B=
           ENDIF
           IF (condition2)
               C=
               D=
           ENDIF
           )

• The following script will make mutiple passes through DB

         B
         (
         IF (condition1)
           A=
           B=
         ENDIF
         )
        D
        (
        IF (condition2)
            C=
            D=
        ENDIF
         )

FIX can only narrow down the calculation scopes, while IF can do much more, just as IF in any other program languages.

For example (all member formulas unless specified)
1) IF can check values of accounts  and SmartList

IF (("Annual Salary" != #MISSING) AND ("Status" == 1))
"Annual Salary" * "Spread_WD_Calc";
ELSE #MISSING;
ENDIF
or have multiple ELSEIF

IF (("Annual Salary" == #MISSING) OR ("Status" == 2) OR ("Status" == 9) OR ("Status" == 7) OR ("Status" == #MISSING))
#MISSING;
ELSEIF ("Status" == 4)
0;
ELSEIF ("Status" == 5)
0.65 * "Annual Salary" * "Spread_WD_Calc";
ELSE
"Annual Salary" * "Spread_WD_Calc";
ENDIF

(in calc scripts)
Profit (IF (Sales > 100)
Profit = (Sales - COGS) * 2;
ELSE
Profit = (Sales - COGS) * 1.5;
ENDIF;);

2) IF can check a member name or a list of members (FIX can do those too)
IF (@ISMBR("BegBalance"))
#MISSING;
ELSE
XXXX;
ENDIF

IF (@ISDESC("New Employees"))
#MISSING;
ELSE
XXXX;
ENDIF


3) Check UDA
IF (NOT @ISUDA("Scenario", "Actual"))
#MISSING;
ELSE
XXXX;
ENDIF


When using equation, if you have a sparse member at the left side of formula, you may not be able to see the results, depending on whether the data block for that sparse member has already been created or not. If not, even though calculation produces the result correctly, there will be no place to store the value.

Assuming account dimensions is dense, flight length dimension is a sparse dimension, in stead of using 

FIX("CA_31100")
"FlightLength" =
ENDFIX;

Using this instead 

FIX("FlightLength")
 "CA_31100" =
ENDFIX;