Ratio between two accounts template in Planning Business Modeler

The rule that you can create by using the Ratio template calculates a ratio between accounts and expresses the ratio as a percentage. You can use a rule that is developed with this template to create calculations such as gross margin percent or the ratio between expenses and sales.

The rule that you create from this template contains logic that guarantees that no errors are thrown when division by zero occurs.

In this topic

Prerequisites

Implementation

Placeholder substitution requirements

Prerequisites

The following list shows the tasks that are prerequisites for a rule that you create with this template.

  • You must create a dimension member in the Account dimension that the rule can use to hold computed values. For example, you can name this member GrossMarginPercent.

  • The SCOPE statement that you use with a Ratio rule should include the PERIODIC member and all ToDate members of the TimeDataView dimension. This includes Year to Date, Quarter to Date, and so on. By default, the PERIODIC member is included in the scope of the TimeDataView dimension, but you must explicitly specify ToDate members in template placeholders.

  • For best performance, scope this rule to include only those members of the Scenario dimension for which PerformancePoint Planning Business Modeler loads data, such as Actual, Budget, and Forecast. The scope should not include calculated members of the Scenario dimension such as Variance.

Top of Page

Implementation

A rule that is created from this template is a Definition rule with MdxScript implementation. Every time that you query a cell set that falls in the scope that is defined by the rule or script, Planning Business Modeler runs this rule and caches the script in the cube. For more information, see MdxScript implementation.

Note:  This rule has a special requirement. Because MDXScript implementation is affected by dependency between rules, the placement of this rule in a rules script is important. To make sure that this rule generates correct results in all cases, put this rule after rules that include ToDate calculations.

Top of Page

Placeholder substitution requirements

To convert the Ratio template into a business rule, you must substitute actual dimension values for the placeholders in the template. For information about how to substitute values, see Filling a placeholder in a rule template.

The following table describes the placeholder substitutions that you must make to create a business rule from this template.

Placeholder

Comment

<<[TimeDataView].[All Members].[All To Date Members]>>

List of all ToDate members of the TimeDataView dimension

<<[Scenario].[Hierarchy].[Base Scenarios]>>

List of members of Scenario dimension that specify the base scenarios for the model

This value must represent one or more of the following scenarios: Actual, Budget, or Forecast

<<[Account].[Hierarchy].[Ratio Account]>>

User-defined member of the Account dimension that is the destination of the ratio calculation

<<[Account].[Hierarchy].[Denominator Account]>>

Account dimension member that is the denominator of the ratio

<<[Account].[Hierarchy].[Numerator Account]>>

Account dimension member that is the numerator of the ratio

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×