Add a Custom Calculation
You can use formulas to add Custom Calculations to Matrix reports:
- Navigate to Reports.
- Add or edit a Matrix report. Refer to Walkthrough Creating a Matrix Report for more information.
- Select Elements > Calculations > Custom. Drag a custom calculation element into the report.
- To create an formula that refers to dimensions in the report, select the Custom Calculations element you added and choose Formula Assistant from the context menu.
A formula term must be of the same dimension type as the tier that contains the calculation element. For example, a level calculation can refer to levels but not accounts, time, version or custom dimensions. There are two types of terms: absolute entity terms and dynamic terms that reference other elements on the report.
An absolute entity term refers to an element of the dimension that is not dependent on the properties of the report or user session. Most of the time they are identical to the items listed in the tree palette for the dimension. For example, when creating a level calculation, the list of absolute terms that can be used in a report formula is identical to the list of levels shown in the tree palette. The list of absolute terms that can be used in a report formula does not change no matter what elements have been placed on the report. In particular, a formula can refer to an absolute entity even if that entity is not used anywhere else in the report.
A dynamic element's value is dependent on some property in the report or user session. For example, the Current Version element of the Version dimension does not refer to a specific version entity, but to the current working version in this user's session. Dynamic elements cannot be referenced directly in a formula in a custom calculation element, but can be referenced by the element's code. A code is auto-generated for each element on the report and you can change this code by right-clicking an element and selecting Rename in the menu. The following is a list of dynamic elements:
Other Calculations, including Subtotal and Difference
Introduction to Report Formula Syntax
All standard formula operators and functions are available in the report formula syntax. In addition, there is specific formula syntax exclusive to reports that is described below.
When referencing absolute entities in a custom calculation element, the syntax is
[account code or element name]. See the following examples:
Accounts - when creating a calculation in an account tier, you would include the account codes in the formula syntax within square brackets. If you were creating a formula to represent Gross Margin, the syntax might look something like this:
Dimensions- when creating a calculation that references dimensions, you would include the dimension value name within square brackets. For example, if you wanted to total the US, Japan, and Canada dimensions, the formula syntax would look like this:
[US]+[Japan]+[Canada]. You can also use the plus
(-)syntax available to refer to values on this level only (use
(-)) or on this and all descendant levels (use
LevelRollup() - Use this function to roll up account data in specified levels. The way in which data rolls up in this function is dependent on how the level rollup is set in Modeling > Model Management > Accounts. For example, if you want to roll up the data for the Marketing, Engineering, and Sales levels on a single tier of the organization structure, the formula would look something like this:
LevelRollup([Marketing], [Engineering], [Sales])If a level rollup contains levels where one level is a subset of another level, the parent is used and the sub-level is ignored. This function is only available in custom calculation elements in reports.
Use RPT syntax to refer to other elements already on the report, by referencing their "code." For example:
To refer to the first month in the report:
To calculate a selected region's share of the total:
- When referring to an element which is parameterized, the parameter's value also flows into the calculation. For example:
To sum three periods shown on the report:
RPT.FirstMonth + RPT.SecondMonth + RPT.ThirdMonth