There are two places where formulas can be used in the report designer tool:

  • Row Filter formula for reports
  • Value Source formula for cells

Both have the same syntax, but are used in different ways.


The general formula language is inspired by the Excel formula language, but is different in several ways. It is also possibly to literally use the Excel format for a value source formula. This is covered in a separate reference.


Using Formulas

Value Source Formulas

These can be entered in the Advanced section of Column Options.


A column must have a value source formula, otherwise it will be blank. The designer automatically generates a value source formula when you add a column to the report. It is simply the [Column Reference] for the column that was added.


To add a new report column so that you can enter a value source formula, click Layout -> Add Formula Column:


This will give a new blank column with the formula "Enter formula here" - a reminder to update the formula!


Row Filter Formulas

A single row filter formula can be entered in the Filter section of the designer. 


A row filter formula lets you exclude certain rows from appearing in the report. It must be some sort of comparison which produces a true/false outcome. If the formula evaluates to false for a row, it will be discarded. If the formula evaluates to true for a row, it will be included in the report.


Filter Functions:

FunctionExamples
Notes
FILTER(name, type, ["start"|"end"|"choice1", "choice2", ...])Only show Quantity Sold less than the selected value:
FILTER("Less than Qty", "integer") > [Quantity Sold]

Filter Order Date by a date range:
[Order Date] >= FILTER("Pick a Range", "date-range", "start")
AND [Order Date] <= FILTER("Pick a Range", "date-range", "end")

Filter Employee by a search string or show everything if the string is empty:
ICONTAINS([Employee], FILTER("Employee Name", "text"))
OR FILTER("Employee Name", "text") = NULL
OR FILTER("Employee Name", "text") = ""

Filter Payment method by using a drop-down list of choices:
[Payment Method] = FILTER("Payment Method", "drop-down", "All", "Credit Card", "Cash")
OR FILTER("Payment Method", "drop-down", "All", "Credit Card", "Cash") = "All"

Creates a custom filter for your report. It will be shown with the name you specify to the user and will be of a specific type. The value that the user has inputted in the filter when running the report will be inserted as the value for the whole function.

Valid types are: "text", "integer", "float", "drop-down", "date-range" and "date".

When using "date-range" types you must also specify if you want the start or end date value from the filter.


Formula Language Reference

Column References

Columns values are referenced by putting the column name in square brackets. For example: [A Column]


Note: The column names are provided by the data source and do not change in formulas if the title is changed. 


When entering a formula, the designer will auto-complete to help you get the column reference correct. It's a good idea to use one of the suggestions, as the formula will not be valid if the column reference is spelled incorrectly (including capitalisation!).


Literals

Fixed values (i.e. literals) can be used in formulas.

Type
Examples
Notes
Whole Number
5,  -12

Decimal Number
12.88,  -0.5

Text
"Hello",  "That's a bit \"interesting\"!"
To include a double quote within text, precede it with back slash \
Null
NULL
Means 'no value'. Useful for hiding values and excluding values from averages.


Operators

Operators can be used to combine values. Order of operations is respected (i.e. parentheses first, then */, then +-, left to right).

Types
Examples
Notes
Addition
[Total] + 100

Subtraction
[Total] - 100

Multiplication
[Total] * 100

Division
[Total] / 100
Division by 0 will result in a NULL value.
Text concatenation
"Hello " & [First Name]

Parentheses
5 * (10 + 20)


Comparisons

Values can be compared. This is useful for row filters, or when using the IF function.

Type
Examples
Notes
Greater than
[Total] > 5

Greater than or equal to
[Total] >= 5

Less than
[Total] < 5

Less than or equal to
[Total] <= 5

Equal to
[Total] = 5,  [Total] = NULL

Not equal to
[Total] <> 5,  [Total] <> NULL

AND
[Total] > 5 AND [Total] < 10 AND [Category] = "Widgets"
Use parentheses to combine AND and OR
OR
[Category] = "A" OR [Category] = "B" OR [Category] = "C"
Use parentheses to combine AND and OR
ANY[Category] = ANY("A", "B", "C")If the value matches any of the listed values


Functions

There are several functions which can be used to modify values. They consist of an upper case function name, followed by a number of arguments in brackets. For example: FUNCTION(value_1, value_2)


