Formula Syntax for Custom Calculation Rules

This topic describes the syntax to use when creating custom calculation rules.

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 Custom Rule page.

  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]

• Custom calculation rules support the following sytnax for substitution variables:

  • The substitution variable name must be written inside square brackets ([]) and start with an &(ampersand) in the formula.

    Example Formula: ([Rule1],[PCM_Adjustment In]) := ([PCM_Input],[PCM_Rule], [&Prior_Year]) *.10;

  • Do not use square brackets to enclose the substitution variable.

    Correct variable value: 2022

    Incorrect variable value: [2022]

Note:

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