Using SCOPE filters in consolidation and currency rules

In Consolidation rules or Currency rules, you can use filter functions in a SCOPE statement (PEL) to generate a set of members that meet certain criteria. Then you can apply calculations, such as a currency conversion, to only the cells that match your filter criteria.

Filter functions operate on properties of Account dimension members or of Flow dimension members. For example, each account member in a Planning Business Modeler model has an Account Classification property. Account Classification identifies the kind of financial statement, such as Income Statement or Balance Sheet, which reports the account information. Each account dimension member also has an Account type. Flow dimension members have a property called Flow type. For information about Account classifications and Account types, see About the Account dimension. For information about Flow types, see About the Flow dimension.

The following table shows the filter functions that PerformancePoint Expression Language supports. You can use these functions as part of a SCOPE statement in Currency rules or Consolidation rules.

The following example shows a rule snippet that uses the ClassificationFilterInclude function to select members for two different currency conversions.

/* Generate a set of members with Income Statement account classification */
SCOPE ([Account].ClassificationFilterInclude("Income statement"));
/* Convert currency in each member using average exchange rate */
    () += AVE * CURRENTTUPLE;
END SCOPE;

TypeFilterInclude and TypeFilterExclude functions specify whether the filter applies to Account types or Flow types, and then specify the name of the member being filtered. However, you cannot use account type as a sub-scope of an account classification.

The following example selects a member set that includes all accounts of type BS-Net income.

SCOPE [Account].TypeFilterInclude("BS-Net income");

The following examples demonstrate several different uses of these functions:

• Single TypeFilterInclude

  The following code shows an example of a TypeFilterInclude function that uses two Flow types, Opening and PY Adj.

  SCOPE [Flow].TypeFilterInclude( "Opening", "PY Adj");
       ()   += OPE * CURRENTTUPLE;
  END SCOPE;
  

• Using TypeFilterInclude and TypeFilterExclude to create mutually exclusive member sets

  The following code shows how to use TypeFilterInclude and TypeFilterExclude to generate mutually exclusive member sets.

  The first SCOPE expression includes all members of the Flow dimension of types PY Adj, Opening, and Appropriation.

  The second SCOPE expression includes everything else. That is, the expression generates a member set that includes all members of the Flow dimension except the members of the first member set. The expression does this by using TypeFilterExclude to exclude the members from the previously used Flow types, PY Adj, Opening, and Appropriation.

  /* First SCOPE selects members of destination flow types */
  SCOPE [Flow].TypeFilterInclude("PY Adj", "Opening", "Appropriation");
       ()     += PRIORAVE * CURRENTTUPLE; 
  END SCOPE;
  
  /* Second SCOPE excludes previous members. This SCOPE includes all members excluded from first SCOPE */
  SCOPE [Flow].TypeFilterExclude("PY Adj", "Opening", "Appropriation", "Closing");
       ()     += AVE * CURRENTTUPLE; 
  END SCOPE;
  

Making a filter more selective

To make the filter more selective, you can nest the filter expressions. In a nested SCOPE expression, each successive SCOPE statement in the nesting sequence becomes more selective. The following example contains comments that explain each step.

/* Outer scope selects Income Statement acccounts */
SCOPE ([Account].ClassificationFilterInclude("Income statement"));

/* Inner scope applies only to member set just generated.
    Selects Income Statement accounts of type Intercompany Income */

    SCOPE([Account].TypeFilterInclude"Income IC");

For example, in the following snippet from the Currency Rule template, the outer SCOPE expressions first select Balance Sheet accounts. Then, from the selected Balance Sheet accounts, the scope selects only members whose Flow member is Opening or PY ADJ. The specific currency conversion applies to only the members who meet both of the filter criteria. This currency calculation first multiplies each selected value by the opening exchange rate (OPE) and then replaces the previous value with the converted value.

SCOPE [Account].CClassificationFilterInclude("Balance Sheet");
    SCOPE [Flow].TypeFilterInclude("Opening", "PY Adj");
         () += OPE * CURRENTTUPLE;
    END SCOPE;

You can use as many nested SCOPE statements as you require to generate a member set that meets your criteria.

See Also

Concepts

About the currency conversion template
Making a rule more general with #if - #else