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.
Returns true if ALL of the given conditions evaluate to true.
AND(data)
Example:
AND(A2>1, B2>1) = true
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
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
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
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
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
Returns true if the given data is a valid date, else returns false.
ISDATE(data)
Example:
data = "3 Jan 2019"
ISDATE(date) = true
Returns true if the given data is a number, else returns false.
ISNUMBER(data)
Example:
data = "100.23"
ISNUMBER(data) = true
Returns true if the given data is a valid period (YYYY-MM), else returns false.
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.
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).
ISTRUE(data)
Example:
data = "YES"
ISTRUE(data) = true
Returns true if the given data is an error value, and false for anything else.
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.
NOT(data)
Example:
data = "1"
NOT(data) = false
Returns true if at least ONE of the given conditions evaluates to true.
OR(data)
Example:
OR(A2>1, B2>1) = true
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:
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.
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.
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