System functions

These functions are a collection of general-purpose tools. They can be used to obtain information in XLReporting, to add objects to reports and models, and to set specific rules for reports and models.

GUID

Returns a system-generated globally unique identifier (also known as universally unique identifiers). GUIDs are used in computer systems all over the globe. XLReporting generates the latest standard (v4) GUIDs which consist of 36 characters. You can use this function whenever you need to generate a unique ID.

Please note that by its very nature this function will return a different value every time a model or report is re-opened or recalculated.

GUID()

Example:
GUID() = "a1aee3e0-8b55-4827-5322-0795c48f5376"

HELP

Renders a clickable button that opens the given help page in the help center, in a separate window.

You need to specify the page name on the help center, not a full web page address. The optional "text" parameter defines the button label; if omitted, the button will show a default "Help" label.

HELP(data, text)

Examples:
HELP("dashboard")

✭ Tip:

You can also use this function in the description field of Define objects, combining it with text. For example: To learn more about this report, click HELP("dashboard")

HIDECOLUMN

Generates a small button that controls the visibility of the columns in the given range in Run model and optionally enables the user to hide or show those columns. You can also provide optional text.

Please note that this is only applicable in Run models.

HIDECOLUMN(range, type, text)

Type:
0 = allow user to show or hide (default)
1 = hide by default, allow user to show
2 = always hide

Example:
HIDECOLUMN(C3:D3)

HIDEROW

Generates a small button that controls the visibility of the rows in the given range in Run model and optionally enables the user to hide or show those rows. You can also provide optional text.

Please note that this is only applicable in Run models.

HIDEROW(range, type, text)

Type:
0 = allow user to show or hide (default)
1 = hide by default, allow user to show
2 = always hide

Example:
HIDEROW(A3:A5)

HIDESAVE

Hides or shows the Save button in models. This is only applicable in Run models for models that contain import queries and editors, which by default will show the Save button. Using this function you can (temporarily) stop the user from saving the model, based on some logic in your model, and show it again at a later point in time.

Please note that this is only applicable in Run models. The function returns no value into its cell, but will control the visibility of the Save button.

HIDESAVE(type)

Type:
0 - show the save button (default)
1 - hide the save button

Example:
HIDESAVE(1)
HIDESAVE(B5)

ICON

Renders a three-state colored icon indicating a negative, neutral, or positive result. This is useful to create visual KPI's. You can pass a negative value, zero, or a positive value, which will render the appropriate icon from the selected type in red (negative), yellow (zero), or green (positive) color. The optional "type" parameter defines the icon type:

The optional "scale" parameter defines the scale of tolerance for a neutral result and the color for positive and negative results. For example, if you specify 1000, the given value will be divided by 1000 and converted to an integer number before the result is evaluated. This ensures that values under 1000 show a neutral icon. If you specify a negative scale, negative results are shown as green, and positive results as red.

ICON(data, type, scale)

Example:
ICON(100, "arrow")
ICON(B1*C1, "check", 1000)

✭ Tip:

You can also specify a scale under 1. For example, if you are comparing percentages, you should specify a scale of 0.01

IMAGE

Renders an image using either an URL-based image or an icon name from the FontAwesome collection.

