QPR ProcessAnalyzer Expressions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search

QPR ProcessAnalyzer expression language is a versatile query engine for all the QPR ProcessAnalyzer data and models. The expression language can perform variety of calculation operations, such as aggregations, dimensioning and following relations between objects.

Expression Language Overview

The picture below is an overview of all the major areas of the QPR ProcessAnalyzer expression language.


The expression language has the following areas:

  • Generic types cover the basic data structures familiar from many programming languages.
  • Configuration objects API allows to access the objects used to manage the content in QPR ProcessAnalyzer system. All objects are organized into projects which contains dashboards, models, datatables and scripts. In addition, models contain filters. Note that dashboards are not yet supported by the configuration objects API. There are also users and groups.
  • Machine learning allow to perform machine learning libraries calculated by the QPR ProcessAnalyzer system.
  • In-memory model API is the interface in the backend for the process mining calculations performed in the QPR ProcessAnalyzer Server memory. When the in-memory model API, the model needs to be loaded into the QPR ProcessAnalyzer Server memory and only after that the calculations can be done.
  • DataFlow is used to read data from a datasource, transform the data and load it to a destination which are the basic steps of ETL. The core idea of the DataFlowis that the data is processed in small chunks, allowing to handle unlimited amount of data without large computing resources. Note that the DataFlow can only do transformations that can be done row by row, such as adding new column. For example, sorting the data is not possible, as it would require to load the entire dataset to be able to perform the sorting operation. The source of the data can be an external system where the extract the data or QPR ProcessAnalyzer's own datatables for further processing previously extracted data.
  • DataFrame capsulates tabular datasets stored and processed in QPR ProcessAnalyzer Server memory. There is versatile set of functionality available to transform the data. Using the DataFrame requires that the entire dataset is loaded into the memory which sets certain limits how large datasets can be processed.
  • SqlDataFrame is conceptually similar to the DataFrame except the data is not in the memory but it's located in the datasource where it's stored (either Snowflake or SQL Server). The transformations are in practice done using SQL that is processed by the datasource itself. Using the SqlDataFramedoesn't pose any requirements to the QPR ProcessAnalyzer Server itself as the data never ends to there. Instead Snowflake warehouse size or SQL Server capacity is an important factor how large datasets can be managed.
  • SQL Expressions are part of the SqlDataFrame and they are expressions to make different kind of calculations to the data. Similar to the SqlDataFrame, the SQL Expressions are converted into SQL for the actual calculation in the datasource.

In the dashboards, there are two chart types available:

  • In-memory chart creates a in-memory queries to fetch the data. The in-memory query uses the In-memory model API to perform calculations. Thus, all the features and limitation of the In-memory model API affect the in-memory chart.
  • Big data chart creates a Snowflake query to fetch the data. The in-memory query uses the SqlDataFrame to perform calculations.

Expression language is run by QPR ProcessAnalyzer Server, and the expressions are used by dashboards and scripts.

Introduction to Expressions

  • Expression is sequence of calculation instructions written in a textual form, which are calculated by QPR ProcessAnalyzer Server.
  • Expressions may be broken down into sub-expressions which are calculated as part of the main expression calculation.
  • Calculating an expression always gives a result which may be any type of object.
  • Expressions are always calculated within some context.
  • Context can be accessed explicitly in an expression by using keyword _ (underscore).
  • Expression language is an object oriented, containing entities for all process mining objects (e.g. cases, events, variations) and also objects for managing content in QPR ProcessAnalyzer (models, projects, datatables).
  • Each object has an own context in which object's functions and properties can be used.
  • There is also the generic context which is used when the function or property is not found in the object specific context.

Chaining Expressions

Two expressions can be chained together using the dot (.) operator. Depending on the types of the objects (whether they are arrays or scalar values) on the left and right side, the result varies, and it's summarized in the table below. Calculation functions as follows:

  • If the first expression result is a scalar (i.e. not an array), the second expression will be calculated with the first expression result as its context.
  • If the first expression result is an array, the second expression will be calculated for each left side array item as context. The result will be an array of calculation results.
  • If additionally the second expression calculations return arrays, the resulting object will be an array of arrays, i.e. a new array is created for each of the left side array items.
  • If the left side expression is not an array, the calculation return a normal one-dimension array.
