Document toolboxDocument toolbox

This article is for Cloud. Visit Data Center

Structure's Formula Language (Expr)

Expr (pronounced like "expert" without the "t") is Structure’s formula language. It can be used in Formula Columns to perform calculations or comparisons based on issue fields, as well as in Sort generators.

If you’ve ever created formulas in Excel or Google Sheets, some elements of Expr will look familiar. We tried to make it as similar to other spreadsheet formula languages as possible while still allowing for more complex features, such as working with arrays or Jira item properties.

Learn Expr

There are a few ways to add formulas to a structure:

The guides below will cover the main elements of formulas and how to use them.

Variables are used to represent values (numbers, text strings, arrays, etc.) within a formula and can represent:

  • Jira issue fields

  • Calculated attributes like Progress

  • Structure-specific attributes like Item type

  • Attributes provided by other Jira apps

  • Another formula

  • Values from another Structure column

Naming Variables

Variable names can contain:

  • letters (English only)

  • numbers

  • underscore (_) characters

Variables cannot contain spaces. The first character must be a letter or an underscore.

Examples:

  • priority

  • sprintName

  • remaining_estimate

  • abc11

As you write your formula, Structure attempts to map your variables to well-known sources. For example, the "remaining_estimate" variable above will automatically be mapped to the Remaining Estimate field. If it can’t map the variable automatically, you will need to select a value for the variable.

Variable names are not case-sensitive. Priority, priority and pRiOrItY will all refer to the same variable.

Learn more: Mapping Variables, Predefined Variables

Property Access

Formulas can also access the value of an item's property using the following notation: object.property

fixVersion.releaseDate //returns the release date for the fixVersion

For a complete list of supported properties, see Item Properties.

Learn more: Expr Advanced Reference - Property Access

Local Variables

Local variables are similar to Variables, but they are defined locally within the formula, rather than being mapped to an attribute or Jira field. This can be helpful when an expression needs to be used more than once in the same formula. Instead of repeating the expression, you can turn it into a local variable, using  WITH :

WITH total_time = timeSpent + remainingEstimate : IF total_time > 0 : timeSpent / total_time

In this example, declaring a local variable “total_time” ensures that every instance of total_time is exactly the same, and if you ever need to change how total_time is calculated, you only have to change it once.

Learn more: Expr Advanced Reference - Local Variables

Functions are predefined expressions that can be used within a formula. Functions calculate a value based on their arguments.

  • A simple function formula: SUM(9, 3). This function takes the input values (9 and 3), adds them together, and returns a sum: 12.

  • A more useful formula: SUM(-original_estimate, remaining_estimate, time_spent) By summing these values, you can see how far over your original estimate you’re tracking.

Function Calls

A function call is written as the function name, followed by parentheses, which may or may not contain arguments.

Examples:

  • SUM(-original_estimate, remaining_estimate, time_spent)

  • CASE(priority, 'High*', 5, 1)

  • TODAY()

Function names are not case-sensitive: you can write TODAY() or Today().

There are 100+ standard functions available with Structure – see Standard Functions for a complete list.

Function arguments may be separated by comma (,) or semicolon (;). But you need to be consistent: use either all commas or all semicolons.

Learn more: https://tempo-io.atlassian.net/wiki/spaces/DRAFTSTRUCTUREDC/pages/3669066050

Aggregate Functions

Aggregate functions calculate the total value from multiple rows within a structure. Unless otherwise specified, this includes the issue and all its sub-issues.

Examples:

  • SUM { remaining_estimate + time_spent } – calculates the total effort (estimated and actual) for the issue and all its sub-issues.

  • MAX { resolved_date - created_date } – calculates the maximum time it took to resolve an issue, among the issue and its sub-issues.

Aggregate functions are written similar to standard functions, except they use curly braces: SUM{x}.

For a complete list of available Aggregate functions, see: Aggregate Functions

To learn more about using Aggregate functions, see: Expr Advanced Reference - Aggregate Function

Aggregate Function Modifiers

Aggregate Functions can also contain modifiers, which influence how the aggregation works. Modifiers always begin with the hash sign (#) and are placed after the function name and before the first curly brace:

  • SUM#all { business_value } – this will force the function to include values from all duplicate items in the total. (By default, duplicates are ignored.)

For a complete list of available Aggregate modifiers, see: Aggregate Functions

To learn more about using Aggregate functions and their modifiers, see: Expr Advanced Reference - Aggregate Function

Chained Function Calls

If you need to apply a sequence of functions to the same value, you can simplify this using the chained notation: listing each function one after the other, separated by a dot (.).

  • Standard notation: Funciton3(Function2(Function1(x)))

  • Chain notation: x.Function1().Function2().Function3()

When you use the chain notation, the value that comes before the dot becomes the first argument for the next function. Any additional arguments must be written after the function, in parentheses.

For example:

created.FORMAT_DATETIME("yyyy").CONCAT(" year issue")

In this example, FORMAT_DATETIME takes the date value in "created" and formats it based on the argument in parenthesis ("yyyy"). CONCAT takes the result from FORMAT_DATETIME and joins it with " year issue".

User Functions

A user function allows you to define a locally-used function within a formula.

Local functions are declared using the following construct:

WITH function_name(argument) = operation_to_perform :

Example:

Learn more: Expr Advanced Reference - User Functions

You can include arithmetic operations, comparisons, logical operations, and text operations within a formula.

Operations

Comments

Operations

Comments

+ - * /

Basic operators. Formulas follow the general precedence rules for arithmetic, so (2 + 3 * 4 = 14). When used, the value is converted to a number.

= !=

Equality and non-equality: if either part of the comparison is a number, the other part is also converted into a number. If both values are texts, then text comparison is used.

Text comparison ignores leading and trailing whitespace and is case-insensitive (according to Jira's system locale).

< <= > >=

Numerical comparisons. When used, both values are converted to numbers.

AND, OR, NOT

Logical operations.

CONCAT

An operation that joins together two text strings. This works similar to the function of the same name: a CONCAT b is the same as CONCAT(a, b).

( )

Parentheses can be used to group the results of operations prior to passing them to another operation. 

Learn more: Expr Advanced Reference - Operators.

Order of Operations

When several types of operations are used, they are done in the following order:

  1. Arithmetic operations

  2. Text operations (CONCAT)

  3. Comparison operations

  4. Logical operations. 

Conditional expressions (IF/ELSE) can be used to switch between two or more expressions, based on whether specified conditions are true (truthy) or false.

A simple "IF" expression can be declared using the IF() function, but for more elaborate IF cases, with multiple conditions and/or requiring an ELSE option, a conditional expression can be used:

Learn more: Expr Advanced Reference - Conditional Expressions

Learn More

To learn more about the concepts discussed above, see: