Report Designer Formula Reference

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!

You may also reference another column from your report by using the Column Title {Column Title}.  i.e. surround the column title with {}. This allows you to use the value from a column in another column.  If you create a formula in a column, you can reference the formula in another column and display/use the result of the calculation.  This allows you to create complex formulas in one place, and then reference/reuse them in your report.  Any changes to the formula is then only required in one column, with the result flowing through to all columns that reference the {Column Title}.  

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.

You can also use the FILTER() function anywhere in a report. The function definition is FILTER(name, type, ["start"|"end"|"choice1", "choice2", ...])

FILTER("Less than Qty", "integer") > [Quantity Sold]

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

ICONTAINS([Employee], FILTER("Employee Name", "text"))
OR FILTER("Employee Name", "text") = NULL
OR FILTER("Employee Name", "text") = ""

[Payment Method] = FILTER("Payment Method", "drop-down", "All", "Credit Card", "Cash")
OR FILTER("Payment Method", "drop-down", "All", "Credit Card", "Cash") = "All"

[Product Name] = ANY(FILTER("Product", "drop-down-multi", "A4", "B5", "C6"))

OR "All" = ANY(FILTER("Product", "drop-down-multi", "A4", "B5", "C6"))

[Product Name] = ANY(FILTER("Product", "drop-down-multi-report", "937q8c91"))

OR "All" = ANY(FILTER("Product", "drop-down-multi-report", "937q8c91"))

FILTER("Search String", "text") = NULL 

OR

FILTER("Search String", "text") = ""

OR

SEARCHOR(IFNULL[Product Name], ""), FILTER("Search String", "text"))

FILTER("Search String", "text") = NULL OR
FILTER("Search String", "text") = "" OR
SEARCHAND(IFNULL[Product Name], ""), FILTER("Search String", "text"))

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.

Operators

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

Comparisons

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

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.

Math Functions

These functions only operate on numeric values.

Date Functions

These functions only operate on date values.

Other Functions

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.

The following always use the latest exchange rates, and are quite fast to execute:

The following can look back in time and use the exchange rate at a certain date:

Multi-Row Functions

Most of the functions in Wink Reports operates on the value in a single row. For instance you sum two columns from the same row.

However there are also functions which can be used to generate extra rows in a report, or to calculate values from neighbouring rows. The functions marked as "Window Functions" work the same as SQL window functions and operate across multiple rows, but won't reduce the number of rows in the report. To collapse rows for summaries, please look at Aggregation. It is also worth noting that using window functions and aggregation together in the same report can have unexpected results, so it's recommended to not combine them in your reports.

These functions can be conceptually tricky to understand, and are generally only recommended for advanced users. If you require any assistance, please raise a support ticket. :-)