The FIX…ENDFIX command block restricts database calculations to a subset of the database. All commands nested between the FIX and ENDFIX statements are restricted to the specified database subset.
This command is useful because it allows you to calculate separate portions of the database using different formulas, if necessary. It also allows you to calculate the sub-section much faster than you would otherwise.
The ENDFIX command ends a FIX command block. As shown in the example, you call ENDFIX after all of the commands in the FIX command block have been called, and before the next element of the calculation script.
FIX (fixMbrs) COMMANDS ; ENDFIX
A member name or list of members from any number of database dimensions. fixMbrs can also contain:
The commands you want to be executed for the duration of the FIX.
You can use SET EMPTYMEMBERSETS to stop the calculation within a FIX command if the FIX evaluates to an empty member set.
FIX commands can be nested within other FIX command blocks. For an example of an incorrect use of nested FIX commands, see “Using the FIX Command” in the Oracle Essbase Database Administrator's Guide.
FIX statements can only be used in calculation scripts, not in outline member formulas. Use an IF command instead of a FIX statement in member formulas. For example:
Jan( IF (Sales) Actual=5; ENDIF;)
AND/OR operators have the same precedence; Essbase evaluates them from left to right. Use parentheses to group the expressions. For example: A OR B AND C is the same as ((A OR B) AND C). However, if you use (A OR (B AND C)), Essbase evaluates the sub-expression in parentheses (B AND C) before the whole expression, producing a different result.
Inside FIX statements, the AND operator represents the intersection of two sets; the OR operator represents the union of two sets. In formulas, these operators are Boolean operators. Using the AND or OR operators on members that are from different dimensions, returns:
AND: An empty set. The FIX statement is ignored and the calculation continues with a warning message.
OR: The union of two members sets. FIX (Jan OR Market) is identical to FIX (Jan, Market).
In FIX statements, members from the same dimension are always acted on as OR unless you specify otherwise.
NOT operators are not supported in FIX statements. Use the @REMOVE function with FIX statements.
You do not need to follow ENDFIX with a semicolon.
You can specify attributes in FIX statements using the @ATTRIBUTE and @WITHATTR functions; for example FIX(@ATTRIBUTE(Can)). You must use these functions; FIX(Can) is not supported.
You cannot use a FIX statement on a dimension if it is a subset of a dimension that you calculate within the FIX statement. For example you could not use Market "New Mkt" in a FIX statement if you calculate all of Market within the FIX statement.
Dynamic Calc members are ignored in a FIX statement. If the only member in a FIX statement is a Dynamic Calc member, an error message is displayed stating that the FIX statement cannot contain a Dynamic Calc member.
If the FIX command is issued from a calculation script and produces an empty set, that part of the calculation is ignored, and the calculation continues to the next statement. The application log entry for the calculation shows that the FIX statement evaluated to an empty set (Calculating […] with fixed members ).
For example, using Sample Basic, assume this statement is in a calculation script:
FIX (@children(Jan)) CALC DIM (Accounts, Product, Market) ENDFIX
Since @children(Jan) is empty, the FIX is ignored; the calculation issues a warning and operates on the entire database.
Similarly, if a region defining a partition or a security filter evaluates to an empty set, Essbase issues a warning and behaves as if the region definition or security filter did not exist.
The calculator function @RANGE and the cross-dimensional operator (->) cannot be used inside a FIX fixMbrs parameter.
Using an EXCLUDE…ENDEXCLUDE command to specifying members that should not be calculated may be simpler than specifying a complex combination of member names in a FIX…ENDFIX command.
FIX (Budget) CALC DIM (Year, Measures, Product, Market); ENDFIX FIX (Budget, Jan, Feb, Mar, @DESCENDANTS(Profit)) CALC DIM (Product, Market); ENDFIX
The following example fixes on the children of East and the Market dimension members with the UDA "New Mkt".
FIX (@CHILDREN(East) OR @UDA(Market, "New Mkt"))
The following example fixes on the children of East with the UDA "New Mkt" and Market dimension members with the UDA "Big Mkt".
FIX((@CHILDREN(East) AND @UDA(Market, "New Mkt")) OR @UDA(Market,"Big Mkt"))