All | Text | Date | Number | Aggregate | Filters | Lookups | Period | Queries | Math | System | Financial | Conditional | Common | Special
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.
BADGE ❖ XLReporting
Renders a styled badge with the given data (text, date, or number) and color.
The badge will have a default background color. You can change that by setting the optional color parameter to either 1-90 for a reference to the standard color palette, or a color name or hex code The foreground color will be automatically set for optimal contrast.
You can optionally provide an icon to be added to the styled badge. Please refer to the IMAGE function for details on the icons you can use.
This function is useful for adding visual cues in reports or models, such as highlighting risk levels, review status, or KPI indicators with color-coded labels.
BADGE(data, color, icon)
Examples:
BADGE("Text", 6)
BADGE("Text", "green")
BADGE("Text",
"#008000")
BADGE("Text", "#008000", "star")
CHANGED ❖ XLReporting
Returns "Yes" if the given cell range on the current row in the sheet has been changed (only available in models). You must refer to the cell range in which you want to detect changes. This function has to be on the same row as the cell range you're referring to. As soon as any cell within that range is edited by the user in Run Model, the value will be set to "Yes".
You can use this function to show which rows have changed, or use it in import queries to determine which rows should be saved into a data set.
This function is useful for tracking edits in user input models, useful for identifying updated rows during review or import processes.
CHANGED(data, ..)
Example:
CHANGED(A1:E1) = "Yes"
COLNUM ❖ XLReporting
Returns the column number (or column name) in the sheet (only available in models).
This function is useful for building dynamic references or managing cell positions in advanced models where layout affects logic.
COLNUM(type)
type:
0 = column number (default)
1 = column name
Examples:
COLNUM() = 5
COLNUM(1) = "E"
COLOR ❖ XLReporting
Shows a marker with the given color.
You can change that by setting the color with the optional color parameter to either 1-90 for a reference to the standard color palette, or a color name or hex code.
This function is useful for providing status indicators, conditional formatting, or visual labels in dashboards and templates.
COLOR(color)
Example:
COLOR("green")
COLOR(32)
COLOR("#475466")
CONNECTED ❖ XLReporting
Returns information from the current import connector.
This function is useful in imports to collect information about the connected source system, the selected company, and all authorised companies.
CONNECTED(type)
type:
"name" = selected company name (default)
"id" = selected company id (unique internal reference)
"system" = selected source system
"list" = list of all company id's and names authorised by the user
Example:
CONNECTED() = "Xero Demo Company"
CONNECTED("system") = "xero"
CUMULATE ❖ XLReporting
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.
This function is useful for showing progressive totals, such as cumulative revenue, balances, or progressive progress in reports.
CUMULATE(data, name, reset)
Example:
CUMULATE(data) = 300 if the field on all rows up to the current row
adds up to 300.
EDITORS ❖ XLReporting
Shows an icon button that opens a popup window with the editors in the adjacent columns on the same row in Run model. Columns that do not contain an editor are ignored and not displayed in the popup. The names parameter defines the field names, and can either be a cell range, a comma-delimited list of names, or an integer with the number of adjacent columns.
This enables you to combine multiple editors into a single popup window. The selected values will be written back into the related cells, so this is simply a different data entry. It enables you to create a visually simpler model by hiding multiple editor columns and showing them in a single popup editor instead.
If you set the optional lock parameter, the editor will be locked.
This function is dynamic, meaning it will take effect after any edited cell. This enables you to dynamically change the range of editor columns and lock or unlock the popup window, providing an effective way of interactive data entry.
This function is useful for simplifying data input in models by consolidating multiple inputs into a single popup, which is ideal for clean user interfaces.
Please note that this is only applicable in Run models.
EDITORS(names, lock)
Examples:
EDITORS("Field 1,Field 2")
EDITORS(B2:D2,
F2>10)
EDITORS(3)
- For design clarity, the icon button is also visible in Define model, but is not active in that screen.
- Hidden columns that contain an editor are displayed in the popup. This enables you to minimize the number of visible editable columns, but still obtain the user input.
- As you can combine this function into any formula, you can make the appearance of the icon button and its related popup editor conditional based on some data in your model.
- Columns that do not contain an editor are ignored and not displayed in the popup.
- The table editor and the planner editor can not be used in a popup, and are therefore ignored.
GUID ❖ XLReporting
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.
This function is useful for uniquely tagging records during data entry or import to ensure traceability or for deduplication purposes.
GUID()
Example:
GUID() = "a1aee3e0-8b55-4827-5322-0795c48f5376"
HASH ❖ XLReporting
Returns a hash output ("fingerprint") computed from the given variable-length text. This process is irreversible, meaning the original text cannot be derived from the hash output. The operation is case-sensitive. Hashes can be used to encrypt a text, or to create a short representation of a text.
Please note that this function will always return the same hash output for a given text, but given the nature of this operation there is a small risk of "collision" where 2 different texts may result in the same hash output.
This function is useful for anonymizing or validating sensitive information such as employee IDs or transaction references in shared models.
HASH(data)
Example:
HASH("this text is an example") = "182jm0s"
HASH("this text is
another example") = "1ulfc88"
HELP ❖ XLReporting
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.
You can use the optional image parameter to show an image behind the button. This uses the IMAGE function and you can choose from any images listed there.
This function is useful for embedding contextual guidance directly in your models or reports to assist users with navigation or data entry.
HELP(data, text, image)
Examples:
HELP("dashboard")
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 ❖ XLReporting
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 only set once (1x time only), when the model is being generated. A typical use case could be to permanently hide columns, or to dynamically show or hide columns based on some runtime value, for example the SELECTED() function.
This function is useful for managing complex input sheets by hiding or revealing data columns based on runtime values or user actions.
HIDECOLUMN(range, 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, T3:U3)
HIDECOLUMN(C3:D3,
1, "Show")
HIDECOLUMN(C3:D3) + "Click me"
HIDECOLUMN(C3:D3,
IF(SELECTEDPERIOD()>"2019-03", 2, 3))
- For design clarity, these buttons are also visible in Define model, but they are not active in that screen.
- 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 dynamically set the type parameter based on an initial cell value, a condition, or on a user selection (for example, the SELECTED function).
- This function accepts a variable number of ranges with the last 2 parameters being optional. It detects the last 2 parameters by their value. If you are referring the type or text parameter to a cell, you should use the function CELLVALUE() instead of directly referring the cell itself. Example: HIDECOLUMN(A1:A1, CELLVALUE(C1))
HIDEROW ❖ XLReporting
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 only set once (1x time only), when the model is being generated. A typical use case could be to permanently hide rows, or to dynamically show or hide rows based on some runtime value, for example the SELECTED() function.
This function is useful for dynamically controlling the visibility of rows in financial models, based on user input or calculated conditions. Ideal for simplifying model interfaces and guiding data entry.
HIDEROW(range, 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, A20:A22)
HIDEROW(A3:A5,
1, "Show")
HIDEROW(A3:A5) + "Click me"
HIDEROW(A3:A5, IF(SELECTEDPERIOD()>"2019-03", 2,
3))
- For design clarity, these buttons are also visible in Define model, but they are not active in that screen.
- 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 dynamically set the type parameter based on an initial cell value, a condition, or on a user selection (for example the SELECTED function).
- This function accepts a variable number of ranges with the last 2 parameters being optional. It detects the last 2 parameters by their value. If you are referring the type or text parameter to a cell, you should use the function CELLVALUE() instead of directly referring the cell itself. Example: HIDECOLUMN(A1:A1, CELLVALUE(C1))
HIDESAVE ❖ XLReporting
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.
This function is dynamic, meaning it will take effect after any edited cell. This enables you to dynamically hide and show the Save button as and when the user is entering data into a model, providing an effective way of realtime data validation.
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.
This function is useful for enforcing data validation in Run Models, based on your own business logic and user input, before letting the user save any entered data, It ensures that data integrity is protected in your forecasting data.
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
Examples:
HIDESAVE(1)
HIDESAVE(B5=1, 1)
HYPERLINK
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).
This function is useful for linking to external documentation or regulatory resources from within a model. It simplifies access to context-specific references.
This function is an alias for the LINK function and thus identical. See that function for more eleborate explanantion.
HYPERLINK(data, text)
Example:
HYPERLINK("https://www.mydocs.com/doc.pdf", "My document")
ICON ❖ XLReporting
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 would ensure 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.
This function is useful for visualizing financial KPIs, risk scores, or budget statuses using intuitive traffic light indicators.
ICON(data, type, scale)
type:
Examples:
ICON(100, "arrow")
ICON(B1*C1, "check", 1000)
You can also specify a scale under 1. For example, if you are comparing percentages, you should specify a scale of 0.01
IMAGE ❖ XLReporting
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:
- a FontAwesome icon name (e.g. "star")
- an XLReporting icon (starting with "web/..")
- an application logo (starting with "logo/..")
- a full image URL (images are only allowed over https://)
- the alias "@logo" (which will show the default tenant logo)
Icons have a default color. They will follow the cell color in models, but you can also explicitly set the color with the optional color parameter to either 1-90 for a reference to the standard color palette, or a color name or hex code.
Images have their own color which can not be changed. You can use the 2nd parameter to set a fixed height of the image (instead of using its default height). Images will always maintain their original width-to-height ratio.
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.
This function is useful for branding, communicating visual context, or embedding reference icons directly in reports and dashboards.
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)
Examples:
IMAGE("star")
IMAGE("star", "green")
IMAGE("logo/excel.png")
IMAGE("logo/excel.png",
50)
IMAGE("https://www.example.com/image.png", "red")
IMAGE("@logo")
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"))
LINK ❖ XLReporting
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
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
semi-column (;) 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.
This function is useful for navigating between financial dashboards, models, and reports, while maintaining the user’s context and selections.
LINK(data, text, select, popup)
Examples:
LINK("r1")
LINK("r1", "Drilldown", "Period=2019-12")
LINK("r1",
"Drilldown", "Period=2019-12;Company-NL", 1)
LINK("https://www.mydocs.com/doc.pdf",
"My document")
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")
LOCKCOLUMN ❖ XLReporting
Tests a user-defined condition and if true, locks the editors in the adjacent number of columns (either subsequent or preceding) on the same row in Run model. The number can be positive to lock subsequent columns, or negative to lock preceding 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 a static value (it can not be a calculation or result of a nested function).
This function is useful for preventing edits in columns when business rules aren’t met, maintaining control over financial input sections.
LOCKCOLUMN(data, number)
Examples:
LOCKCOLUMN(G1 = 0, 2)
LOCKCOLUMN(G1 > 1, -2)
- For design clarity, the padlock icon is also visible in Define model, but is not active in that screen.
- ROWACTION also has a LOCK function, but it applies to the entire row. If you use ROWACTION and LOCKCOLUMN in the same cell, LOCKCOLUMN will take precedence.
LOCKROW ❖ XLReporting
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.
This function is useful for freezing entire rows once approved or submitted, ensuring integrity in budgeting workflows.
LOCKROW(data)
Example:
LOCKROW(G1 = 0)
- For design clarity, the padlock icon is also visible in Define model, but is not active in that screen.
- ROWACTION has a type parameter, which enables you to dynamically lock multiple rows. If you use ROWACTION and LOCKROW on the same row, ROWACTION will take precedence.
NOPRINT ❖ XLReporting
Show the given data on screen, but hide it in prints and PDF exports. You can use this to prevent sensitive information from being exported outside XLReporting. This function is similar to PRINT("hidecell", value) but does not show any icons on screen.
This function is useful for hiding confidential information in PDF exports while keeping it visible during data entry or review.
NOPRINT(data)
Example:
NOPRINT(G1)
OBJECT ❖ XLReporting
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.
This function is useful for retrieving metadata about the report or model, enabling context-aware logic and labeling.
OBJECT(type)
type:
"name" = object name (default)
"id" = object id (unique internal
reference)
"description" = object description
"active" = status (1=active, 0=locked)
"group" = the group that this object belongs to
"version" = the current version
Examples:
OBJECT() = "P&L report"
OBJECT("id") = "r1"
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.
PAGEBREAK ❖ XLReporting
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").
This function is useful for structuring financial reports and ensuring logical breaks when exporting to PDF.
PAGEBREAK()
Example:
PAGEBREAK()
PERMIT ❖ XLReporting
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.
This function is useful for role-based access control, enabling or disabling features based on user permissions.
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".
- Please note that permits are case-sensitive.
- As this function behaves the same as any other function, you can combine it with other functions, values, or cell references. For example: HIDESAVE(NOT(PERMIT("save")))
- If you want to check for multiple permits, you can combine them within AND() or OR() functions.
- This function tests if a specific permit has been assigned to the current user. You can also lookup all permits that have been assigned to the current user by calling the function USER("permits").
PREVIOUS ❖ XLReporting
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.
This function is useful for detecting grouping changes or validating sequential data consistency.
PREVIOUS(data, name)
Examples:
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.
PRINT ❖ XLReporting
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 use this function anywhere in your model.
This function enables you to dynamically define page numbering, pagination, repeating headers, and paragraphs (widow/orphan control). You can also dynamically hide sheets, rows, columns, and cells when printing or exporting to PDF.
XLReporting will always try to fit the content to the available page width, as following:
- If the content is wider than the page width, all column widths will be reduced proportionally.
- If the content is smaller than the page width, all column widths will be expanded proportionally as long as the difference (the remaining white space) is no more than 30% of the page width. Expanding column widths beyond 30% would stretch the content out of proportion, and possibly affect the height/width ratio of embedded charts, images, and tables. Therefore, in such case the content will not be fit to the page width, and white space will remain at the right margin of the page.
- By default, the width of all visible columns is proportionally adjusted to fit the page width. However, you can overrule that by setting PRINT("adjust") in one column to instruct XLReporting to adjust only the width of that single column. The width of all other columns will remain unchanged, as well as the height/width ratio of embedded charts, images, and tables.
Page numbering is automatic, starting at 1 and numbering sequentially through all pages and sheets in the model. Hidden sheets are excluded from page numbering. You can also specify a specific page number by setting PRINT("number", start) at any place in your model where start is the new page number. The page numbering will continue to increment from that number onwards.
This function is useful for customizing report output layouts, hiding sensitive rows, managing pagination, and branding exported reports.
PRINT(type, start)
type:
"page" = start a new page
(default)
"same" = keep a section of rows together on
the
same page, if possible
"freeze" = freeze this row, and repeat it on
every subsequent page (e.g. table header)
"number" = placeholder for
dynamic page numbers
"total" = placeholder for total number of
pages
"concept" = print a "concept" watermark
"hiderow" =
hide this entire row
"hidecolumn" = hide this entire column
"hidesheet" =
hide this entire sheet
"hidecell" = show the value on screen, but
hide it in print and PDF
"adjust" = adjust the width of this
column to fit the page width
Examples:
PRINT("page")
PRINT("number")
PRINT("number", 3)
- PRINT("same") instructs XLReporting to try and keep a section of rows together on the same page, if possible. If the section does not fit within the remaining space on the current page, it will start in its entirety on a new page. If the entire section is longer than 1 page, it will still break across 2 pages. A "section" starts at the 1st row that contains a PRINT("same") up until the next row that has a PRINT("same").
- Frozen rows (if defined via the toolbar in Define Model) will be repeated on every page. You can change the frozen row at any point by placing PRINT("freeze") on a row, which will then be repeated on all subsequent pages.
- PRINT("number") will be replaced by the actual page number on all pages. If you place this function on a frozen row or normal row, each page will show the appropriate page number. You can use this function within a formula, for example ="Page " + PRINT("number") + " of " + PRINT("total").
- You can make calls to PRINT() conditional based on some value in your model. For example =IF(A1=0, PRINT("hiderow"), "").
- PRINT("concept") enables you to include a watermark based on certain conditions, which takes preference over what the user selects in the Export or Print screen.
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.
This function is useful for generating test data in model development or simulations.
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.
This function is useful for Monte Carlo simulations or randomized testing in financial models.
RANDOM()
Example:
RANDOM() = 0.4464553
RECALC ❖ XLReporting
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.
This function is useful for correcting aggregate results like percentages in subtotal or total rows, especially when arithmetic logic must be preserved.
RECALC()
Example:
RECALC(profit / revenue)
REPEAT ❖ XLReporting
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.
This function is useful for filling down values in grouped data, in order to apply formulas to every row or creating a more readable layout.
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".
ROLE ❖ XLReporting
Returns true if the given user role has been assigned to the user in the current session, else false.
This function is useful for checking if a user has a specific access level and adjusting interface behavior accordingly.
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".
ROWACTION ❖ XLReporting
Generates icon buttons or a popup menu to 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 type parameters each represent a separate action, and they can be either a constant value (0 or 1), or a logical condition. The type parameter can also have a value of 2 to show the actions as a popup menu instead of as icon buttuns.
- add inserts a new row with all defined formulas, cell styles, and cell locks, but without any values.
- copy inserts a new row with all defined formulas, cell styles, and cell locks, with the values copied from the current row.
- delete deletes the current row, and remembers the values on this row (it works the same as a "cut & paste" operation).
- move inserts a new row with all defined formulas, cell styles, and cell locks, with the values copied from the most recently deleted row.
- type displays the actions as icon buttons (value 0), as popup menu (value 2), or locks all edits (value 1 or true) on the same row.
Please note that this is only applicable in Run models.
This function is useful for enabling controlled row-level editing actions in data entry models, such as adding or locking budget rows.
ROWACTION(add, copy, delete, move, type)
Examples:
ROWACTION(1, 0, 0)
ROWACTION(G1=1, 0, 0, 0, H1>1)
- For design clarity, the buttons are also visible in Define model, but they are not active in that screen.
- As this function behaves the same as any other function, you can combine it with other functions, values, or cell references. For example: ROWACTION(1, 0, 0) + "Click me".
ROWNUM ❖ XLReporting
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.
This function is useful for generating sequential row numbers for use in auditing or row-based calculations.
ROWNUM()
Example:
ROWNUM() = 5
SAVEACTION ❖ XLReporting
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 or SMS message with the given message and subject.
You can send an email by specifying the recipient email addresses(es). The recipients do not need to be registered users in XLReporting.
You can send a SMS message by specifying the email address of registered users in XLReporting, for example SAVEACTION("Budget rejected", "FYI", "john@mycompany.com"). For security reasons, you can not specify more than 5 recipients, duplicate messages are automatically prevented, and you can send messages ONLY to registered users in XLReporting, who have entered a correct mobile phone number. You can not directly specify other telephone numbers.
If you want to send an email or SMS message to multiple recipients, you can separate them with a semi-column.
This function is useful for notifying stakeholders of changes, triggering approvals, or logging events during save operations.
Please note that a model can only contain one single SAVEACTION.
SAVEACTION(type, message, subject, email)
type:
0 = no action (default)
1 = add message to activity
2 = send
email
3 = show notification of success
4 = show notification of error
5 = show
message and send email
6 = show message and send SMS message
For type 1, 3, and 4
only the message parameter is required.For type 2, 5 and 6 all parameters are
mandatory.
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@mycompany.com") = will show a message as well as send an email.
- You can use a logical condition in the type parameter as a way to enable or disable an action, based on the state of referenced cells in the opened model.
- You can use formulas or functions to create dynamic messages.
- The action is only triggered by changes to cells referenced as parameters of this function. Once the user has clicked SAVE, the action won't trigger again until referenced cell(s) are being changed again.
- If the user clicks on SAVE multiple times within the same model, with the same selections, and with unchanged data in the referenced cell(s), it would result into duplicate messages. In that case, only the 1st email or SMS will be sent, and not any subsequent duplicate messages.
- Standard subscriptions include a limit of 50 SMS messages sent per tenant per month. Extra charges will apply if you are sending more. Contact us for details if necessary.
- This function is intended to briefly inform recipients of some event, not of the details itself. Please keep the messages short, and do not expose sensitive information directly in emails or SMS messages. Our security systems monitor the outgoing communication sent by email and SMS to prevent improper use.
SCORECARD ❖ XLReporting
Renders a scorecard with the given value, title, and options. You can optionally provide a goal value, color, and width. If you set the optional goal parameter, tree-state icons will visualize if the value is higher, lower, or equal to the goal.
The options parameter will be shown below the value within the scorecard. You can optionally provide a label, a suffix, an image, an icon, and the icon scale, all separated by a comma. The label will be shown below the value, the suffix will be shown behind the value, the image will be shown below the title, and the icon is used to visualise how the value relates to the goal (see the icon types), with the (optional) scale defining the scale of tolerance for a neutral result and the color for positive and negative results. For example: setting options to "employed,FTE,chart-line" will show "employed" below the value, with "FTE" next to the value, and a "chart-line" image below the title. Options "employed,FTE,chart-line,circle,1000" will add an "circle" icon based on the goal value and on a scale of 1000. Please refer to the ICON function for more detail.
Scorecards have a default color. In models, they will adopt the cell color, but you can also explicitly set the color with the optional color parameter to either 1-90 for a reference to the standard color palette, or a color name or hex code. You can also set the text color using cell formatting in models.
By default, the width of scorecards is dynamically based on the width of the title, but you can explicitly set the width (in pixels) with the optional width parameter.
This function is useful for visualizing performance metrics or budget targets in a compact, informative format.
SCORECARD(data, title, options, goal, color, width)
Examples:
SCORECARD(70, "Gross margin", "of revenue,%", 65, "green")
SCORECARD(10,
"Staff", "employed,FTE,chart-line", 9)
SCORECARD(10, "Staff",
"employed,FTE,chart-line,circle,1000", 9)
SELECTED ❖ XLReporting
Returns the value selected by the user in response to a SELECT function. You need to specify the field name (or field id) that was used by 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).
This function is useful for dynamically referencing user-selected filters, such as period or department, in models and reports.
SELECTED(name)
Examples:
SELECTED("Company") = "US"
SELECTED() = "Period:
2019-03, Company=US"
SELECTED("@file") = "File: mydata.xlsx"
- 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).
- You can optionally also specify 2 separate parameters for the field name and field id. The function will first try the field id, and if that does not (or no longer) exist, it will use the field name. Example: SELECTED("Company", "t1:f1").
SELECTEDPERIOD ❖ XLReporting
Returns the period selected by the user in response to a SELECTPERIOD function.
This function is useful for synchronizing formulas and logic to the user’s selected financial period.
SELECTEDPERIOD()
Example:
SELECTEDPERIOD() returns the period selected by the user.
SETTING ❖ XLReporting
Returns true if the given setting exists in the global settings of the tenant (or its value if a value has been set), else false.
This function is useful for referencing company-specific global settings or toggles in your logic.
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.
- Please note that global settings are case-sensitive.
- Global settings can either be true/false (based on whether they exist or not), or they can contain a value if they are entered as "key=value".
- As this function behaves the same as any other function, you can combine it with other functions, values, or cell references. For example: HIDESAVE(NOT(SETTING("save")))
- If you want to check for multiple settings, you can combine them within AND() or OR() functions.
- This function tests if a specific setting exists in the global settings of the tenant. You can also lookup all settings by calling the function TENANT("settings").
SLIDER ❖ XLReporting
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 type of slider (see below).
Sliders have a default color. They will follow the cell color in models, but you can also explicitly set the color (except for the meter and progress types) with the optional color parameter to either 1-90 for a reference to the standard color palette, or a color name or hex code.
This function is useful for visualizing relative values, such as goal tracking, in an intuitive slider format.
SLIDER(data, min, max, type, color)
type:
Example:
SLIDER(70, 10, 100, "star")
SORT
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.
This function is useful for structuring data logically in reports or ensuring proper sequence before applying conditional logic.
SORT(data, type)
type:
0 = ascending order (default)
1 = descending order
Examples:
SORT(A1:A10)
SORT(data)
SORTBY
Sorts the given cell range or data array based on the values in another cell range or data array.
This function is useful for sorting one range based on the values of another, useful in aligning dependent data.
SORTBY(data, range, type)
type:
0 = ascending order (default)
1
= descending order
Example:
SORTBY(A1:A10, B1:B10)
SPARKLINE ❖ XLReporting
Renders a sparkline (a small chart) based on a cell range or data array, or a comma-delimited list of values. By default, sparklines have a black color. You can change that by setting the color with the optional color parameter to either 1-90 for a reference to the standard color palette, or a color name or hex code.
You can also use a single cell editor planner as data for this function. Sparklines will recognise and plot the values of the selected planner, including predefined aliases (such as @month, @quarter, @year etc) and custom planners.
For bar sparklines, you can also set a different color for negative values, using an additional parameter: SPARKLINE(data, type, color, negcolor).
This function is useful for visualizing trends in financial data, such as monthly revenue, in a compact format.
SPARKLINE(data, type, color)
type:
"line" = line
"area"
= area
"bar" = column
"pie"
= pie
"spline" = line (smooth)
"splinearea" = area (smooth)
Example:
SPARKLINE(data, "pie")
SPARKLINE(data, "line", "blue")
SPARKLINE(data,
"bar", "blue", "red")
TENANT ❖ XLReporting
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)
"id" = tenant id (unique internal reference)
"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)
"version" = application version
For preset configurations only:
segment1 = the name of segment1
segment2 = the name of segment2
This function is useful for retrieving information about your organization context, such as tenant ID or regional settings.
Examples:
TENANT() = "Our Company"
TENANT("server") = "303.eu"
TOOLTIP ❖ XLReporting
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 color with the optional color parameter to either 1-90 for a reference to the
standard color palette, or 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.
This function is useful for adding helpful hints or context explanations for users working in complex financial models.
TOOLTIP(text, icon, color)
Examples:
TOOLTIP("Hello, here is a tip")
TOOLTIP("Warning, read this
first", "star", "red")
- You can not use any HTML tags in the tooltip text, but you can use \n for line breaks.
- As this function behaves the same as any other function, you can combine it with
other functions, values, or cell references. For example:
TOOLTIP("Hello") + "Hover over me"
TOOLTIP("Hello " + LEFT(C4, 50))
TOPX ❖ XLReporting
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.
This function is useful for focusing reports on top-performing items or highlighting exceptions (e.g. top 10 expenses).
TOPX(data, limit, type)
type:
0 = descending order (default)
1 = ascending order
Examples:
TOPX(A1:A10, 5)
TOPX(data, 20) = top 20 items in underlying
source data
TOTAL
Returns report totals for report queries in models. This function is used automatically where needed, and not intended to be used manually.
This function is useful for fetching query totals in model calculations without needing manual aggregation.
TOTAL(data)
TRANSPOSE
Inserts values from the given cell range, a comma-delimited list of values, 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 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 SELECTED() function, the transposed cell will contain the (single or multiple) values that the user has selected.
- If you provide the YEARRANGE(), PERIODRANGE(), or DATERANGE() function as a parameter, the transposed cell will contain the dynamic range of years, periods or dates. Once generated, the model will contain a transposed cell for each item in the defined range.
If you provide a static list of values (a cell range or comma-delimited list of values, 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 of values), you should use the DROWS or DCOLUMN function.
This function is useful for converting data layouts between rows and columns to suit report or model designs.
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)
UPDATE ❖ XLReporting
Run and automatically save the given model with the given selections, to recalculate data using the latest logic.
You can use this function to instruct the system to run, recalculate, and instantly save models in a batch, each with different selections. A typical use-case is when you have changed logic or formulas in a model, or if you have imported new source data, and you want to recalculate all data accordingly.
You can pass optional select values to the target model, separated by a semi-column (;)
for example: Period=2019-12;Type=P&L. User selection values can be constructed dynamically in
convert expressions in a report, based on rows and fields in the report. 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.
This function is similar to LINK, in that it opens a model with certain selections. However, UPDATE goes further in that it also instantly saves the model (similar to when a user would click the Save button), and a series of (same or different) models can be run and saved as part of a batch, each with different selections.
You should use this function in reports, from which you can then run and save models. For example, if your report has 100 rows, it will run and save the specified model with the specified selections for each of the 100 rows. You can either use the same model for all rows, or a different model conditional on the data in your report.
You have to make sure that the models can be saved without requiring user intervention (such as validation), and all mandatory data must exist and be valid.
This function is useful for automating batch recalculations and saves across multiple scenarios or reporting dimensions.
UPDATE(data, select)
Examples:
UPDATE("m1", "Period=2019-12")
UPDATE("m1", "Period=2020-01")
USER ❖ XLReporting
Returns various information about the user in the current session. You can either lookup a standard 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.
For type name you can optionally provide the user id of another user in the same tenant, to get that user's name. This is not available for any other types.
This function is useful for customizing views or logic based on user identity, department, or assigned roles.
USER(type, id)
type:
"name" = user name (default)
"email" = user email
"id" = user id (unique internal
reference)
"login" = type of user (1=API login, 0=manual login)
"department" = department
"jobtitle" = job title
"mobile" = mobile phone
"roles" = all roles that have been assigned to this user
"filters" = all data filters 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 current 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)
VALUE
Returns the value that is passed as a parameter, validated against its data type. This is mostly used in imports to define constant values.
This function is useful for converting literal values into typed constants during data import or formula evaluation.
VALUE(data)
Examples:
VALUE("price") returns "price"
VALUE(34) returns 34