Templates for aggregating Account values

PerformancePoint Planning Business Modeler automatically includes templates for business rules that aggregate account values to the current date over a standard time, such as month-to-date, quarter-to-date, or year-to-date. By substituting appropriate dimension member values for placeholders in a template, you can create business rules that perform these calculations.

What do you want to do?

Find out what templates are available

Find out what substitutions are necessary in each template

Determine the aggregation method

Create a rule by using an account aggregation template

Find out what templates are available

The rules that you can create by using one of these templates calculate an aggregate value of account activity, from the start of the designated period to the time that is configured by the Current Period property in the model. For example, a YTD Calculation rule calculates an aggregate from the start of the calendar or fiscal year to the date specified in the Current Period property.

Template

Description

MTD Calculation

Aggregates from the start of the month in the calendar or fiscal year to the date specified in the Current Period property.

QTD Calculation

Aggregates from the start of the quarter-year point in the calendar or fiscal year to the date specified in the Current Period property.

TrimesterToDate Calculation

Aggregates from the start of the trimester to the date specified in the Current Period property.

HalfYearToDate Calculation

Aggregates from the start of the half-year point in the calendar or fiscal year to the date specified in the Current Period property.

YTD Calculation

Aggregates from the start of the calendar or fiscal year to the date specified in the Current Period property.

Top of Page

Find out what substitutions are necessary in each template

To create a working rule from on of these templates, you must substitute actual dimension member values for the placeholders in the templates.

Each template contains a placeholder for a special member of the TimeDataView dimension to hold the calculated result. However, the specific placeholder name that appears in the template depends on the type of template.

For all other substitutions, the placeholder names are the same in each kind of template.

The following table describes the placeholders that are used in these templates:

Placeholder

Description

TimeDataView Member

The following table shows the kind of template and the placeholder name for the special TimeDataView member that appears in that template:

Template

Placeholder name

MTD Calculation

MonthToDate

QTD Calculation

QuarterToDate

TrimesterToDate Calculation

TrimesterToDate

HalfYearToDate Calculation

HalfToDate

YTD Calculation

YearToDate

Base Scenarios

List of base scenarios for this model

AccountHier

The account, or Account dimension hierarchy, for which you want to calculate aggregate values. You must substitute the same value for AccountHier at multiple locations in the template that you use.

Note: A property of this account member determines the aggregation method for the calculation. For more information, see the next section, Determine the aggregation method.

TimeHier

The Time hierarchy that is used by the model. You must substitute the same value for TimeHier at multiple locations in the template.

Top of Page

Determine the aggregation method

PerformancePoint Planning Server calculates aggregate values by one of three methods, depending on the type of account. The three methods are:

  • Sum     The aggregate value is the sum of the values over the specified time. Typically, this method applies to Income Statement (Profit and Loss) accounts, which represent financial status over time.

  • End    The aggregate value is the value of the last child member in the specified account over the specified time. Typically, this method applies to Balance Sheet accounts, which represent a summary financial picture.

  • Average Balance    The aggregate value is the numeric average of the account values over the specified time. In Planning Server , this method applies only to Rate accounts, which show the rates used in calculations.

Planning Server uses the TimeBalance property of an account to identify its aggregation method. For example, if the TimeBalance property of an account is Sum, the rule calculates a sum of account values across time. If the TimeBalance property of an account is End, the rule uses the last child value in the account.

Each account in the Account dimension has an associated value in TimeBalance property. Therefore, to determine the aggregation method, you must choose an Account dimension member with the TimeBalance property that you want when you specify the AccountHier placeholder value.

To find the aggregation method for predefined members of the Account dimension, see one or more of the following articles:

For general information about properties of the Account dimension, see About properties for the Account dimension in Planning Business Modeler.

Top of Page

Create a rule by using an account aggregation template

To create a rule by using one of the templates that are listed in this article, you must first create a rule set and rule, and then fill the template placeholders with appropriate values. Expand each of the four major tasks in the following procedure to see the detailed steps in each.

  1. Complete the prerequisite tasks

    You must complete the following tasks before you can use one of these templates to create a rule.

    1. Verify that the model that will contain the rule is a financial model. That is, the model is of type Financial Model with Shares Calculation, or Financial Model without Shares Calculation. You cannot create a rule by using one of these templates in a generic model.

    2. Verify that the Time dimension for the model includes a Year level.

    3. Run the special script that is included with Planning Business Modeler. The script creates a member in the TimeDataView dimension to hold computed values that the rule creates. For information about how to run this script, see Script to create dimension members for rule templates.

      Important: You must run the script to add a member to the TimeDataView dimension. You cannot add a member to this dimension by using the Planning Business Modeler user interface. To learn more, see About the TimeDataView dimension in Planning Business Modeler.

  2. Create a new rule set

    A rule that you create by using one of these templates is a Definition rule. Therefore, when you create a rule set to contain your new rule, you must create a Definition rule set.

    When you create a Definition rule set, Planning Business Modeler automatically configures it for MdxScript implementation. This means that 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 the rule and caches the script in the cube.

    To create a Definition rule set to contain the rule, take these steps:

    1. In the Workspace Browser, click Models. In the Models in this site table, click the name of the model in which you want to create a rule.

    2. In the Workspace Actions pane, click Check out to make the model available for revision.

    3. In the Model Workspace, click the Business Rules tab.

    4. In the table of business rules, right-click the row that contains the root Definition rule set. Then, on the shortcut menu, select Create a New Rule Set.

    5. In the Create a New Rule Set dialog box, type a name, label, and description for the rule set that will contain your new rule. For information about limitations and character restrictions, see About names and labels.

    6. From the Type list, select Definition, and then click OK to create the rule set.

  3. Create a new rule

    Take these steps:

    1. In the table of business rules, right-click the name of the rule set that you just created. Then, on the shortcut menu, select Create a New Rule.

    2. In the New Rule dialog box, type a name, label, and description for the rule that you want to create by using one of the cumulative value templates. For information about limitations and character restrictions, see About names and labels.

    3. Check the Use template or copy from an existing rule box.

    4. Select Template, and then click the ellipsis button [...] next to the Template list to open the Select Rule Template dialog box. Your selection must be one of the following values: YTD Calculation template, HalfYearToDate Calculation, TrimesterToDate Calculation, QTD Calculation, or MTD Calculation.

    5. From the Rule Template table, select the name of the rule template that you want to use, and then click OK to return to the New Rule dialog box.

    6. Click OK to finish creating the new rule object and open the Rule expression pane that contains the text of the rule template.

  4. Fill placeholders in the rule template

    After you create a new rule by selecting one of these templates, the text of the rule appears in the Rule expression pane. Take these steps:

    1. Right-click in the Rule expression pane to show the shortcut menu, and select Fill Template Placeholders.

    2. In the Fill Template Placeholders dialog box, for each placeholder in the Placeholders grid, select or type a value. For more information about the required values, see the description of the selected template in Available templates.

    3. When you finish selecting values for all placeholders, click OK.

Important: After you finish creating the rule, verify that the Scope statement for this rule includes only members of the Scenario dimension for which data is loaded, such as Actual, Budget, and Forecast. Do not include calculated members of the Scenario dimension, such as Variance.

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!

×