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 small predefined methods that perform a 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 the relevant permissions. 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 import definition.
  • Data sets - process manually edited data before it is stored in the data set.
  • My account - Profile - define custom functions which you can use in any expression across all your objects.

XLReporting scripts support standard 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 read-only metadata (e.g. column definitions) and runtime data (which can be edited by your script).

Your script can read the properties of the my.columns object, and edit the my.data object. The script editor window shows a realtime data sample of both objects, 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

Custom functions

By writing scripts in My account - Profile, you can create your own custom functions which you can then use elsewhere in convert and filter expressions in reports, models, imports.

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

Once created, you can use this function in any convert or filter expression.
For example, EXAMPLE(1, 2) would return 3.

Notes

Please note that JavaScript is case-sensitive so this also applies to your scripts, function names, parameters, and inner constructs. 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