Left side Right side Result
scalar scalar scalar
scalar array array
array scalar array
array array two-dimensional array


The left side is a string (i.e. not an array), so that string will be the context for the expression after the dot:
"one".("Number is " + _.ToUpper())
Result: Number is ONE

The left side is an array, so the right side expression will be calculated for each of the array items:
["one", "two", "three"].("Number is " + _.ToUpper())
Result (array of three strings): ["Number is ONE", "Number is TWO", "Number is THREE"]

Both the left and right sides are arrays, so the results is an array of arrays:
["one", "two", "three"].(["Number is " + _.ToUpper(), "First letter is " + _.Substring(0,1)])
Result (array of arrays containing strings):
	["Number is ONE", "First letter is o"],
	["Number is TWO", "First letter is t"],
	["Number is THREE", "First letter is t"]

This is same as the previous, except an aggregation function is applied, which aggregates the deepest level:
StringJoin(", ", ["one", "two", "three"].(["Number is " + _.ToUpper(), "First letter is " + _.Substring(0,1)]))
Result: ["Number is ONE, First letter is o", "Number is TWO, First letter is t", "Number is THREE, First letter is t"]

Sum([1, 2, 3].([_ + 4, _ + 5]))
Result: [11, 13, 15]

Average(Sum([1, 2, 3].([_ + 4, _ + 5])))
Result: 13

let myCases = EventLogById(1).Cases
Result: Average duration of all cases in the EventLog 1.

let myCases = EventLogById(1).Cases
			let lastEvent = LastEvent.Typename;


Several expressions can be separated using semicolon (;). Example:

let var1 = 2;
let var2 = 3;
var1 + var2;
Returns: 5

Expression can contain line breaks without affecting the calculation.

Following arithmetic and logical operations are available:

  • Addition (+) can be performed on numeric types, strings (concatenate two strings) and Timespans (addition of two TimeSpans).
  • Subtraction (-) can be performed on numeric types, DateTimes (calculating TimeSpan between two DateTimes) and TimeSpans (difference between two TimeSpans).
  • Multiplication (*) can be performed on numeric types, and between a TimeSpan and a numeric type (multiplying TimeSpan by number which results in TimeSpan),
  • Division (/) can be performed on numeric types, between a TimeSpan and a numeric type (dividing a TimeSpan where TimeSpan must be the left hand side operand).
  • Remainder (%): can be performed on numeric types.
  • Comparison operators ==, <, <=, >, >=, != are used to compare objects and return boolean values.
  • Logical operators: && (AND), || (OR), ! (NOT) are used to combine expressions that return boolean values.

There is quick syntax for condition statement available:

condition ? trueValue : falseValue


var1 == 4 ? "Value is 4" : "Value is other than 4"

Common Datatypes

The expression language has the following basic datatypes available:

Data type Expression language type Literal expression Use cases How to convert
String String
"Hello world!"
"Characters to escape: \" and \\"
"Line 1\nLine2"
Strings are textual values. The usual mistake is to store numbers and dates as strings which makes difficult to make calculations and comparison with them. To convert variable var1 value to a string, use expression:
let var1 = 295.01;
Integer Int32 or Int64
Integers are whole numbers that describe numbers that don't have decimals, e.g. number of items etc. Equality comparisons with integers are possible. To convert variable var1 value to an integer, use expression:
let var1 = "29501";
Decimal number Double
Decimal numbers (expression language data type is double) may have decimals. Note that equality comparisons are not possible with decimal numbers. To convert variable var1 value to a decimal number, use expression:
let var1 = "295.01";
DateTime DateTime
DateTime(2021, 5, 21, 15, 30, 13, 482)
Dates are for the timestamps. If data is as string, the string needs to be splited into different parts, convert them into integers and DateTime function used. Example:
let var1 = "2020-10-21T11:48:04.842";
let splitted=var1.Split(["-", "T", ":", "."]).ToInteger(_);
Boolean Boolean
Booleans can have either true or false. If data is in a string, compare it agains a string literal "true". Example:
Duration Timespan

