Skip to main content
Adaptive Insights
Knowledge @ Adaptive Insights LLC, a Workday company.

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

  • LevelRollup(). Use this function to roll up account data on specified levels. Data rolls up based on how the level rollup is set for Accounts in Model Management. For example, to roll up the data for the Marketing, Engineering, and Sales levels on a single tier, use the syntax: LevelRollup([Marketing], [Engineering], [Sales]). If a level rollup contains a level that 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. 

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. In this example, Select.Region is the code applied to a specific level such as Sales - North. Region(+) represents the parent level for that specific level such as Operations.

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?