You can enter either an icon name (e.g. "star") or a full image URL (images are only allowed over https://). This function accepts "@logo" as an alias for the defined tenant logo.

By default, icons will have a grey color. You can change that by setting the optional "color" parameter to either a color name or hex code.

You can also use this function in the description field of Define objects, combining it with text to explain the use of the object to users.

IMAGE(data, color)

Example:
IMAGE("star")
IMAGE("star", "green")
IMAGE("https://www.mylogo.com/logo.png", "red")

✭ Tip:

As this function behaves the same as any other function, you can combine it with other functions, values, or cell references. For example: ="Our logo: " + IMAGE("https://www.mylogo.com/logo.png", "red")

LINK

Renders a clickable hyperlink to either a menu item or a web page.

You can either select an menu item from the dropdown list and optionally pass selection parameters, or enter a full URL to a web page (only links over https:// are allowed).

You can use this function to provide navigation links from one model or report to another (target) model or report, for example to provide a different layout or detail on selected data. You can pass optional filter values to the target model or report, separated by a comma. For example: Period=2019-12,Type=P&L. Filter values can be constructed dynamically in models (using cell functions) and in reports (using convert expressions).

Please note that you can only specify filter values on fields that are defined as user selections (using SELECT or SELECTPERIOD) in the target model or report.

You can also use this function to build a model that contains your own custom menu, replacing the standard Analyze menu. You can organise and style the menu items as required, combine it with other information, and add this model to a dashboard.

You can also use this function in the description field of Define objects, combining it with text to explain the use of the object to users.

LINK(data, text, filters)

Example:
LINK("r1")
LINK("r1", "Drilldown", "Period=2019-12")
LINK("https://www.mydocs.com/doc.pdf", "My document")

✭ Tip:

As this function behaves the same as any other function, you can combine it with other functions, values, or cell references. For example: ="See this link: " + LINK("https://www.mydocs.com/doc.pdf", "My document")

OBJECT

Returns various information about the currently selected object.

You can also use this function in the description field of Define objects, combining it with text to explain the use of the object to users.

OBJECT(type)

Type:
name        = object name (default)
id          = object id
description = object description
active      = (1=active, 0=locked)
group       = the group that this object belongs to

Example:
OBJECT() = "P&L report"
OBJECT("id") = "r1"

✭ Tip:

The object name and description can be changed by users at any time. If you want to uniquely identify an object, you should refer to the system-generated object "id" which will never change during the lifetime of an object.

PREVIOUS

Returns the value of the field on the previous data row (in an import, query, or report).

If you use this function for multiple fields in the same import, query, or report, you can provide the optional "name" parameter to either keep them apart, or purposely share previous data between different fields.

This function is useful if you want to compare the value on the current row to the value on the previous row, for example to detect if a grouping has changed.

Please note that this is done on the underlying data rows, which is useful in imports, but not necessarily useful in reports or queries because the underlying data is aggregated and sorted.

PREVIOUS(data, name)

Example:
PREVIOUS(data) to show the value of the previous row on the current row
IF(data = PREVIOUS(data), "New group", "Old group") to detect when a new grouping has started, based on the value in this column changing from one row to the next.

RANDBETWEEN

Returns a random integer number between the 2 numbers you specify (both inclusive).

Please note that by its very nature this function will return a different value every time a model is re-opened or recalculated.

RANDBETWEEN(number, number)

Example:
RANDBETWEEN(1, 100) = 73

RANDOM

Returns a random fractional number between 0 (inclusive) and 1 (exclusive).

Please note that by its very nature this function will return a different value every time a model is re-opened or recalculated.

RANDOM()

Example:
RANDOM() = 0.4464553

RECALC

Recalculates this field at every report subtotal level and report totals. This only has effect in Reports.

By default, field expressions are calculated at the underlying source data, its results are aggregated in reports, and subtotals and report totals are then calculated as per the setting in the report layout (e.g. sum, average, count etc). This does not always produce mathematically correct results, particularly when the expression includes a division operation.

For example: if an expression is "quantity * price", its result can perfectly be summed at any subtotal level. However, if an expression is "profit / revenue", the default subtotal operation does not work: we can't just sum percentages, we need to recalculate this formula at every subtotal level and report totals.

XLReporting automatically recalculates any expression that contains a division or the PERCENT function (which effectively performs a division). All other expressions follow the default subtotals and report totals setting. In cases where you want to explicitly force an expression to be recalculated, you should wrap your formula within a RECALC() function.

RECALC()

Example:
RECALC(profit / revenue)

REPEAT

Repeats the value of the field on the previous data row (in a report, query, or import) if this row has no value, else returns the value of the field on the current row.

If you use this function for multiple fields in the same report, query, or import, you can provide the optional "name" parameter to either keep them apart, or purposely share previous data between different fields.

This function is similar to the Repeat previous values option in Define imports, but provides a finer level of control, and can be used not just for imports, but for reports and model queries as well.

REPEAT(data, name)

Example:
REPEAT(data) = "4000" if the field on this row has no value and same field on the previous row contained "4000".

ROWACTION

Generates buttons that enable the user to add rows, copy rows, and delete rows in Run model. This function can also be used to lock all edits (buttons and cell editors) on the same row, based on some condition (for example, a validation of data). If locked, this is indicated by a padlock icon.

The "add", "copy", "delete", and "lock" parameters each represent a separate action, and they can be either a constant value (0 or 1), or a logical condition.

Please note that this is only applicable in Run models.

ROWACTION(add, copy, delete, lock)

Example:
ROWACTION(1, 0, 0)
ROWACTION(G1=1, 0, 0, H1>1)

LOCKCOLUMN

Tests a user-defined condition and if true, locks the editors in the subsequent or preceeding number of columns on the same row in Run model. The number can be positive to lock subsequent columns, or negative to lock preceeding columns. If locked, this is indicated by a padlock icon.

Please note that this is only applicable in Run models.

LOCKCOLUMN(data, number)

Example:
LOCKCOLUMN(G1 = 0, 2)
LOCKCOLUMN(G1 > 1, -2)

PERMIT

Returns true if the user in the current session has been assigned the given permit (that is, if at least one of the roles that have been assigned to the user has this permit), else false.

PERMIT(data)

Example:
PERMIT("save") returns true if at least one of the roles that have been assigned to the current user contains the permit "save".

ROLE

Returns true if the user in the current session has been assigned the given user role, else false.

ROLE(data)

Example:
ROLE("Administrator") returns true if at least one of the roles that have been assigned to the current user is called "Administrator".

ROWNUM

Returns the row number from the retrieved data (when used in imports, reports, or model queries), or the row number in the sheet (when used in models).

Please note that there is no guarantee as to the sort order of source data for imports, reports, or model queries. If you want to generate the row number in a particular order, you need to call SORT as well.

ROWNUM()

Example:
ROWNUM() = 5

SAVEACTION

Defines an action to be taken when the model is saved. This is only applicable in Run models for models that contain import queries. Using this function you can add a custom message to the "Save models" entry in the activity log, and optionally also send an email to a user, or show an on-screen notification to the user.

Please note that there can only be 1 SAVEACTION of each type in a model.

SAVEACTION(type, message, subject, email)

Type:
0 = no action (default)
1 = add message to activity
2 = send email
3 = show notification (success)
4 = show notification (error)

For type 1, 3, and 4 only the "message" parameter is required. For type 2, all parameters are mandatory. If you want to send an email to multiple addressees, you can separate them with a semi-column. For security concerns, you can not use more than 5 addressees.

Examples:
SAVEACTION(1, "User changed status") = will add this message to the activity log
SAVEACTION(B4 > 10, "User changed status to " + LEFT(B5, 10)) = will add this dynamic message
SAVEACTION(2, "User changed status", "Please approve", "john.doe@example.com") = will add this message as well as send an email to the designated user

SELECTED

Returns the value selected by the user in response to a SELECT function. You need to specify the field name that was used with the SELECT() function.

You can also pass the special alias @file to retrieve the most recently selected file name in an import.

If you do not provide a name, the function will return a formatted text with the currently selected value for every field that has a SELECT function (this can often be useful for headers in models).

SELECTED(name)

Example:
SELECTED("Company") = "US"
SELECTED() = "Period: 2019-03, Company=US"
SELECTED("@file") = "File: mydata.xlsx"

✭ Tip:

You can type xlapp.show.UserSelections.names in your browser console to see the names and current values of all selection parameters on the current page (please note you need Access console permission).

SELECTEDPERIOD

Returns the period selected by the user in response to a SELECTPERIOD function.

SELECTEDPERIOD()

Example:
SELECTEDPERIOD() returns the period selected by the user.

SORT

Sorts the underlying source data on this field.

In most cases there is no reason to sort source data for imports, reports, and model queries. The sort order in data sets is irrelevant, and reports are visually sorted at aggregate levels by settings in the report layout. Sorting the data is only relevant if you want to process the data in a very specific way (for example, looking for repeated, sequential, empty, or duplicate values in an import, and attach some custom logic to that). Sorting will be applied before any of the other functions are executed. When used in reports, please note that the actual report will always be sorted by settings in the report layout, regardless of the sorting in the process.

It is important to understand that this function -although it is defined for one field- actually expands its scope and will affect the entire source data. You can only define this function for one single field within any given import, report, or model query.

SORT(data, type)

Type:
0 = ascending order (default)
1 = descending order

Example:
SORT(data)

SPARKLINE

Renders a sparkline (a small chart) based on a range of values. By default, the sparkline will have a black color. You can change that by setting the optional "color" parameter to either a color name or hex code.

SPARKLINE(data, type, color)

Type: "line", "area", "bar", "pie", "spline", or "splinearea"

Example:
SPARKLINE(data, "line")
SPARKLINE(data, "line", "blue")

SLIDER

Renders a slider chart, indicating the position of the given value across a value range. The optional "type" parameter defines the marker type (see below). The optional "scale" parameter defines the scale (width) of the chart (default is 10).

SLIDER(data, min, max, type, scale)

Example:
SLIDER(70, 10, 100)

TENANT

Returns various information about the tenant in the current session.

You can also use this function in the description field of Define objects, combining it with text to explain the use of the object to users.

TENANT(type)

Type:
name    = tenant name (default)
address = tenant address
city    = tenant city
country = tenant country
license = tenant license
version = application version
server  = server information
region  = data region (AP, EU, US)

Example:
TENANT() = "Our Company"
TENANT("server") = "303.eu"

TOOLTIP

Renders a tooltip that shows a popup with the given text when the user hovers over the tooltip icon. The "text" parameter can be a string or an expression.

By default, the tooltip will be rendered with a default icon, but you can choose another icon from the FontAwesome collection by providing the icon name as the "icon" parameter (e.g. "star").

By default, the icon will have a blue color. You can change that by setting the optional "color" parameter to either a color name or hex code.

You can also use this function in the description field of Define objects, combining it with text to explain the use of the object to users.

TOOLTIP(text, icon, color)

Example:
TOOLTIP("Hello, here is a tip")
TOOLTIP("Warning, read this first", "star", "red")

TOPX

Sorts the underlying source data on this field and then limits the number of rows in the data. You can use this for imports, reports, and model queries. The "limit" parameter is optional and defaults to 10.

It is important to understand that this function -although it is defined for one field- actually expands its scope and will affect the entire source data. You can only define this function for one single field within any given import, report, or model query.

TOPX(data, limit, type)

Type:
0 = descending order (default)
1 = ascending order

Example:
TOPX(data) = top 10 list
TOPX(data, 50) = top 50 list

TRANSPOSE

Inserts values from the given cell range, a comma-delimited list, or a formula into a range of subsequent rows or columns in a model, starting at the current cell. The "type" parameter determines whether the values will be transposed into rows or into columns, and whether a new row or column will be inserted for every value. The "number" parameter is optional and specifies how many columns or rows you want to transpose.

This only takes effect when the model is being generated in Run model. In Define model, the cell that contains this function only shows information.

You can use this function in 4 different ways:

• If you reference a cell range, the transposed cells will contain formula references to the original cells, enabling you to create very dynamic models. Once generated, the model will contain as many transposed cells as there are cells in the given cell range.
• If you provide a comma-delimited list of values, the transposed cells will contain static texts. Once generated, the model will contain as many transposed cells as there are non-blank values in the given list.
• If you provide an embedded formula text, all transposed cells will contain that formula. You can include the placeholder [item] in your formula text which will be replaced with a sequential number (starting at 1) for each transposed cell. You need to start the formula with = and place it entirely within quotes (e.g. "=ADDPERIOD(A1, [item])" because you only want that formula to be executed once the transposed cell has been created, and not when you're typing it.
  
  When using an embedded formula text, you need to set the "number" parameter to specify how many columns or rows you want to transpose.
• If you provide the PERIODRANGE() function, the transposed cell will contain the range of periods you define. Once generated, the model will contain as many transposed cells as there are periods in the range you defined.

If you provide a static list of values (a cell range or comma-delimited list, you don't have to provide the "number" parameter. But it is required if you use a dynamic list of values or embedded formula texts

Other than as mentioned above, you can not combine this function with any other functions in the same cell.

TRANSPOSE is similar to DCOLUMNS and DROWS. If you want to insert dynamic query data into rows or columns (as opposed to a cell range or comma-delimited list), you should use the DROWS or DCOLUMN function.

TRANSPOSE(range, type, number)

Type:
0 - overwrite cells in existing rows (default)
1 - insert new row for every value

2 - overwrite cells in existing columns
3 - insert new column for every value

Examples:
TRANSPOSE(A1:F1, 1)
TRANSPOSE("Actual,Budget,Variance", 3)
TRANSPOSE("=ADDPERIOD(2019, [item]), 3, 6)
TRANSPOSE(PERIODRANGE("2019-01", 6), 3)
TRANSPOSE(PERIODRANGE(SELECTED("Period", 6), 3)

USER

Returns various information about the user in the current session.

You can also use this function in the description field of Define objects, combining it with text to explain the use of the object to users.

USER(type)

Type:
name     = user name (default)
email    = user email
login    = type of user (1=API login, 0=manual login)
roles    = the roles that have been assigned to this user
permits  = the permits (custom rights) that have been assigned to this user
group    = the group that this user belongs to
ip       = user IP address
browser  = user browser
language = user language
platform = user platform

Examples:
USER() = "john doe"
USER("email") = "john.doe@mycompany.com"