Text Functions

These functions only operate on text values.

Function
Examples
Notes
LEFT(value, character count)
LEFT([Client Name], 20)
Trims <value> to a maximum length of <character count> letter. <character count> should be a whole number.
RIGHT(value, character count)
RIGHT([Client Name], 10)
Trims <value> by taking the last <character count> letters. <character count> should be a whole number.
UPPER(value)
UPPER([Client Name])
Converts <value> to upper case.
LOWER(value)
LOWER([Client Name])
Converts <value> to lower case.
SUBSTITUTE(value, old text, new text)SUBSTITUTE([Client Name], "Smith", "Johnson")Replaces <old text> with <new text> in the string <value>.
SUBSTRING(value, regular expression)SUBSTRING([Note Text], "[a-z.]+@[a-z.]+")Extracts a substring from <value> which matches the given regular expression.
SPLIT(value, separator, index)SPLIT("Cat/Dog/Mouse", "/", 2)
returns "Dog"
Splits the string value into different parts as specified by the separated and returns the occurrence at the index position. The first value is at index 1.


Math Functions

These functions only operate on numeric values.

Function
Examples
Notes
CEIL(value)
CEIL([Line Total])
Rounds up to the nearest whole number.
FLOOR(value)
FLOOR([Line Total])
Rounds down to the nearest whole number.
SQRT(value)
SQRT([Quantity])
Square root.
ABS(value)
ABS([Line Total])
Absolute value (i.e. discard the negative sign).


Date Functions

These functions only operate on date values.

Function
Examples
Notes
YEAR(value, [month])
YEAR([Invoice Date])

YEAR([Financial Year Date], 7)
Snap to the start of the calendar year. If you specify a month number then it will snap to the beginning of that month, eg 2019-01-12 will snap to 2018-07-01 if month=7
YEAREND(value, [month])YEAREND([Invoice Date])

YEAREND([Financial Year Date], 7)
Snap to the end of the calendar year. If you specify a month number then it will assume the year begins on the first of that month. 2019-01-12 will snap to 2019-06-30 if month=7
QUARTER(value)
QUARTER([Invoice Date])
Snap to the start of the calendar quarter.
MONTH(value)
MONTH([Invoice Date])
Snap to the start of the calendar month.
MONTHEND(value)MONTHEND([Invoice Date])Snap to the last day of the calendar Month.
WEEK(value)
WEEK([Invoice Date])
Snap to the start of the calendar week (Monday).
DAY(value)
DAY([Created Date])
Snap a datetime to a date, discarding the time component.
HOUR(value)HOUR([Created Date])Snap the datetime to the hour.
MINUTE(value)MINUTE([Created Date])Snap the datetime to the minute.
YEARVALUE(value)YEARVALUE([Date])Returns the year from the date as a value, eg. 2017
MONTHVALUE(value)MONTHVALUE([Date])Returns the month component from 01 to 12
MONTHNAME(value)MONTHNAME([Invoice Date])Returns the month name eg. "April"
DAYVALUE(value)DAYVALUE([Invoice Date])Returns the date component eg. 27
DAYNAME(value)DAYNAME([Invoice Date])
The day of the week for the date, eg "Tuesday"
HOURVALUE(value)HOURVALUE([Invoice Date])
Returns the hour from the datetime as a value between 0 and 23.
MINUTEVALUE(value)MINUTEVALUE([Invoice Date])
Returns the minute from the datetime as a value between 0 and 59
DATETRIM(value)
DATETRIM([Invoice Date])
Adds a filter to the report allowing the user to change the date resolution on the fly (e.g. yearly, monthly). Especially useful when aggregating or grouping by dates.
TODAY()
TODAY()
Today's date. Useful when filtering for dates before/after today.
NOW()
NOW()
Current datetime. Useful when filtering for datetimes before/after now.
WORKDAYS(start, end, [holiday 1], [holiday 2], ...)WORKDAYS([Invoice Date], TODAY(), "2019-01-01", "2019-12-25")The number of work days (excludes Saturdays and Sundays) between start and end, inclusive of both days. Holidays can optionally be added as well in yyyy-mm-dd format or column references.
INTERVALVALUE(value, units)
INTERVALVALUE([Amount], "hours")
Creates an interval which can be added to or subtracted from a datetime.
Valid units are: second, minute, hour, day, week, month, year. The plural version of each unit is also valid.


