- 08 Nov 2023
- 2 minute read

- Print
- DarkLight
- PDF

# Formulas in Reports

- Updated 08 Nov 2023
- 2 minute read

- Print
- DarkLight
- PDF

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.