Formulas in Reports
- 04 Mar 2026
- Print
- DarkLight
- PDF
Formulas in Reports
- Updated 04 Mar 2026
- Print
- DarkLight
- PDF
Article summary
Did you find this summary helpful?
Thank you for your feedback
The Report Builder includes the ability to define formulas that apply to the aggregated values in the rows or columns of a report. To use a formula, variables must be set that may then be used in the calculation.
We recommend building out the parts and respective data columns and data rows for the report first, before adding formulas.
Variables
The Variable Name setting can be found in a data column or data row. The setting is available only for Population column types, and Aggregate row types. A variable name can be added to a Group By row by changing the setting to Aggregate and choosing Count, which is identical to the Group By setting.
To set a variable for a Population type column or Aggregate type row, double-click the column or row to edit. In the Variable Name field, assign a variable. This variable name must be a unique value that is prefixed with the @ symbol and should be machine friendly (no spaces).
Define Formula
To define a formula, add a new data column or row group. Select a column type of Formula, or row type of Formula. Type the formula in the Formula field, using the previously designated variable names as desired.
Important!
It is not possible to define a formula that uses variables across rows and columns, i.e. a formula can only be derived for variables within columns or within rows, and formula rows will display blank values in formula columns.
Simple mathematical calculations can be performed, e.g.:
@variable * 100
@variable1 + @variable2
@variable1 / @variable2
Using Number Format to Display Percentages
To display percentages, use the nullif function in your formula along with the Number Format setting.
In the above example, the Percentage column is based on the formula:
@CurrentPeriod / nullif(@TotalApplications, 0)
This divides the Current Period column by the Total Applications column. The nullif function is used to prevent a divide-by-zero error. If @TotalApplications is 0, a null value will be returned instead. That way, the column will just display a blank (null) value, instead of erroring out the report.
The number format of 0.00% is used. Using the % symbol automatically converts values to percentages. There is no need to multiply by 100.
For the Biology row, the number of Total Applications is 3 and the number for Current Period is 1. The result in the Percentage column is displayed as 33.33%
For the Chemistry row, the number of Total Applications is 2 and the number for Current Period is 0. The result in the Percentage column is displayed as 0.00%
If the number format for the Percentage column were to be changed, the values displayed would be:
Number Format | Display |
|---|---|
# | Biology: (blank) Chemistry (blank) |
0 | Biology: 0 Chemistry: 0 |
0.0 | Biology: 0.3 Chemistry: 0.0 |
#.# | Biology: .3 Chemistry: (blank) |
#.#% | Biology: 33.3% Chemistry: % |
Example Formula Type Column
In the example below, we have two data table rows, with a type of Aggregate and a function of Count. The rows count the number of records for Fall 2018 and Spring 2018, respectively. The following columns have a type of Formula: Conversion Rate, Acceptance Rate, and Yield Rate.
Conversion Rate: @Applicant / nullif(@Prospect, 0)
Acceptance Rate: @Admit / nullif(@Applicant, 0)
Yield Rate: @Commit / nullif(@Admit, 0)
Example Formula Type Row
Using the same example, it is possible to assign a variable each to the Fall 2018 Total and Spring 2018 Total rows, and add a new row derived from a formula. The Overall row group has a type of Formula, with the following formula:
@Fall2018 + @Spring2018
This yields the following results:
The Conversation Rate, Acceptance Rate, and Yield Rate columns are empty, as a formula row cannot be applied to a formula column.
Note that the Overall row group could also be set up using a Group By row type, filtering on Fall 2018 and Spring 2018. This would allow the Conversion Rate, Acceptance Rate, and Yield Rate columns to be calculated. Row formulas have much more limited use cases than column formulas.
Was this article helpful?
