---
title: "Using Formulas In Queries"
slug: "student-success-using-formulas-in-queries"
updated: 2026-04-08T19:25:43Z
published: 2026-04-08T19:25:43Z
canonical: "knowledge.technolutions.net/student-success-using-formulas-in-queries"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://knowledge.technolutions.net/llms.txt
> Use this file to discover all available pages before exploring further.

# Using Formulas In Queries for Student Success

Query formulas are a powerful tool for SIS exports and data integration, making it possible to export your data in precisely the format and layout required by external systems. Query Formula Export Parts can be used to:

- perform mathematical calculations
- combine multiple fields into one export column
- write if/then statements

and otherwise manipulate data in the Query Builder.

## Adding a Formula

1. Navigate to the Edit Query view of the query, insert a Subquery Export and select **Formula** as the Output and give the part a name.

[![Add Part](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/15673375495579.png?sv=2026-02-06&spr=https&st=2026-10-15T20%3A49%3A33Z&se=2026-10-15T21%3A49%3A33Z&sr=c&sp=r&sig=KidkCtAWfgedEPSrwUxARPXB8dh0DUJ%2FsilQf4v9Roc%3D)](https://knowledge.technolutions.com/guide-media/01H12FY2W3S6JYY2RGP6CEPBA1)
2. In the Edit Part window:
  - **Add Exports**: The corresponding export must be added in this section for each variable used in the formula. For example, if the formula is being used to find the average risk score for a student, all scores need to be added as exports. Here, we are taking the average of the following reported ratings:
    - Resiliency
    - Mindfulness
    - Emotional Exhaustion
    - Psychological Distress

[![Add Exports](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/15673662402843.png?sv=2026-02-06&spr=https&st=2026-10-15T20%3A49%3A33Z&se=2026-10-15T21%3A49%3A33Z&sr=c&sp=r&sig=KidkCtAWfgedEPSrwUxARPXB8dh0DUJ%2FsilQf4v9Roc%3D)](https://knowledge.technolutions.com/guide-media/01H12G9HVWDKVQZMW6S7EM563J)
3. Edit each newly added export:
  - Click on each export and alter the name removing any spaces or special characters (except underscores and/or dashes). Names must be unique within the formula.
  - Verify the format type for the export is compatible with the formula you are creating (*Int* for integers, *Real* for real numbers, *String* for words/character strings, etc.). This can be done by double clicking on the export and changing the Format Type. The export value, translation code, or a custom null value can also be used. Performing a mathematical operation on a combination of numbers and strings, as an example, may yield unexpected results.

[![Rename Exports](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/15673833932571.png?sv=2026-02-06&spr=https&st=2026-10-15T20%3A49%3A33Z&se=2026-10-15T21%3A49%3A33Z&sr=c&sp=r&sig=KidkCtAWfgedEPSrwUxARPXB8dh0DUJ%2FsilQf4v9Roc%3D)](https://knowledge.technolutions.com/guide-media/01H12GJZ7Q4X5T1DXJ8GBVRA71)
4. Enter the formula, adding `@` before export names to reference them. A few functions can be pre-populated in the Edit Part window. These can be accessed by entering a hash `#` in the Formula field. In this example, we are adding four different scores and then dividing by 4.

[![Create Formula](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/15674251906203.png?sv=2026-02-06&spr=https&st=2026-10-15T20%3A49%3A33Z&se=2026-10-15T21%3A49%3A33Z&sr=c&sp=r&sig=KidkCtAWfgedEPSrwUxARPXB8dh0DUJ%2FsilQf4v9Roc%3D)](https://knowledge.technolutions.com/guide-media/01H12H2RG0KJAYEYAWERWDJCZS)

## Common Functions

### Mathematical Operations

Most mathematical functions can be done using a formula. The data type must be compatible with the operation you are performing. When in doubt, use the data type of *real*.

#### Examples

- `@merit_aid + @fafsa_aid`
- `@tuition - @merit_aid`
- `@merit_aid * 4.0`
- `@total_merit_aid / 4.0`

### Case Statement

Returns different results based on certain conditions, like an 'if...then...' statement. It goes through the conditions in order and will return the result for the first condition met. If no conditions are met, the END result will be returned. If there is no END result, a null value will be returned.

#### Syntax

```sql
case
when condition1 then result1
when condition2 then result2
when conditionN then resultN
else result3
end
```

#### Example

```sql
case @person_student_type
when 'Senior' then 'This is a Senior'
when 'Junior' then 'This is a Junior'
when 'Sophomore' then 'This is a Sophomore'
else 'This is a First-Year'
end
```

### Coalesce

Returns the first non-null value in a series of values.

#### Syntax

```sql
coalesce(@value1, @value2, @valueN)
```

#### Example

```sql
coalesce(@major_enrolled, @major_requested)
```

### Convert

Changes the data type of a stored value for the export or filter. Data types that can be used:

- date
- datetime
- int (integer)
- money
- real
- string (characters)

This can be useful if you are hoping to perform mathematical functions on values that may not be stored as real numbers or integers. Most data types can be altered by double-clicking on the export and changing the 'Format Type.'

#### Syntax

```sql
try_convert(data_type, @value)
```

#### Example

```sql
try_convert(varchar(max), @record_guid)
```

### Date Addition

Adds a unit of time, using various units:

- minute
- hour
- day
- month
- year

to a date and then returns the new date. Negative interval values can be used to subtract a time from a specified date as well.

#### Syntax

```sql
dateadd(time_unit, interval_value, @value)
```

#### Example

```sql
dateadd(day, 4, @major_declaration_date)
```

### Date Difference

Subtracts Value 1 from Value 2 in various units of time: minute, hour, day, month, year. If you want to calculate Age, use the standard Person exports of 'Age (as of date)' and 'Current Age Based on Birthdate.'

#### Syntax

```sql
datediff(time_unit, @value1, @value2)
```

#### Example

```sql
datediff(day, @major_requested_date, @major_declaration_date)
```

### Like

Searches a value to see if it contains certain characters or numbers. Wildcards '%' can represent zero, one, or multiple variable characters.

#### Syntax

- `@value LIKE 'pattern'`
- `@value LIKE '%pattern'`
- `@value LIKE 'pattern%'`

#### Examples

```sql
@person_last LIKE 'Johnson'
```

```sql
@person_email LIKE '%@technolutions.com'
```

### Null If

Returns Value 1 if the values are not equal or returns a null value if they are equal. Can be used to avoid a mathematical error if dividing, and the denominator could be 0.

#### Syntax

```sql
nullif(@value1, @value2)
```

#### Example

```sql
@email_interactions / nullif(@total_interactions, 0)
```