Functions for filters

These functions can be used in filters to enable to only retrieve 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.

AND(data)

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

BETWEEN

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

BETWEEN(data, from, to)

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

CONTAINS

Returns true if any of the given values exist within the given data, else returns false. You can provide any number of values.

CONTAINS(data, values)

Example:
data = "2019"
CONTAINS(data, "2018", "2019", "2020") = true

ISANY

Returns true if the given data equals any of the given values, else returns false. You can provide any number of values.

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.

ISANY(data, 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.

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 = "3 Jan 2019"
ISDATE(date) = true

ISNUMBER

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

ISNUMBER(data)

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

ISPERIOD

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.

OR(data)

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

SELECT

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 its simple default all the way to sophisticated 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 and/or multi-select 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.

SELECT(type, source, filter, label)

Type:
0 = optional + singleselect (default)
1 = optional + multiselect
2 = mandatory + singleselect
3 = mandatory + multiselect
4 = optional + multicombo
5 = mandatory + multicombo

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

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

SELECT(3, [Accounts: Code], [Accounts: Type] = "P&L") enables the user to choose 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]) enables the user to choose 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
The type multiselect lets users select multiple values. The type multicombo does the same but also lets users key in part of the desired value. This can be more user-friendly when the list of values is long.


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

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 identify, filter, and process the appropriate calculations based on the selected period.

Optionally, if you use fiscal years that deviate from calendar years, you can pass the start month (1-12) as an optional parameter. This will automatically adjust the period names and period sums that will be returned by the PERIODNAME and PERIODSUM functions.

The SELECTPERIOD function automatically builds a dynamic list of values from the field that you want to filter on.

SELECTPERIOD(start)

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

Example:
SELECTPERIOD() returns the period selected by the user
SELECTPERIOD(4) returns the period selected by the user, fiscal years start in April.

XOR

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

XOR(data)

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



Recommended reading:
Back to top | Imports | Reports | Models