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.
Returns the column number (or column name) in the sheet (only available in models).
COLNUM(type)
Type:
0 = column number (default)
1 = column name
Example:
COLNUM() = 5
COLNUM(1) = "E"
Returns the cumulative value (running total) of the field (in a report, query, or import).
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 add multiple fields into the same total.
You can optionally set the reset parameter to 1 when you want to start a new running total within the same report, query, or import.
CUMULATE(data, name, reset)
Example:
CUMULATE(data) = 300 if the field on all rows up to the current row
adds up to 300.
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"
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 (the lst section in the
URL), not a full web page address. The optional text parameter defines the button
label. If this is 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")
Controls the visibility of columns in the given range in Run model and using the optional type parameter, you can show a button to enable the user to hide or show those columns. You can also provide an optional text label in the button.
Please note that this is only applicable in Run models. The visibility is set when the model is being generated.
HIDECOLUMN(range, type, text)
Type:
0 = show initially, allow user to hide/show (default)
1 = hide
initially, allow user to show/hide
2 = always hide, no user choice
3 = always
show, no user choice
Examples:
HIDECOLUMN(C3:D3)
HIDECOLUMN(C3:D3, 1,
"Show")
HIDECOLUMN(C3:D3) + "Click me"
HIDECOLUMN(C3:D3, IF(SELECTEDPERIOD()>"2019-03",
2, 3))
Controls the visibility of the rows in the given range in Run model and using the optional type parameter you can show a button to enable the user to hide or show those rows. You can also provide an optional text label in the button.
Please note that this is only applicable in Run models. The visibility is set when the model is being generated.
HIDEROW(range, type, text)
Type:
0 = show initially, allow user to hide/show (default)
1 = hide
initially, allow user to show/hide
2 = always hide, no user choice
3 = always
show, no user choice
Examples:
HIDEROW(A3:A5)
HIDEROW(A3:A5, 1, "Show")
HIDEROW(A3:A5)
+ "Click me"
HIDEROW(A3:A5, IF(SELECTEDPERIOD()>"2019-03", 2, 3))
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 the button again when the logic changes. Or you can use this function to open a model as readonly, based on certain criteria.
By default, the cells remain editable even when the Save button is hidden. If you set the optional lock parameter, all cells will automatically be locked when the Save button is hidden, making the model effectively readonly. If the Save button is visible, this parameter has no effect.
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, lock)
Type:
0 = show the save button (default)
1 =
hide the save button
Lock:
0 = don't lock cells (default)
1 =
lock all cells if type=1
Example:
HIDESAVE(1)
HIDESAVE(B5=1, 1)
Renders a clickable hyperlink to an external web page, by providing a full URL to a web page (only secure links using https:// are allowed).
HYPERLINK(data, text)
Example:
HYPERLINK("https://www.mydocs.com/doc.pdf", "My document")
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 (see below).
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
Renders an image from a collection of 1.000 FontAwesome icons, 300 XLReporting icons, 50
application logo's, or any 3rd-party URL-based image.
You can enter any of the below:
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.
You can view all icon collections at:
FontAwesome
icons
XLReporting
icons (part 1)
XLReporting
icons (part 2)
XLReporting
icons (part 3)
XLReporting
icons (part 4)
XLReporting
icons (part 5)
Application
logos
IMAGE(data, color)
Example:
IMAGE("star")
IMAGE("star", "green")
IMAGE("logo/excel.png")
IMAGE("https://www.example.com/image.png", "red")
IMAGE("@logo")
✭ Tip:
As this function behaves the same as any other function, you can
combine it with other functions, values, or cell references. For example, you can make the color
dynamic by using IMAGE("star", IF(A1>100, "green", "red"))
Renders a clickable link to either a menu item or an external web page.
You can either
select an menu item and optionally pass user selection values, or enter a full URL to a web page
(only secure links using 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 select values to the target model or report, separated by a
comma. For example: Period=2019-12,Type=P&L. User selection values can be constructed
dynamically in models (using cell functions) and in reports (using convert expressions). If you
don't explicitly specify selection values, the current user selections will be used as
default.
Please note that you can only specify selection values on fields that are
defined as user selections (using SELECT or SELECTPERIOD) in the target model or report.
If you set the optional popup parameter to 1, the (target) model or report will be opened in a separate browser window. Links to external web pages will always be opened in a separate browser window.
You can set an optional text label in the button. In a model, you can style this link any way you want by using cell formatting.
You can also use this function to build a model that contains your own custom menu, as an alternative to using the standard 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, select, popup)
Example:
LINK("r1")
LINK("r1", "Drilldown", "Period=2019-12")
LINK("r1",
"Drilldown", "Period=2019-12", 1)
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")
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, and the number has to be static value (it can not be a calculation or result of a nested function).
LOCKCOLUMN(data, number)
Example:
LOCKCOLUMN(G1 = 0, 2)
LOCKCOLUMN(G1 > 1, -2)
Tests a user-defined condition and if true, locks all editors on the same row in Run model. If locked, this is indicated by a padlock icon.
Please note that this is only applicable in Run models.
LOCKROW(data)
Example:
LOCKROW(G1 = 0)
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 = status (1=active, 0=locked)
group = the group that this object belongs to
version = the current version
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.
Defines a page break when printing model or exporting to PDF (only available in models). You can place this function in any cell on a row. This function has no parameters, and is effectively an alias to PRINT("page").
PAGEBREAK()
Example:
PAGEBREAK()
Returns true if the given permit has been assigned to the user in the current session, through at least one of the user roles that have been assigned to the user, 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".
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.
Defines layout instructions when printing models or exporting to PDF (only available in models). This only takes effect in prints and PDF exports, on the screen you will see icons as placeholders. You can place this function in any cell on a row. You can use the optional start parameter to define the starting page number.
PRINT(type, start)
Type:
page = start a new page (default)
freeze =
freeze this row, to repeat it on every page (e.g. table header)
number  = placeholder
for dynamic page numbering
same = if possible, keep all rows until the
next PRINT("same") on the same page
concept = print a "concept" watermark
hide =
hide this row
skip = skip this sheet
Example:
PRINT("page")
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
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
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)
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".
Returns true if the given user role has been assigned to the user in the current session, 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".
Generates buttons that enable the user to add, copy, delete, and move rows in Run model. This function can also be used to lock all edits (buttons and cell editors) on the same row, based on a logical condition (for example, a validation of data). If locked, this is indicated by a padlock icon.
The add, copy, delete, move, 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, move, lock)
Example:
ROWACTION(1, 0, 0)
ROWACTION(G1=1, 0, 0, 0, H1>1)
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
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, or show an on-screen notification to the user, or optionally send an email with the given message and subject.
Please note that there can only be onle single 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
Renders a scorecard with a value, a given title and label, and optionally a
goal indicator and text color. As you can combine this function with other
functions and formulas, you can create dynamic KPI indicators. If you set the optional
goal
parameter, a positive indicator will show if the data is higher, and a negative indicator if the
data is lower. You can also set the text color with the optional color
parameter using either a color
name or hex code. You can also set the text color using cell formatting in models.
Please note that scorecards have a fixed size of 200x200px, so you may need to
set the cell width and height accordingly, or merge multiple cells.
Type:
0 = result is neutral
<0 = result is negative
>0
= result is positive
SCORECARD(data, title, label, goal, color)
Example:
SCORECARD(70, "Gross margin", "as % of sales", 65, "green")
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 alias @file as name to retrieve the most recently selected file name used in an import.
If you do not provide the name parameter, 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 Show console permission).
Returns the period selected by the user in response to a SELECTPERIOD function.
SELECTEDPERIOD()
Example:
SELECTEDPERIOD() returns the period selected by
the user.
Returns true if the given setting exists in the global settings of the tenant (or its value if defined), else false.
SETTING(data)
Examples:
SETTING("save") returns true if a setting "save" exists in the
global settings.
SETTING("vat") returns 21 if a setting "vat=21" exists in the global
settings.
Sorts the given cell range or data array, or underlying source data.
This function can also be used in Convert the data in reports, queries, and imports. In most scenarios, the sort order is irrelevant, as reports are sorted at aggregate levels according to the defined report layout. Sorting the data is only relevant if you want to process the underlying source data in a specific way (for example, looking for repeated, sequential, empty, or duplicate values, and apply some custom logic to that). Sorting will be applied before any of the other functions are executed. It is important to understand that this function (even though it is defined for just one field) 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 query.
SORT(data, type)
Type:
0 = ascending order (default)
1 = descending order
Example:
SORT(A1:A10)
SORT(data)
Sorts the given cell range or data array based on the values in another cell range or data array.
SORTBY(data, range, type)
Type:
0 = ascending order (default)
1
= descending order
Example:
SORTBY(A1:A10, B1:B10)
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"
"splinearea"
Example:
SPARKLINE(data,
"line")
SPARKLINE(data, "line", "blue")
Renders a slider chart, which indicates the position of a given value within a scale. The min and max parameters are optional and determine the scale. If they are omitted, the scale defaults from 0 to 100. The optional type parameter defines the marker type (see below).
SLIDER(data, min, max, type)
Example:
SLIDER(70, 10, 100, "star")
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
postcode = tenant postcode
city = tenant city
country = tenant country
industry = tenant industry
settings = global settings of the tenant
type = type of tenant (Standard, Sandbox, Trial, Partner, Client)
version = application version
server = server information
region = data region (AP, EU, US)
Example:
TENANT() = "Our Company"
TENANT("server") = "303.eu"
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")
Sorts the given cell range or data array, or underlying source data, and limits the number of returned rows. The limit parameter is optional and defaults to 10.
This function can also be used in Convert the data in reports, queries, and imports to limit the underlying source data. The limit will be applied before any of the other functions are executed. It is important to understand that this function (even though it is defined for just one field) expands its scope and will limit the entire source data. You can only define this function for one single field within any given import, report, or query.
TOPX(data, limit, type)
Type:
0 = descending order (default)
1 = ascending order
Example:
TOPX(A1:A10,
5)
TOPX(data, 20) = top 20 items in underlying source data
Applies automatic subtotal and total formules in reports and queries. This function is used automatically where needed, and not intended to be used manually.
TOTAL(data)
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 5 different ways:
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, and the function can not insert dynamic query data. It is important to understand that this function will be executed before any queries are being run.
TRANSPOSE is similar to DCOLUMNS and DROWS but for static data. 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)
Returns various information about the user in the current session. You can either lookup a system field (see the list of below types) or the name of a custom property.
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 = all roles that have been assigned to this user
permits = all 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
signon = signon method
Examples:
USER() = "john doe"
USER("email") =
"john.doe@mycompany.com"
USER("country") = "UK" (if you saved a property "country=UK" on
this
user)
Returns the value that is passed as a parameter, validated against its data type. This is mostly used in imports to define constant values.
VALUE(data)
Example:
VALUE("price") returns "price"
VALUE(34) returns 34