Formulas in Reports
- 08 Nov 2023
- 2 minute read
- Print
- DarkLight
- PDF
Formulas in Reports
- Updated 08 Nov 2023
- 2 minute read
- Print
- DarkLight
- PDF
Article summary
Did you find this summary helpful?
Thank you for your feedback
The Report Builder includes the ability to define and use formulas within a report. This is typically utilized to calculate admit rates, conversion rates, projected attendance, etc. 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 group. The setting is available only for Population column types, and Aggregate row types.
To set a variable for an existing Population type column or Aggregate type row group, double-click the column or row to edit.
In the Variable Name field, assign a variable. This variable name must be prefixed with the @ symbol.
Tip!
Use shorter names: For ease of writing and reading a formula, we recommend using user-friendly names as well (e.g. “@apps” or “@fall2017”).
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.
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 Number Format setting. Add the % symbol to automatically convert values to percentages. There is no need to multiply by 100.
In the above example, the Percentage column is based on the formula:
(Current Period / nullif(Total Applications, 0))
The number format of 0.00% is used.
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 Percentage were to be changed, the values displayed would be:
Number Format | Percentage |
|---|---|
# | Biology: (null) Chemistry (nul) |
0 | Biology: 0 Chemistry: 0 |
0.0 | Biology: 0.3 Chemistry: 0.0 |
#.# | Biology: .3 Chemistry: (null) |
#.#% | Biology: 33.3% Chemistry: % (null) |
Example Formula Type Column
In the example below, we have two data table row groups, with a type of Aggregate = 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 / Prospect
Acceptance Rate % = Admit / Applicant
Yield Rate % = Commit / Admit
Example of 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, where each column is defined as:
Fall 2018 Total + Spring 2018 Total
The Conversation Rate, Acceptance Rate, and Yield Rate columns are empty, as there are no numerical data in the respective cells on which the calculation should be performed.
Note that the Overall row group could also be set up using an Aggregate = Count row type, filtering on Fall 2018 and Spring 2018. Since there is numerical data in all the relevant columns, the formula columns Conversion Rate, Acceptance Rate, and Yield Rate can be calculated.
Was this article helpful?
