1. Administering and Working with Enterprise Profitability and Cost Management
  2. Building and Calculating Models
  3. Creating and Managing Rules
  4. Creating Custom Calculation Rules
  5. About Creating Custom Calculation Rules

About Creating Custom Calculation Rules

Custom calculation rules let you modify or create data in place. In a custom calculation rule, you define:

  • The target range for the calculation

  • The formula to execute for all cells in the target range

You can use custom calculation rules to:

  • Calculate driver data

  • Create what-if changes

  • Adjust costs and revenues

Design Considerations

When creating custom calculation rules, keep in mind the following:

  • Member selections in the rule target really means "traverse these intersections"

  • The target range defines the Level 0 intersections the rule will visit, executes the formula, and writes the result to the result member.

  • The rule formula supports basic math functions

  • CASE statements work, other MDX functions are not supported

Best Practices

Custom calculation rules are very sensitive to sparse ranges. To prevent performance challenges:

  • Minimize the target range as much as is practical.

  • Use NONEMPTYTUPLE on one of the formula operand tuples preceding formula. See Skipping Empty Tuples to Optimize Custom Calculations.

    For example:

    [PCM_Adjustment In]:= NONEMPTYTUPLE ([PCM_Input],[PCM_Rule]) ([PCM_Input],[PCM_Rule]) * .10;

Custom Calculation Rule Example

In this example, the Entity and Account dimensions are specified for the target range for the calculation, and the formula will be executed for all cells in that range.

Example of a Custom Rule

Note:

The custom calculation rule in this example is used in the BksML50 sample application provided with Enterprise Profitability and Cost Management. See Creating the Sample Application for information on how to access the sample application.