QPR Reporting Expression

QPR Reporting Expression language extend QPR Suite Web Service queries by enabling for example calculation, grouping and joining logic, and also lot more helpful functions. For more information, see QPR Reporting Expressions documentation.

To run QPR Reporting Expressions, you need to install QPR Suite Reporting Add-On (freely available for QPR customers). To install, follow the installation instructions.

QPR Reporting Expressions Syntax

Syntax of QPR Reporting Expressions consists of a list of named expressions in the following format:

name1=expression1
name2=expression2
name3=expression3

Each expression is in a separate line. Number of expressions is not restricted. Individual expressions follow the syntax defined in the above linked QPR Reporting Expression language documentation. It's not possible to use new line characters inside individual expressions.

Results of previous expressions can be used as parameters in the following expression, enabling to split complex expression into smaller parts. For example, "name1" can be used in expressions "name2" and "name3", and "name2" can be used in expression "name3".

It's the result of the last expression, that is returned when executing the query (in the above example, expression "name3"). Other intermediate results ("name1" and "name2" in the example) are ignored.

QPR Reporting Expressions always return tabular data that is compatible with QPR UI datasets. Data types string, integer, double, boolean and datetime are supported in tabular result cells, when QPR UI converts QPR Reporting Expressions result into a QPR UI dataset. Other datatypes are ignored and they are replaced by null values.

Examples

The following query counts how many scorecards there are in each QPR Metrics model:

scorecards=From('[SC].models.scorecard', '', '', 'model.name(as="modelname"),id')
countSums=SortBy(GroupBy([scorecards], Array('modelname'), Array('numberOfScorecards'), Array('ArraySize([id])')), 'numberOfScorecards DESC')
sortedResult=SortBy([countSums], 'numberOfScorecards DESC')

The following query will return 20 process element instances which have the greatest width in the flowchart. Note that running the following query may take time, if there are lot of process models and elements inside them.

instances=From('[PG].models.subobjects.instances', '', '', 'name,id', '')
getWidth=AddColumn(AddColumn([instances], 'elementInstanceId', 'InstanceIdFromFullId([id])'), 'elementWidth', 'ConvertToDouble(ItemAt(SubAttributesAsArray([id], \'graphicalproperties\', \'width\', \'[instanceid]=[elementInstanceId]\'), 0))')
sortedData=SortBy([getWidth], 'elementWidth desc')
getMax20=Where(AddColumn([sortedData], 'rowordernumber', '[rowordernumber]'), '[rowordernumber] < 20')

The following query is an example of joining two datasets. It returns a line for each user-group assignment.

users=From('[UM].users', '', '', 'name(as="username"),fullname,email,id(as="userid"),ingroups', '')
groups=From('[UM].groups', '', '', 'name(as="groupname"),id(as="groupid")', '')
joined=RemoveColumns(LeftJoin([users], [groups], 'Contains([ingroups], [groupid])'), Array('ingroups'))

The following query returns a random number between 0 and 100 in a dataset which contains one row and one column which name is "data":

randomNumber=AddDatasetRow(CreateDataset(Array('data')), Floor(Random() * 101))

The following query returns the current local day and time formatted in the way defined in the query. Dates can also be queried without explicit formatting, and then they are formatted using web browser local settings.

currentDateTime=AddDatasetRow(CreateDataset(Array('data')), DateToString(CurrentDateTime(), true, 'dd.MM.yyyy HH:mm:ss'))