Functions for filters

These functions can be used to select (filter) only the required data in imports, reports, or models. Using these functions you can also define dynamic user selection parameters.

AND

Returns true if ALL of the given conditions evaluate to true. You can specifiy multiple ranges.

AND(data, data, ..)

Example:
AND(A2>1, B2>1) = true

BETWEEN ❖ XLReporting

Returns true if the given data is between the given from and to parameters, else returns false. You can use this for numbers, texts, periods, and dates.

BETWEEN(data, from, to)

Examples:
data = "2019-02"
BETWEEN(data, "2019-01", "2019-03") = true

CONTAINS ❖ XLReporting

Returns true if any of the given values ("needles") exist within the given data ("haystack"), else returns false. You can provide any number of values. Both the haystack and each of the needles can either be a single value, a cell range, data array, or comma-delimited list of values. The check is always case-sensitive.

If the haystack is a single value, the function will check if the text any of the needle(s) exist (partially) within the haystack text.

If the haystack is a cell range, data array, or comma-delimited list of values, the function will check if any of the needle(s) exist (each in its entirety) within the haystack. If you want to search for partial text in each item in a range, you should convert the array into a combined string, for example: CONTAINS(JOIN(data, ""), needle)

CONTAINS(data, values, values, ..)

Examples:
data = "apples and oranges"
CONTAINS(data, "apple", "orange") = true

data = "apple,pear,orange"
CONTAINS(data, "apple", "orange") = true
CONTAINS(JOIN(data, ""), "app", "ora") = true

ISANY ❖ XLReporting

Returns true if the given data equals any of the given values, else returns false. You can provide any number of values, but they must be singe values (not arrays or cell ranges).

If you provide no values at all, then returns true if the given data is not blank. In other words, ISANY without values is the opposite of ISBLANK. The following values are considered blank: absent data, empty string "", a single space " ", a "0" text, and a 0 number.

You can also watch the tutorial video.

ISANY(data, values, values, ..)

Example:
data = "2019-02"
ISANY(data, "2019-01", "2019-02") = true

ISBLANK

Returns true if the given data is blank (empty), else returns false. ISBLANK is the opposite of ISANY without values. The following values are considered blank: absent data, empty string "", a single space " ", a "0" text, and a 0 number.

ISBLANK(data)

Example:
data = "2019-02"
ISBLANK(data) = false

ISBOOLEAN

Returns true if the given data is a boolean value ("truthy" or "falsey").

Truthy values are: positive numbers, true and yes (case-insensitive).
Falsey values are: negative numbers, false and no (case-insensitive).

ISBOOLEAN(data)

Example:
data = "YES"
ISBOOLEAN(data) = true

ISDATE

Returns true if the given data is a valid date, else returns false.

ISDATE(data)

Example:
data = "31-1-2019"
ISDATE(data) = true

ISNUMBER

Returns true if the given data is a number, else returns false.

ISNUMBER(data)

Example:
data = "100.23"
ISNUMBER(data) = true

ISPERIOD ❖ XLReporting

Returns true if the given data is a valid period (YYYY-MM), else returns false.

ISPERIOD(data)

Example:
data = "2019-01"
ISPERIOD(date) = true

ISTEXT

Returns true if the given data is a text (and not a number or date), else returns false.

ISTEXT(data)

Example:
data = "word"
ISTEXT(data) = true

ISTRUE

Returns true if the given data is "truthy", and false for anything else. Truthy values are: positive numbers, "true" and "yes" (case-insensitive).

ISTRUE(data)

Example:
data = "YES"
ISTRUE(data) = true

ISERROR

Returns true if the given data is an error value, and false for anything else.

ISERROR(data)

Example:
data = ERROR
ISERROR(data) = true

NOT

Returns a boolean value that is the opposite of the boolean value of the given data or expression. Usually, you combine NOT with one of the other filter functions, for example NOT BETWEEN.

NOT(data)

Example:
data = "1"
NOT(data) = false

OR

Returns true if at least ONE of the given conditions evaluates to true. You can specifiy multiple ranges.

OR(data, data, ..)

