Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Formula Syntax and Rules for Custom Calculations in Reports

Provides formula syntax, rules, and examples of calculations in matrix reports.

Rules for Calculations

You add a calculation to an existing tier to calculate on that tier. For example, add a custom calculation to a levels tier to calculate on levels. See Add Calculations to a Matrix Report. A calculation formula can reference the following elements in a report:

  • The elements in the Elements panelcallout_1.png that are independent of the report properties or the user session. These elements are not on the report rows, columns, filters, or sheet tabs. For example, if you are calculating on accounts, you can use the same accounts in a formula as the ones that show under Elements > Accounts.  
  • The elements in the canvascallout_2.png such as on the rows, columns, filters, or sheet tabs. These elements are dependent on some report property or the user session. A calculation refers to an element in the canvas using the element code. For example, Current Version is an element on the canvas that does not refer to a specific value of the Version dimension. It changes to match whatever version the current user is on when running the report.

image showing elements panel and report canvas

Formula Syntax Examples

All standard operators and functions are available in the report formula syntax plus the following formula syntax for reports only. See Formula Functions and Operators and Functions for Calculations

Elements Pane

Enclose these elements in square brackets using [account code] or [element name]. See the following examples:

  • Accounts. For example, to calculate gross margin: [PL_Income]-[PL_COGS]
  • Custom dimensions. For example, to total the US, Japan, and Canada dimensions: [US]+[Japan]+[Canada].
  • Level dimensions. Use the plus (+) and minus (-) enclosed in parentheses to roll up parent elements. For example:
    • [Operations(+)] refers to the rollup of operations.

    • [Operations(-)] refers to the "operations only" level

  • Rollup(). Use this function to aggregate data based on the rollup mode (for example, sum or average) that is set for the account in Modeling. For example, to roll up the data for the Marketing, Engineering, and Sales levels, use: Rollup([Marketing], [Engineering], [Sales]). If a rollup contains a value that is a subset of another value, the function uses the parent value and ignores the subset. This Rollup() function is only available for custom calculation elements in reports. 

Report Canvas

Use RPT syntax to refer to elements in the report canvas using their codes. For example:

  • To refer to the first month in the report, use RPT.FirstMonth 
  • To calculate a region's share of the total, use: DIV(RPT.SelectRegion,[Region(+)])*100. Select.Region is the code for a specific level such as Sales - North. Region(+) represents the parent level for that specific level such as Operations.

    For example, you want to show what percentage of the total sales revenue the North America (NA) region contributes. To create this report, you add the Sales Revenue account and NA and its parent region to your report rows. You drag a custom calculation into the regions tier. On the columns, you add time and level (HQ). Add a formula to the custom calculation to calculate the NA share of sales revenue as a percentage of the region total.

    A region's share of the total

Element is a report parameter 

If an element is a report parameter, the parameter value reflects in the calculation. For example, a report displays time (Jan, Feb, March) on the columns and time is a parameter. A Subtotal column adds the values for the months. When you change the parameter to display Feb, March, and April, the Subtotal column now adds the values for these three months. To sum the three periods shown in the report, use:

RPT.FirstMonth + RPT.SecondMonth + RPT.ThirdMonth.

Note that FirstMonth, SecondMonth, and ThirdMonth are the codes for the time elements.

 

  • Was this article helpful?