The EXCLUDE command allows you to define a fixed range of members which are not affected by the associated commands. The ENDEXCLUDE command ends an EXCLUDE command block.
As shown in the example, you call ENDEXCLUDE after all of the commands in the EXCLUDE command block have been called, and before the next element of the calculation script.
Specifying members that should not be calculated in an EXCLUDE..ENDEXCLUDE command may be simpler than specifying a complex combination of member names in a FIX…ENDFIX command.
EXCLUDE (Mbrs) COMMANDS ; ENDEXCLUDE
A member name or list of members from any number of database dimensions. Mbrs can also contain:
The commands to be executed for the duration of the EXCLUDE.
Use EXCLUDE…ENDEXCLUDE commands only within calculation scripts, not in outline member formulas.
You can include EXCLUDE commands within FIX command blocks.
If a FIX command within an EXCLUDE command block specifies cells already specified by the EXCLUDE statement, those cells are not calculated, and a warning message is posted to the application log file.
An EXCLUDE command block cannot include CALC ALL, CLEARDATA, and DATACOPY commands.
AND and OR operators have the same precedence and are evaluated 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, subexpressions (for example, (A OR (B AND C)) are evaluated before the whole expression, producing a different result.
Inside EXCLUDE command blocks, 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 EXCLUDE statement is ignored and the calculation continues with a warning message.
OR: The union of two members sets. EXCLUDE (Jan OR Market) is identical to FIX (Jan, Market).
NOT operators are not supported in EXCLUDE command blocks. Use the @REMOVE function.
You do not need to follow ENDEXCLUDE with a semicolon.
Use the @ATTRIBUTE and @WITHATTR functions to specify attributes within EXCLUDE command blocks; for example EXCLUDE(@ATTRIBUTE(Can)). FIX(Can) is not supported.
You cannot use EXCLUDE on a dimension if it is a subset of a dimension that you calculate within the EXCLUDE command block. For example you could not use Market "New Mkt" in an EXCLUDE statement if you calculate all of Market within the command block.
Dynamic Calc members are ignored in an EXCLUDE statement. If the only member in an EXCLUDE statement is a Dynamic Calc member, an error message is displayed stating that the EXCLUDE statement cannot contain a Dynamic Calc member.
If the EXCLUDE 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 EXCLUDE statement evaluated to an empty set (Calculating […] with fixed members ).
For example, consider the following statement in a Sample Basic calculation script:
EXCLUDE (@children(Jan)) CALC DIM (Accounts, Product, Market) ENDEXCLUDE
Since @children(Jan) is empty (Jan is a level 0 member), the EXCLUDE parameter is ignored; the calculation operates on the entire database.
Similarly, if a region defining a partition or a security filter evaluates to an empty set, Essbase behaves as if the region definition or security filter does not exist.
Calculator function @RANGE and the cross-dimensional operator (->) cannot be used inside an EXCLUDE Mbrs parameter).
The following example excludes calculations on the children of Qtr4, enabling calculation of other quarters in the Year dimension.
EXCLUDE (@CHILDREN(Qtr4)) CALC DIM (Year) ENDEXCLUDE