In this article:
Functions | Expressions | Scripts | Script events | Notes

Scripts and expressions

XLReporting allows for multiple levels of sophistication of logic. The below functionality applies to all configurable objects (reports, models, imports, and data sets).

Functions

Functions are methods that perform a single particular task on given parameters, and return the result. They are particularly useful in converting and filtering data in import, reports, and models:

Examples:
data = "this text"
LEFT(data, 3) = "thi"

Expressions

Expressions are one or more functions, variables, values, and operators combined in the right sequence to produce the desired result. You can use expressions wherever you can use functions, and they are particularly useful in converting and filtering data in import, reports, and models. You would typically use expressions when you want to perform multiple tasks in one single step:

Examples:
data = "this text"
UPPER(LEFT(data, 3)) + "$" = "THI$"

data = "5410.72"
ROUND(data / 1000, 1) = 5.4

The below example (from an import) substracts the Credit column from the Debit column in the source data, divides it by 1000, and then rounds the result to 0 decimals:

screenshot define imports columns convert

Scripts

Scripts are for advanced use, and only accessible if your user role has Edit script permission. They are small pieces of code that can perform automated and repetitive tasks.

You can create scripts using the Script field in the Define screen for reports, models, imports, and data sets.

You can use scripts in the following scenarios:

Reports - process the source data before the report is generated.
Models - process query data before it is inserted into the model.
Imports - process the source data before it is processed by the defined import mapping.
Data sets - process manually edited data before it is stored in the data set.

XLReporting scripts are compatible with JavaScript code. Your script will run in isolated scope without access to global or document objects, and will be passed an object called my which contains readonly metadata (e.g. column definitions) and runtime data (which can be edited by your script).

Your script can read the my.event status, the my.object reference, the my.columns metadata, and edit the my.data runtime data. Please note that scripts are being invoked multiple times for every object, and your script needs to evaluate the my.event status to properly respond to the desired event. See script events for further details.

The script editor window shows realtime data samples of the my object, which you can explore.

Example:
for (var row in my.data) {
my.data[row].f5 = my.data[row].f5 + " test";
}

If you want to call any of the standard functions in your script, you need to prefix their names with xlapp.exec (e.g. xlapp.exec.LEFT).

screenshot define scripts

Script events

Scripts are being called once for every query that is involved in retrieving an object, and once just after the report or model has been rendered on screen. For models that contain import queries, scripts are also being called once for every individual import query. Your script needs to evaluate the my.event status to properly respond to each selected event, and to prevent your script from getting executed multiple times.

For example, a model that contains 5 report queries will call your script 6 times: once for every import query (passing the column metadata and runtime data retrieved by that query), and once for the fully generated model (passing a reference to the rendered model).

The values of my.event and related properties are:

data - This applies to reports, and report queries and data queries within models. The property my.object contains the query identifier, my.layout contains the currently selected layout or sheet, my.columns contains the column metadata used by this query, and my.data holds the runtime data returned by the query. Your script can read, change, and write to the runtime data, before this data will be merged into the report.
render - This applies to reports and models. The property my.object contains a reference to the object on the screen (e.g. the report or model). The objects my.columns and my.data will both be empty for this event. When your script is being called with this event, the object is already fully rendered on-screen so your script can no longer edit the underlying data, but you can still access and use methods on the visual component (which is a webix widget).
import - This applies to imports and import queries within models. The property my.object contains the target data set or import query identifier, my.columns contains the column mapping used by the import query, and my.data holds the runtime data that will be processed by the mapping of the import query. Your script can read, change, and write to the runtime data, before this data will be mapped and imported into the data set.

Examples:
if (my.event == "data" && my.object == "m1:q1") {
    for (var row in my.data) {
        my.data[row].f5 = my.data[row].f5 + "test";
    }
}

if (my.event == "render") {
    my.object.disable();
}

Notes

Please note that scripts, function names, variables, and parameters are case-sensitive.

Scripts run within the browser session on the local user computer, and have no access to other computers, other tenants, or our servers.

Defined scripts may carry some risk and are continuously monitored and inspected by us.