About Custom Calculation Rule Formula Syntax

This section describes the syntax, or format, to use when creating custom rules in Profitability and Cost Management.

The syntax is summarized as follows:

• The basic formula format is Result equals Formula, where Result and Formula are separated by :=, so Result := Formula.

• The left side of the equation is the Result, a tuple that determines where custom calculated results are written.

  Note:

  A tuple is a comma-separated list of members enclosed in parentheses.

  The tuple is an MDX specification of one or more members where no two members are in the same dimension. Only member names are allowed in the tuple expression. Member functions are not supported for custom calculations. Dynamic and attribute members are not allowed in the Result. The Result must include a single member from the Result dimension. It must be Level 0 and may be part of a Level 0 tuple, such as ([STA1120],[Feb]). The Result dimension is defined in the Target tab of the Rule Definition screen.

  Note:

  MDX is a joint specification of the XML for Analysis founding members. For more information about allowable MDX syntax, see Chapter 7, "MDX", of the Oracle Essbase Technical Reference.

• The right side of the equation is the Formula, a simple MDX numeric value expression. It includes the other members, tuples, or constants at any level and the mathematical expression to perform. It must include at least one member from the Result dimension and may be Level 0, upper level, or a tuple.

  Note:

  Members on either side of the equation must be enclosed in square brackets, [ ].

  Tuples must be enclosed in parentheses, ( ).

  The Formula expression must end in a semicolon, ;.

  The Formula can use the following:

  • Constants: [STAT1120]:=1;

    Note:

    A constant can be a numeric constant such as 7. It can be an expression that references values from other members in the result dimension, and it can reference values expressed as member tuples.

  • A mathematical operation: [STAT1114]:=([STAT1305],[Rule])*([STAT1307],[Rule]);

    Note:

    Mathematical operations must always include a Rule dimension member.

• Custom calculation rules support the following syntax formats for member names:

  • No qualification: Only the member name is provided.

    Example: [MemberName]

  • Full qualification: The fully qualified name (FQN) for the member.

    Example: [Dimension].[Gen2].[Gen3].[Gen4]...[MemberName]

Note:

For information about optimizing performance when defining custom calculation rules, see Skipping Empty Tuples to Optimize Custom Calculations.