Examples:
OR(A2>1, B2>1)
OR(BETWEEN(1000, 1100), 2000, ">9000")
OR(BETWEEN("2019-01", "2019-03"), BETWEEN("2019-10", "2019-12")

SELECT ❖ XLReporting

Prompts the user to select a value from a list of values. This enables you to apply user-defined restrictions to the data that will be processed by imports, reports, or models (for example, to run a report for just a certain company or department).

The SELECT function can be used in different ways, from simple defaults to extensive logic:

• By default, the SELECT function automatically builds a dynamic list of values from the field that you want to filter on. Optionally, you can provide a different source (data set and field) as well as an optional filter. This enables you to generate a list of values fully independent from the field that you want to filter on.
• By default, lists of values in different SELECT fields are independent of each other. Optionally, you can define cascading selections, which makes the lists of values in user selections dependent on each other.
• By default, user selection of a value is optional and the user can select one single value. Optionally, you can make the user selection mandatory, allow selection of multiple values, and/or enabling type ahead, by setting the type parameter.
• By default, the list of values will also be used to filter your data. Optionally, you can define key-value selections that enable you to show the values of any field, but actually filter the data on the key field of the selected data set. You can specify this by adding 10 to the type parameter.
• By default, the SELECT function will show the label of the underlying data field and will merge multiple user selections that share the same label. If you want to override this behaviour, you can specify a custom label parameter.

You can also watch the tutorial video.

SELECT(type, source, filter, label)

type:
0 = optional  + select (default)
1 = optional  + multiselect
2 = mandatory + select
3 = mandatory + multiselect
4 = optional  + combo
5 = optional  + multicombo
6 = mandatory + combo
7 = mandatory + multicombo

Add 10 to the type if you want to use key-value selections, for example:
11 = optional + multiselect + key-value

Examples:
SELECT() prompts the user to select a single value
SELECT(3) prompts the user to select one or multiple values

SELECT(3, [Accounts: Code], [Accounts: Type] = "P&L") prompts the user to select one or multiple values from the list of accounts that have type "P&L".

SELECT([Chart of Accounts: Subcategory], [Chart of Accounts: Category] = SELECTED(Category)) will dynamically filter the list of values based on the selected value in another user selection (so-called cascading selections).

SELECT(10, [Companies: Legal Name]) prompts the user to select from a list of company names, but the data will be filtered on company code (so-called key-value selections).

Selecting the source
The source parameter can be a field in a data set (e.g. [Accounts: Account]), a static list of comma-separated values (e.g. "Apples,Pears,Bananas"), or an expression that returns a list of values (e.g. PERIODRANGE("2019", 12).

Selecting the filter
The filter parameter must contain a field in a data set with an operator and value to match against (e.g. [Accounts: Type] = "BS"). You can also use these filter functions: BETWEEN, CONTAINS, ISANY, ISBLANK, AND, OR, NOT. For more information, see filter functions.

Selecting multiple values
By default, users can select one single item from the list of values. The types multiselect and multicombo allow to user to select one or multiple values.

Type ahead while selecting
By default, users have to select from the list of values. If that list is long, it might be advisable to allow users to type ahead. By setting the types combo or multicombo, the list is dynamically filtered to matching choices, when a user starts typing some characters.

Cascading user selections
You can create user selections where the list of values depends on the selected value in another selection (so-called cascading selections) by adding a filter in a SELECT() call that uses SELECTED() to get the selected value of another user selection. See the above examples.

Key-value user selections
Key-value selections enable you to show a list of user-friendly names or descriptions from a data set, but filter the data on the key field of the same data set. You only need to provide the field that you want to show in the list of values, from a data set that has a defined key field, and XLReporting will automatically use the corresponding key field to filter the data. See the above examples.

Merging user selections
When you combine multiple queries into a model, or multiple imports into an import batch, or if you add multiple reports or models onto the same dashboard, XLReporting will automatically merge duplicate or repeated selections into one. As an example, if 2 queries both have a user selection with field name or label "Company", that selection will be shown only once and its value will be passed to both queries. This logic is automatically applied if the field name (or label), the source, and the filter of 2 or more SELECT calls are the same.

SELECTPERIOD ❖ XLReporting

Prompts the user to select a period from a list of periods. This function is required, and selection is mandatory, if you want to use the PERIODSUM functions. Both functions work together to select and calculate the appropriate amounts based on the selected period. You need to place PERIODSUM on a field containing periods (YYYY-MM).

The SELECTPERIOD function can be used in different ways, from simple defaults to sophisticated logic:

• By default, the SELECTPERIOD function automatically builds a dynamic list of periods from the period field that you want to filter on. So it will only show periods that exist in your data. Optionally, you can provide a different source (data set and field) as well as an optional filter. This enables you to generate a list of values fully independent from the field that you want to filter on.
• By default, fiscal years are assumed to start at month 1. Optionally, you can pass the startmonth (1-12) if you use fiscal years that deviate from calendar years. This will automatically adjust the period names and period sums that will be returned by the PERIODNAME and PERIODSUM functions.

Selecting the source
The source parameter can be a field in a data set (e.g. [Periods: Period]), a static list of comma-separated values (e.g. "2020-01,2020-02,2020-03"), or an expression with a function that returns a list of values (e.g. PERIODRANGE("2020", 12).

The source parameter can also be used to generate a list of years (YYYY), for example using the YEARRANGE() function. This will present a list of years to the user, whilst the period amounts will be calculated as if the last month in the year (YYYY-12) had been selected.

The source parameter can also be one of the following aliases which will show a dropdown calendar to the user:

• @date - shows a dropdown calendar that enables the user to select a date
• @period - shows a dropdown calendar that enables the user to select a month
• @year - shows a dropdown calendar that enables the user to select a year

Selecting the filter
The filter parameter must contain a field in a data set with an operator and value to match against (e.g. [Periods: Closed] = "N"). You can also use these filter functions: BETWEEN, CONTAINS, ISANY, ISBLANK, AND, OR, NOT. For more information, see filter functions.

You can also watch the tutorial video.

SELECTPERIOD(start, source, filter)

start:
1 - 12 = the 1st month of the fiscal year (optional, 1 = default)

Examples:
SELECTPERIOD() prompts the user to select a period (mandatory)
SELECTPERIOD(4) prompts the user to select a period (mandatory), with each fiscal year starting in April.
SELECTPERIOD([Periods: Period]) shows a custom list of periods, with the fiscal year starting in January.
SELECTPERIOD(PERIODRANGE(2020,12)) shows a predefined list of periods, with the fiscal year starting in January.

XOR

Returns true if exactly ONE (and not less and not more than one) of the given conditions evaluates to true. You can specifiy multiple ranges.

XOR(data, data, ..)

Example:
XOR(A2>1, B2>1) = true