Configuring formula fields
Formula fields enable you to create virtual report fields with dynamically calculated values based on other report fields. These formula fields can then be used in columns, filtering rules, sorting rules, and grouping rules just like regular report fields.
The formula fields tab is divided into two sections:
- The section on the left displays a list of the formula fields that are currently configured.
- The section on the right displays the settings for the formula field that is being added or edited.
Adding a formula field
To add a new formula field, click the Add formula field button at the bottom of the list of formula fields on the left.
The settings for the new formula field will be displayed on the right.
See configuring formula field settings for more information on the settings available when adding a formula field. When you have finished configuring the formula field, click the Add button to add it to the report. If you decide not to add the formula field, click the Cancel button instead.
The new formula field's card will be displayed at the bottom of the list of formula fields on the left, and the new formula field's settings will remain displayed on the right for further editing.
Editing a formula field
To edit an existing formula field, click on the formula field's card in the list on the left.
The settings for the selected formula field will be displayed on the right.
See configuring formula field settings for more information on the settings available when editing a formula field. When you have finished configuring the formula field, click the Update button to apply your changes. If you decide not to apply your changes, click the Cancel button instead.
Removing a formula field
To remove an existing formula field, click on the formula field's card in the list on the left.
The settings for the selected formula field will be displayed on the right.
Click the Delete button to remove the formula field. If you decide not to remove the formula field, click the Cancel button instead.
Reordering formula fields
The order of formula fields is important when formula expressions use other formula fields as variables. A formula field that uses another formula field as a variable must appear lower in the list than the referenced formula field. To change the position of a formula field, click on the formula field's card and drag it up or down into the desired position.
Configuring formula field settings
Each formula field specifies a name, key, and formula expression, and can optionally specify a rollup expression.
Field name
Field name is a required field that specifies a name for the formula field. You'll reference this name when selecting the formula field for use in a column, filtering rule, sorting rule, or grouping rule.
Field key
Field key is a required field that specifies a unique identifier for the formula field. This key is used to reference the formula field as a variable in other formula expressions. For example, if you create a formula field with the key formula_labour_cost, other formula expressions can reference this field's value using formula_labour_cost as the variable name.
Field keys must:
- Be prefixed with
formula_ - Be unique across all formula fields in the report
- Contain only letters, numbers, and underscores after the prefix
Formula expression
Formula expression is a required field that defines how the formula field's value will be calculated. The formula expression is evaluated for each row in the report.
Expressions are automatically validated as you type. If the expression contains invalid syntax or references a variable name that doesn't exist, a validation error will be displayed below the input.
See the formulas reference to learn more about the expression syntax and available expression variables you can use to define your formula expression.
Rollup expression
Rollup expression is an optional field that allows you to define a custom aggregation method for the formula field. A rollup expression is similar to a formula expression, but it is evaluated using summary level data instead of row-level data.
See the formulas reference to learn more about the expression syntax and available expression variables you can use to define your rollup expression.
When a rollup expression is defined, the "Rollup" aggregation method will be available as an option if you configure a column based on the formula field.