(no literal presentation)

Duration between two timestamps. Timespan() or TimespanFromTicks() functions.
Dictionary/JSON Dictionary/JSON
#{"name": "John", "age": 30, "children": ["Anna", "Mia", "Eric"]}
#{"number": 1, "text": "Hello!", "datetime": DateTime(2020)}
#{1: "number", "Hello!": "text", DateTime(2020): "datetime"}


  • Variables and datatable cells may also contain null values, which usually designate missing values etc.
  • Expression language data type for an object is seen using InternalTypeName property

Notes on string literals:

  • Characters " (double quote) and \ (backslash) need to be escaped using the \ (backslash) character.
  • Linebreaks can be added with \n and tabulator with \t.
  • Unicode characters can be added with \uXXXX where XXXX is a unicode character code, example: \u001f.

For writing date and timespan value literals, use the DateTime and Timespan functions.


Array is an object that is a list of any types of objects. Arrays can be created in an expression by enclosing a comma-separated list of items to brackets.


[1, 2, 3]
Returns: An array with three elements: 1, 2 and 3.

["Dallas", "New York", "Los Angeles"]
Returns: Array of strings (region names).

Returns: An empty array.

It's possible to apply operators (such as +, -, *, /, %, >, >=, <, <=, !=, ==) directly to arrays with the following rules:

  • If both operands are arrays, the operator is applied separately for each item in the arrays in a way that items at the same index are applied with each other. If the lengths of arrays are different, an exception is thrown.
  • If only the left or right side operator is an array, the operator is applied for each item in the array together with the non-array operand.
  • If both operands are not arrays, the operator is applied directly to the objects.


[1,2,3] + [4,5,6]
Returns: [5,7,9]

[1,2,3] > 2
Returns: [false,false,true]

[1,2,null] ?? 3
Returns: [1,2,3]

[[1,2,3],[5,6,7]] + [1,2]
Returns: [[2,3,4],[7,8,9]]

Arrays can also be used directly with those operators having one operand (-, !, ~). Examples:

Returns: [-1,-2,-3]

![true, [false, true], false]
Returns: [false,[true, false],true]

![0, [1, 0], 1]
Returns: [true, [false, true], false]

Logical operators (&& and ||) don't support arrays in the way described previously. Also, the null coalescing operator (??) supports arrays only for left side operand, whereas the right side operand cannot be an array.

Lookup operator ([ ])

Lookup operator (brackets) is used to get one or several items from an array or a dictionary. Using the brackets, it's possible to define:

  • single integer to get a single item from the array
  • array of integers to get multiple items from the array

Note that the indices start from the zero, and using an index which is not in the array will throw an exception.

In addition to static values, it's possible to defined a lookup expression inside the brackets. The lookup expression is evaluated in the context of the array where items are to be fetched.


[1, 2, 3, 4][1]
Returns: 2

[1, 2, 3, 4][Count(_) - 1]
Returns: 4

["a", "b", "c", "d"][[0, Count(_) - 1]]
Returns: ["a", "d"]

[[1, 2], 3, [4, [5, 6]]][2][1][0]
Returns: 5

The lookup operator also works for dictionaries:


#{"a": 1, "b": 2, "c": 3}["b"]
Returns: 2

#{1: "number", "Hello!": "text", DateTime(2020): "datetime"}[[DateTime(2020), "Hello!", 1]]
Returns: ["datetime", "text", "number"]

Also this kind of lookup can be used:

#{"a": 1, "b": 2, "c": 3}.b
Returns: 2

#{"a": 1, "b": 2, "c": 3}.(a+b+c)
Returns: 6

