This article is for Cloud. Visit Data Center
Structure's Formula Language (Expr)
- Jaramy Conners
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:
Adding a Formula Column - this is where you’ll do most of your formula work
Adding a Bundled Formula - a great way to get started with formulas
Using a Sort by Formula Generator - a powerful way to organize your issues
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. |
| Logical operations. |
| An operation that joins together two text strings. This works similar to the function of the same name: |
| 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:
Arithmetic operations
Text operations (CONCAT)
Comparison operations
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: