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.
Returns true if ALL of the given conditions evaluate to true. You can specifiy multiple ranges.
This function is useful for applying multiple filters simultaneously, for example, selecting only records where a value exceeds a threshold and belongs to a specific category.
AND(data, data, ..)
Example:
AND(A2>1, B2>1) = true
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.
This function is useful for date or value range filtering, such as identifying transactions within a specific quarter or amounts between two thresholds.
BETWEEN(data, from, to)
Examples:
data = "2019-02"
BETWEEN(data, "2019-01", "2019-03")
= true
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)
This function is useful for keyword searches or flexible matching, such as checking if a text field includes specific items, accounts, or regions.
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
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.
This function is useful for simple list-based filtering, which is ideal for checking if a value matches selected companies, categories, or periods.
You can also watch the tutorial video.
ISANY(data, values, values, ..)
Example:
data = "2019-02"
ISANY(data, "2019-01", "2019-02") =
true
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.
This function is useful for finding missing values or validating that required data is present before analysis or import.
ISBLANK(data)
Example:
data = "2019-02"
ISBLANK(data) = false
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).
This function is useful for filtering user inputs or fields with binary logic like Yes/No, Active/Inactive, or Approved/Rejected.
ISBOOLEAN(data)
Example:
data = "YES"
ISBOOLEAN(data) = true
Returns true if the given data is a valid date, else returns false.
This function is useful for validating imported dates or filtering out records with invalid or missing date values.
ISDATE(data)
Example:
data = "31-1-2019"
ISDATE(data) = true
Returns true if the given data is a number, else returns false.
This function is useful for data integrity checks in reports, ensuring that numeric fields are usable for calculations.
ISNUMBER(data)
Example:
data = "100.23"
ISNUMBER(data) = true
Returns true if the given data is a valid period (YYYY-MM), else returns false.
This function is useful for filtering records by recognized reporting periods, especially when using dynamic time-based logic.
ISPERIOD(data)
Example:
data = "2019-01"
ISPERIOD(date) = true
Returns true if the given data is a text (and not a number or date), else returns false.
This function is useful for distinguishing between labels and values, or validating that a field contains narrative input instead of numbers.
ISTEXT(data)
Example:
data = "word"
ISTEXT(data) = true
Returns true if the given data is "truthy", and false for anything else. Truthy values are: positive numbers, "true" and "yes" (case-insensitive).
This function is useful for flagging approved entries or active states in reports or workflows with status tracking.
ISTRUE(data)
Example:
data = "YES"
ISTRUE(data) = true
Returns true if the given data is an error value, and false for anything else.
This function is useful for identifying problematic entries in models or imports so they can be excluded or highlighted.
ISERROR(data)
Example:
data = ERROR
ISERROR(data) = true
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.
This function is useful for inverse logic, e.g. filtering out specific values, such as NOT BETWEEN two periods.
NOT(data)
Example:
data = "1"
NOT(data) = false
Returns true if at least ONE of the given conditions evaluates to true. You can specifiy multiple ranges.
This function is useful for creating flexible filters where multiple criteria are acceptable (e.g. region A OR region B).
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")
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:
This function is useful for building dynamic reports and models where users define the scope, such as selecting a company, department, or time period, allowing highly customizable views and inputs.
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.
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:
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:
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.
This function is useful for dynamic period filtering in models and reports, especially in combination with PERIODSUM. It supports fiscal year offsets and lets users pick from lists, calendars, or predefined ranges.
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.
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.
This function is useful for applying mutually exclusive logic in decision rules, such as highlighting conflicts or enforcing clean single-choice selection.
XOR(data, data, ..)
Example:
XOR(A2>1, B2>1) = true