Scripts and expressions

XLReporting is not just highly configurable but also programmable, allowing multiple levels of sophistication. 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 program 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.
  • Manage - Profile - define custom functions which you can use in any expression across all your objects.

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 the desired 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 that the query delivered. 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();
}

Custom functions

By writing scripts in Manage - Profile, you can create your own custom functions which you can then call elsewhere in convert and filter expressions in reports, models, imports. You can also use custom functions to pass centrally-maintained configuration values to your reports and models. The only data that custom functions can access is the function parameters that you pass to them. You need to create your functions as properties of my.data (see below example) so that they become available across all objects of your tenant.

Example:
my.data.EXAMPLE = function(value1, value2) {
    return value1 + value2;
}

my.data.SETTING = function(name) {
    var values = {key: '123', class: 'xyz', category: 'abc'};
    return values[name];
}

Once created, you can use this function in any convert or filter expression. For example, EXAMPLE(2, 4) used in a convert expression for a report field would return 6.

Notes

Please note that scripts are case-sensitive and this also applies to function names, variables, and parameters. Scripts run within the browser session on the local user computer, and have no access to other computers or our servers.

Recommended reading:
Back to top | Functions | Data sets | Imports | Reports | Models

We value your privacy

We use cookies to enhance your browsing experience and analyze our traffic.
By accepting, you consent to our use of cookies.

Accept Reject Cookie Policy