Other Functions

Function
Examples
Notes
IF(condition_1, true_value_1, condition_n, true_value_n, false_value)
IF([Total] > 50, "Large", "Small")
IF([Total] > 100, "Huge", [Total] > 50, "Medium", "Small")
Similar to the Excel IF function, except multiple conditions are supported. The conditions are checked from left to right.
IFNULL(value, fallback)
IFNULL([Amount], 0)
Replace any blank value with the supplied <fallback>. Both <value> and <fallback> should be the same type. Useful to show zeros where otherwise would be blank.
NULLIF(value_to_check, null_value)
NULLIF([Amount], 0)
Replace where the <value_to_check> equals <null_value> with NULL. Useful to show blanks where otherwise would be zeros.
CONTAINS(value, search_text)
CONTAINS([Client Name], "Smith")
Gives a true value if <search_text> is found in <value>. Both arguments must be text types.
ICONTAINS(value, search_text)
ICONTAINS([Client Name], "smith")
Gives a true value if <search_text> is found in <value> ignoring upper/lower case. Both arguments must be text types.
STARTSWITH(value, search_text)
STARTSWITH([Client Name], "Xxx")
Gives a true value if <value> starts with <search_text>. Both arguments must be text types.
ENDSWITH(value, search_text)
ENDSWITH([Client Name], "Smith")
Gives a true value if <value> ends with <search_text>. Both arguments must be text types.
NUMBERVALUE(value)NUMBERVALUE("1.43")Converts a text string to a number.
DATEVALUE(value, formatting)DATEVALUE("2018-01-28", "yyyy-mm-dd")
DATEVALUE("28/1/18", "dd/mm/yy")
Converts a text string to a date according to the formatting template specified.
TEXT(value)TEXT(25.3)Converts a number (or date) to a text string.


Currency Conversion Functions

It is possible to convert currency values from one currency to a single base currency. By default AUD, CAD, EUR, GBP, NZD, USD and ZAR are supported as the "use_currency" values, but it can be overridden to use any other currency. If you know exactly which currencies your organisation(s) support, then it's advised to specify them in the function because it will speed up the number of lookups the report column has to do.


Function
Examples
Notes
FXAUD(from_currency, amount, [use_currency1], [use_currency2], ...)
FXAUD("USD", 23.51)
FXAUD([Currency], [Total], "JPY", "USD")
Convert the value to AUD base currency. Optionally specify use_currency values for each currency you want to support converting from.
FXCAD(from_currency, amount, [use_currency1], [use_currency2], ...)
FXCAD("USD", 23.51)
FXCAD([Currency], [Total], "JPY", "USD")
Convert the value to CAD base currency. Optionally specify use_currency values for each currency you want to support converting from.
FXEUR(from_currency, amount, [use_currency1], [use_currency2], ...)
FXEUR("USD", 23.51)
FXEUR([Currency], [Total], "JPY", "USD")
Convert the value to CAD base currency. Optionally specify use_currency values for each currency you want to support converting from.
FXGBP(from_currency, amount, [use_currency1], [use_currency2], ...)
FXGBP("USD", 23.51)
FXGBP([Currency], [Total], "JPY", "USD")
Convert the value to GBP base currency. Optionally specify use_currency values for each currency you want to support converting from.
FXNZD(from_currency, amount, [use_currency1], [use_currency2], ...)
FXNZD("USD", 23.51)
FXNZD([Currency], [Total], "JPY", "USD")
Convert the value to NZD base currency. Optionally specify use_currency values for each currency you want to support converting from.
FXUSD(from_currency, amount, [use_currency1], [use_currency2], ...)
FXUSD("CAD", 23.51)
FXUSD([Currency], [Total], "JPY", "CAD")
Convert the value to USD base currency. Optionally specify use_currency values for each currency you want to support converting from.
FXZAR(from_currency, amount, [use_currency1], [use_currency2], ...)
FXZAR("USD", 23.51)
FXZAR([Currency], [Total], "JPY", "USD")
Convert the value to ZAR base currency. Optionally specify use_currency values for each currency you want to support converting from.