Define variables (let) and assign variable values (=)

Variables can be defined using the let operator:

let variableName = variableValue;

Variables can only be used in the same scope where they are defined. Variable cannot be initialized, if there is already a variable with the same name in the same scope.


let myVariable1 = "myValue";
let myVariable2 = Now;
let myVariable3 = 4;

Variables can be defined without the initial value. In that case, the variables get and _empty value. Example:

let myVariable1;

It's possible to assign (set) values to variables using the following syntax:

variableName = variableValue;


myVariable1 = "new value";
myVariable2 = Now;
myVariable3 = myVariable3 + 1;

Note that the variables need to be defined first to be able to set values for them.

Conditional statement (if)

Conditions can be written using the "if" statement which has the following syntax:

if (condition) {
  //run if condition is true
} else {
  //run if condition is false

The "else" block is not mandatory:

if (condition) {
  //run if condition is true

It's also possible to chain if's:

if (condition1) {
  //run if condition1 is true
} else if (condition2) {
  //run if condition2 is true (and condition1 false)
} else if (condition3) {
  //run if condition3 is true (and condition1 and condition2 false)
} else {
  //run if all of the above conditions are false

Conditional statement (switch)

Conditions can be written using the "switch" statement, following the JavaScript syntax (https://www.w3schools.com/js/js_switch.asp). Switch statement is more limited than the "if" statement, but the switch is easier to read and performs faster than the "if" statement (in cases when it can be used). Also the "break" and "default" statements are usually used with the switch. Switch statement has the following syntax (where expr, x and y are expressions):

switch (expr) {
  case x:
    // code block
  case y:
    // code block
    // code block

Example: dimension expression where a case attribute value is mapped to a textual presentation.

let label;
switch (Attribute("Shopping Cart Type")) {
  case "Standard":
    label = "Shopping cart type is standard";
  case "Fast track":
    label = "Shopping cart type is fast track";
  case null:
    label = "Shopping cart type is not defined";
    label = "Shopping cart type is other";

Looping statement (for)

The for loop can be defined using the following syntax:

for (initialization; condition; iterator) {

The initialization is evaluated in the beginning, and the iterator is evaluated after each iteration. The loop continues to the next iteration if the condition is true.

Example (returns 10):

let sum = 0;
for (let i = 1; i < 5; i++) {
  sum = sum + i;
return sum;

Looping statement (while)

The while loop can be defined using the following syntax:

while (condition) {
  //looped as many times the condition is true

Example: loop is stopped when the counter variable reaches 5:

let counter = 0;
while (counter < 5) {
  counter = counter + 1;

In addition, break (exit the while loop) and continue (start the next iteration) statements can be used with the while loop.

This example using break prints "12":

let items = "";
let i = 0;
while (i < 5) {
  i = i + 1;
  if (i == 3) {
  items = items + i;

This example using continue prints "1245":

let items = "";
let i = 0;
while (i < 5) {
  i = i + 1;
  if (i == 3) {
  items = items + i;

Return statement

It's possible to return from the current block with a value using the return statement. Example:

return "value to return";

The return value is optional. If not given, the _empty value is returned. Example:


The return statement works as follows in the different circumstances:

  • If currently evaluating an user defined function, then the return value is returned to the caller of the user defined function as function call result.
  • If currently evaluating a chained expression, the return value is used as the result of the chained evaluation.
  • If currently evaluating an argument for a function call, then the return value is used as the value of the parameter being evaluated.
  • Otherwise returns the value as the result of the evaluation of the whole expression.

Null conditional operator (?.)

The null conditional operator (one question mark) is useful in chaining operations where there might appear null values in the middle of the chain. If not handled correctly, e.g. trying to get a property from a null value, will throw an exception. In the null conditional operator, if the result of the left-hand side expression is a null value, the next step in the chaining operation is not executed, but a null value is returned instead.

For example, the following expression throws an exception if StartTime is null:


The null conditional operator can be used to take into account the null situation, and the following expression returns null if StartTime is null:


In the null conditional operator, if the left-side expression is an array or a hierarchical array, the chaining operator does not chain null values in that array (see the examples). Null conditional chaining can be applied to both contextless and hierarchical chaining operations by prefixing the chaining operator with ? character.

The null conditional operator is faster to calculate and syntax is easier to read than using if condition.


[DateTime(2020, 3, 15), null]?.truncate("year")
Returns: [DateTime(2020), null]
(would return error without the null conditional operator)

There is also a null conditional lookup operator which can be applied to the lookup operation by adding ? character in the front of the lookup operator. If used, and there is a null value instead of an array, the result of the lookup operation is null (an exception would be thrown without the null conditional lookup).


Returns: null

[[1, 2], null, [3]].(_?[0])
Returns: [1, null, 3]

Null coalescing operator (??)

The null coalescing operator (two question marks) can be used to replace null values with something else. The null coalescing operator works as follows:

  • If the left-hand side expression is null, the right-hand side value is returned.
  • If the left-hand side expression is not null, the left-hand side value is returned.

The null coalescing operator combined with the _remove operator is one way to remove null values from an array (see the examples below), but the recommended way is to use the RemoveNulls function.


null ?? "foo"
Returns: "foo"

1 ?? "foo"
Returns: 1

[1, null, 3].(_ ?? "foo")
Returns: [1, "foo", 3]

[1, null, 3].(_ ?? _remove)
Returns: [1, 3]

[1, null, 3]:(_ ?? _remove)
Returns: [1: [1], 3: [3]]

Increment and decrement operators

Increment (++) and decrement (--) operators serve as a short syntax to increase/decrease numeric value by one. The operators can be used before (prefix) or after (postfix) the incremented/decremented variable. When used as prefix, the changed variable value is returned by the increment/decrement statement (i.e., it can be seen that the increment/decrement is made before the other operations in the same row are made). When used as postfix, the original variable value is returned by the increment/decrement statement (i.e., it can be seen that the increment/decrement is made after the other operations in the same row are made).


let myVar1 = 1;
let myVar2 = myVar1++; // myVar2 = 1
let myVar1 = 1;
let myVar2 = ++myVar1; // myVar2 = 2
let myVar1 = 1;
let myVar2 = myVar1--; // myVar2 = 1
let myVar1 = 1;
let myVar2 = --myVar1; // myVar2 = 0
let counter = 0;
while (counter++ < 3) {
	WriteLog(counter); // Writes to log: 1, 2, 3
let counter = 0;
while (++counter < 3) {
	WriteLog(counter); // Writes to log: 1, 2

Catching exceptions

The try...catch statement is used to catch thrown exceptions, to handle the exception situation and continue executing script despite the exception. The statement comprises of a try block and either a catch block, a finally block, or both. The code in the try block is executed first, and if it throws an exception, the code in the catch block is executed. The code in the finally block will always be executed before control flow exits the entire construct.

try...catch statement structure:

try {
} catch (exceptionVariable) {
} finally {

There are following blocks in the above example:

  • tryStatements: Statements to be executed.
  • catchStatements: Statements that are executed if an exception is thrown in the try block.
  • exceptionVariable: Optional identifier to hold the caught exception for the associated catch block.
  • finallyStatements: Statements that are executed before control flow exits the construct. These statements execute regardless of whether an exception was thrown or caught.

Example to catch errors in data extraction, log the error and continue script execution:

try {
  results = ExtractSap(queryParameters);
} catch (error) {
  Writelog("Data extraction from SAP failed.");

Throwing objects

Objects can be thrown using the throw statement. For example, strings can be thrown, but the thrown object can also be a more complex dictionary containing additional data. Note that there is no separate exception entity that can be thrown.

Following example throws a string that can contain a message why the processing was stopped:

throw "Error occurred etc.";

The thrown object can be caught using the try-catch statement. The caught entity is an exception object that has the thrown object in parameter thrownObject.


try {
  throw "Error occurred etc.";
} catch (exception) {

Defining functions

Functions can be defined using following syntax (called lambda syntax):

(param1, param2, param3) => (function definition)


Function to add to numbers:
(a, b) => a + b

Function to return the current time:
() => Now

FunctionDefinition encapsulates a function consisting of following parts:

  • function body (expression)
  • function parameters (optional)
  • function name.

FunctionDefinition objects can be created in the following ways:

  • Using function <function name>(<function arguments>)<function body> syntax
  • Using (<function arguments>) => <function body> syntax
  • Using Def function
  • In Def function, use &-prefix is for an attribute

Functions defined by a FunctionDefinition object can be called as follows: <function name>(<function arguments>). Functions are evaluated in a new scope, i.e. variables defined in the function are only available within that function.


Def(null, "a", "b", a+b)._(1,2)
((a, b)=>a+b)._(1,2)
((a, b)=>a+b)(1,2)
Returns: 3

[Def("", "a", "b", a+b), Def("", "a", "b", a*b)].(_(1,2))
[(a, b)=>a+b, (a, b)=>a*b].(_(1,2))
Returns: [3, 2]

Def("FirstOrNull", "arr", CountTop(arr) > 0 ? arr[0] : null)
function FirstOrNull(arr) { CountTop(arr) > 0 ? arr[0] : null }
Result: Are all equivalent and create a function FirstOrNull into the current evaluation scope. Returns also the created FunctionDefinition object.

The FunctionDefinition objects have the following properties:

  • Arguments: Returns an array containing names of all arguments of the function.
  • Body: Expression body of the function as string.
  • Name: Name of the function if this is a named function.

It's possible to define functions that call themselves, which is used in recursive algorithms. There is a certain limit how many times a function can call other functions. Usually this limit may be reached with functions calling themselves. If this stack overflow situation is encountered, there may be a bug in the algorithm or then there are too many recursions for the system to handle.

Template strings

Template strings are string literals enclosed with backticks (`) that can contain embedded expressions defined using syntax ${expression}. The embedded expressions are evaluated in the same calculation context in which the template string is defined.

Notes on template strings:

  • If there is a need to use backticks in the template strings, escaping is done with a backslash (\`).
  • Template strings can contain line breaks.
  • Embedded expressions can also contain template strings.


let creditBlocksCount = 15;
`There are total of ${creditBlocksCount} credit blocks!`;
Returns: There are total of 15 credit blocks!

EventLogById(1).Cases.`Case ${Name} contains ${Count(Events)} events`
Returns a list of following strings for each case, e.g. Case 12345 contains 12 events

Returns: 4

Returns: 2

Models.`Id:${Id}, Name: ${Name}`
Returns: An array of strings containing model ids and names.

let m = [
  #{"Name":"Model 1", "Id": "1", "Description": "Big one"},
  #{"Name":"Model 2", "Id": "2", "Description": "This too"},
  #{"Name":"Foo Model", "Id": "3", "Description": "Not this"}
`<ul>${StringJoin("""", OrderBy(m, Name).`
    Model: ${Id == 2 ? `(default) ${Name}`: Name}
    Id: ${Id}
    Description: ${Description}

    Model: Foo Model
    Id: 3
    Description: Not this
    Model: Model 1
    Id: 1
    Description: Big one
    Model: (default) Model 2
    Id: 2
    Description: This too

Code comments

Single line comments can be added using // syntax. Line comment spans until the end of the line.

let var1 = 123; //This is comment which is ignored by the calculation

Multiline comments can be added with syntax starting with /* and ending to */.

Comment that spans to
multiple lines.

Expression Chaining using : keyword

Expressions can be chained together two ways:

  • Contextless chaining: When . keyword is used to chain expressions, the resulting objects will not have any context information.
  • Hierarchical chaining: When : keyword is used to chain expressions, only the result of the whole chained expression will consist of hierarchical arrays where all the values in the first expression (=context object) will be bound to the arrays those values generated. If the second expression does not return an array, the result will be changed to be an array.


Contextless chaining: First expression not an array, second expression not an array:
"1".("Number is " + _)
"Number is 1"

Contextless chaining: First expression is an array, second expression not an array:
[1,2,3].("Number is " + _)
["Number is 1", "Number is 2", "Number is 3"]

Contextless chaining: First expression is an array, second expression is an array:
[1,2,3].["Number is " + _, "" + _ + ". number"]
[ ["Number is 1", "1. number"], ["Number is 2", "2. number"], ["Number is 3", "3. number"] ]

Hierarchical chaining: First expression is an array, second expression is an array:
[1,2,3]:["Number is " + _, "" + _ + ". number"]
[ HierarchicalArray(1, ["Number is 1", "1. number"]), HierarchicalArray(2, ["Number is 2", "2. number"]), HierarchicalArray(3, ["Number is 3", "3. number"]) ]

  • Hierarchical arrays: Whenever traversing a relation in expression language using hierarchical chaining operator ':' for chaining expressions, a hierarchical array will be returned. It is an object which behaves just like a normal array except it stores also context/root/key/label object which usually represents the object from which the array originated from, for example the original case object when querying events of a case.
  • Hierarchical objects: Arrays where at least one object in the array is itself an array is considered to be a hierarchical object. Hierarchical arrays are treated in similar way as normal arrays in hierarchical objects.
  • Depth of a hierarchical object is the number of inner arrays that there are in the object, i.e. how deep is the hierarchy.
  • Level in hierarchical object consists of all the nodes that are at specific depth in object's array hierarchy. 0 is the level at the root of the object, consisting only of the object itself as single item. Levels increase when moving towards leaves.
  • Leaf level is a level that doesn't have any sub levels.
In the following example, the second parameter of the IsConformant function is a hierarchical objects used as key-value pair collection:
EventLog.Cases:IsConformant(myDesignModel, #{"IgnoreEventTypesMissingInModel": false, "IgnoreIncompleteCases": true})

Full and limited modes

The expression language can run in two modes: the full and limited mode. In the full mode, all functionality is available, while in the limited mode, operations that modify data or connect to external datasources are prevented. In dashboards, the limited mode is in use for security reasons, and in scripts the full mode is available. The following operations are prevented in the limited mode:

  • SendEmail()
  • CallWebService()
  • ImportOdbc()
  • ImportOdbcSecure()
  • Model.DeletePermanently()
  • Model.Restore()
  • Model.TriggerNotifications()
  • Project.DeletePermanently()
  • Project.Restore()
  • Project.CreateDatatable()
  • Datatable.AddColumn()
  • Datatable.DeletePermanently()
  • Datatable.Import()
  • Datatable.Merge()
  • Datatable.RemoveColumns()
  • Datatable.RenameColumns()
  • Datatable.Truncate()
  • RecycleBin.DeletePermanently()
  • Call SQL script

Calling expression scripts is allowed, but in the script all previously mentioned operations are prevented.

In-memory expression blocks in SQL expressions

It's possible to embed in-memory expressions into SQL expressions using #expr{} tags. The in-memory expressions are evaluated first and results placed into the SQL expression in place of the tag as an SQL literal value, and then the SQL expression is executed in the datasource. As the in-memory expression is run in the QPR ProcessAnalyzer server, it's not possible to use the in-memory expression to process data that is located in the datasource.

Following data types are supported in the replaced literals: string, integer, float, boolean and date. Also if the in-memory expression returns an SQL expression, it is placed as such to the contained SQL expression. Note that the #expr{} tags are only supported in the SQL expressions.


function GenerateExpression(increment) {
  return ToSqlExpression(#sql{Column("id") + #expr{increment}});
df.WithColumn("foo", #expr{GenerateExpression(1)} + #expr{GenerateExpression(2)} + 1).Collect()

Will add a column for dataframe having value equal to SqlExpression: (Column("id") + 1) + (Column("id") + 2) + 1