Difference between revisions of "QPR Reporting Expression Language"

From Mea Wiki
Jump to navigation Jump to search
Line 10: Line 10:
 
* Unary: '''!''', '''not''', '''-''', '''~''' (bitwise not)
 
* Unary: '''!''', '''not''', '''-''', '''~''' (bitwise not)
 
* functions: '''Abs(1)''', '''doSomething(1, 'dummy')'''
 
* functions: '''Abs(1)''', '''doSomething(1, 'dummy')'''
−
 
  
 
The expression language supports .Net 4.5 datatypes. Following table contains examples, how to write literals of some basic datatypes.
 
The expression language supports .Net 4.5 datatypes. Following table contains examples, how to write literals of some basic datatypes.

Revision as of 11:14, 3 April 2018

Introduction to QPR Reporting Expression Language

This chapter describes the expression language used by QPR Suite Reporting Add-on and several QPR Suite accelerators. Expressions (formulas) enable to define values dynamically, meaning that values can be calculated based on the information that is available only at runtime. The expression evaluation is case sensitive, which applies e.g. for parameter and function names. Actually all function names are not case sensitive, but it’s easiest to consider they are. Expressions can be combined using operators. Expression's priority order starting from the greatest priority is: primary, unary, power, multiplicative, additive, relational, logical and, logical or. Following operators can be used:

  • Logical or: or, || (these are equivalent)
  • Logical and: and, && (these are equivalent)
  • Relational: =, !=, <>, <, <=, >, >= (!= and <> are equivalent)
  • Basic calculations: +, -, *, /, %
  • Bitwise: & (bitwise and), | (bitwise or), ^ (bitwise xor), << (left shift), >> (right shift)
  • Unary: !, not, -, ~ (bitwise not)
  • functions: Abs(1), doSomething(1, 'dummy')

The expression language supports .Net 4.5 datatypes. Following table contains examples, how to write literals of some basic datatypes.

Data type Example
integer 123456
double 123.456, 0.123
boolean true, false
string 'Hello world!'

In string literals, characters \ and ' need to be written as \\ and \' (i.e. escaped), e.g. string ab\cd'ef is written as 'ab\\cd\'ef'.

When operating with dates, note that all dates returned by QPR Web Services are strings formatted in XML date format (yyyy-MM-ddTHH:mm:ss.fffzzz). They can be converted to datetimes using StringToDate function.

In the expressions unicode characters can be defined using syntax \uxxxx. For example, new line is \u000D\u000A (carriage return and line feed characters used in Windows systems).

Arithmetic Functions

Function Description Example expression Result of the example
Abs (decimal) Returns the absolute value of a specified number. Abs(-1) 1 (decimal)
Acos (decimal) Returns the angle whose cosine is the specified number. Acos(1) 0 (decimal)
Asin (decimal) Returns the angle whose sine is the specified number. Asin(0) 0 (decimal)
Atan (decimal) Returns the angle whose tangent is the specified number. Atan(0) 0 (decimal)
Ceiling (decimal) Returns the smallest integer greater than or equal to the specified number. Ceiling(1.5) 2 (decimal)
Cos (decimal) Returns the cosine of the specified angle. Cos(0) 1 (decimal)
Exp (decimal) Returns e raised to the specified power. Exp(0) 1 (decimal)
Floor (decimal) Returns the largest integer less than or equal to the specified number. Floor(1.5) 1 (decimal)
IEEERemainder (decimal) Returns the remainder resulting from the division of a specified number by another specified number. IEEERemainder(3, 2) -1 (decimal)
Log (decimal) Returns the logarithm of a specified number. Log(1, 10) 0 (decimal)
Log10 (decimal) Returns the base 10 logarithm of a specified number. Log10(1) 0 (decimal)
Max (decimal) Returns the larger of two specified numbers. Max(1, 2) 2 (decimal)
Min (decimal) Returns the smaller of two numbers. Min(1, 2) 1 (decimal)
Pow (decimal) Returns a specified number raised to the specified power. Pow(3, 2) 9 (decimal)
Random (decimal) Returns a random value between 0 and 1 (the value can be 0 but cannot be 1). Random() 0.358024762 (decimal)
Round (decimal) Rounds a value to the nearest integer or specified number of decimal places. Round(3.222, 2) 3.22 (decimal)
Sign (decimal) Returns a value indicating the sign of a number. Sign(-10) -1 (integer)
Sin (decimal) Returns the sine of the specified angle. Sin(0) 0 (decimal)
Sqrt (decimal) Returns the square root of a specified number. Sqrt(4) 2 (decimal)
Tan (decimal) Returns the tangent of the specified angle. Tan(0) 0 (decimal)
Truncate (decimal) Calculates the integral part of a number. Truncate(1.7) 1 (decimal)

String Functions

Function Parameters Description
CharAt (string)
  • input string
  • index number (int)
 Return character of the index number position in the input string. The indexing starts from zero.
Contains (boolean)
  • first string
  • second string
 Return true if the first string contains the second string; otherwise false.
CropText (string array)
  • input string
  • min characters (int)
  • max characters (int)
  • crop characters (string array)
 Crops a text into several parts. Length of the parts is between provided minimum and maximum characters. Text is cropped in positions where the crop characters appear (crop characters are excluded from the result).

If no crop characters are found, text is cropped from the max position.

EndsWith (boolean)
  • first string
  • second string
 Return true if the first string ends with the second string; otherwise false.
IndexOf (int)
  • first string
  • second string
  • start index (int)
 Return the index number of the first occurrence of the second string in the first string. Indexing starts from zero. If the start index is provided, the search is started from that index.
LastIndexOf (int)
  • first string
  • second string
  • index (int)
 Return the index number of the last occurrence of the second string in the first string. Indexing starts from zero. If the start index is provided, the search is started from that index (calculated from the start of the string) towards the beginning of the string.
Length (int)
  • input string
 Return the number of characters in the input string.
RemoveIllegalChars (string)
  • input string
  • allowed characters (string)
  • replace string
 Removes from the input string all characters that are not part of the listed chars. If the replace string is provided, removed characters are replaced with that one. Examples:

RemoveIllegalChars('ABCDE', 'BD', nullvalue(), ") gives 'BD'

RemoveIllegalChars('ABCDE', 'BD', nullvalue(), ' ') gives ' B D '

Replace (string)
  • first string
  • second string
  • third string
 Replaces all occurrencies of the second string with the third string in the first string. Example:

Replace('abcd', 'b', 'e') gives aecd.

StartsWith (boolean)
  • first string
  • second string
 Return true if the first string starts with the second string; otherwise false.
Substring (string)
  • input string
  • start index (int)
  • length (int)
 Returns a substring of the input string starting from the start index. If the length is provided, the returned string contains maximum of that number of characters.
ToLower (string)
  • input string
 Return a string where all the characters of the input string have been converted to lower case characters.
ToUpper (string)
  • input string
 Return a string where all the characters in the input string have been converted to upper case characters.
Trim (string)
  • input string
  • string array
 Removes spaces from the start and end of the input string (if string array is not provided). If the string array is provided, the string in the array are removed from the input array.

Datetime Functions

Function Parameters Description
AddMilliseconds (datetime)
  • datetime
  • milliseconds (double)
 Adds the specified number of milliseconds to a datetime.
AddMonths (datetime)
  • datetime
  • number of months (int)
 Adds the specified number of months to a datetime. Negative number of months means number of months to subtract. This function takes into account years, so e.g. when adding one month to a date in December, the result is January in the next year.
AddTimespan (datetime)
  • datetime
  • timespan
 Adds the specified timespan to a datetime.
CompareDates (int)
  • datetime 1
  • datetime 2
 Compares two datetimes and returns zero when the datetimes equals, greated than zero if the former is later, and less than zero if the former is earlier.
CurrentDateTime (datetime) [none] Returns a datetime representing the current date and time as UTC. As there are no parameters, use syntax CurrentDateTime()
FromMetricsDateFormat (datetime)
  • numerical value (string, double, int)
 Returns a date that this the provided number of days from 1.1.1900 00:00:00 UTC. QPR Metrics date values uses this date format.
GenerateTimeStampArray (datetime array)
  • start timestamp (datetime)
  • end timestamp (datetime)
  • generating type (string)
  • increment (timespan)
 Generates an array of timestamps between the start and end timestamps. The first generated timestamp is earlier or equal than the start timestamp, and the last generated timestamp is later or equal than the end timestamp.

Available generating types:

- year
- halfyear
- third
- quarter
- month
- custom

If type is "custom", the fourth parameter is provided, which is the increment timespan (resulting equally spaced timestamps). Note that e.g. day, week, hour are equally spaced timestamps, so they can be generated using "custom" type.

GetDate (int)
  • datetime
 See (1) below (property: Date)
GetDay (int)
  • datetime
 See (1) below (property: Day)
GetMonth (int)
  • datetime
 See (1) below (property: Month)
GetTicks (int)
  • datetime
 See (1) below (property: Ticks)
GetYear (int)
  • datetime
 See (1) below (property: Year)
NewDatetime (datetime)
  • year (int)
  • month (int)
  • day (int)
  • hour (int)
  • minute (int)
  • second (int)
  • millisecond (int)
  • local time (boolean)
 Returns a new datetime with the following parameter values.
- year: 1 - 9999
- month: 1 - 12
- day: The day 1 through the number of days in month
- hour: 0 - 23 (default: 0)
- minute: 0 - 59 (default: 0)
- second: 0 - 59 (default: 0)
- millisecond: 0 - 999 (default: 0)
- local time (default: true)

First two parameters are mandatory.

SubtractTimespan (datetime)
  • datetime
  • timespan
 Substracts the specified timespan from the datetime.
Timespan (datetime)
  • days (int)
  • hours (int)
  • minutes (int)
  • seconds (int)
  • milliseconds (int)
 Creates a new timespan. A timespan represent an interval between two datetimes.
ToMetricsDateFormat (double)
  • datetime
 Returns number of days between 1.1.1900 00:00 UTC and the provided datetime. QPR Metrics handles date values using this format. Also the function is needed for criteria in Web Service’s QueryObjects, e.g.

'createddate>' + ToMetricsDateFormat(CurrentDateTime())

(1) DateTime properties in .Net Framework 4.5: https://msdn.microsoft.com/en-us/library/system.datetime_properties(v=vs.110).aspx

Array Functions

An array is a list of objects of any type, such as strings or integers. Arrays may contain same valued items multiple times.

Function Parameters Description
ArrayExcept (array)
  • array 1
  • array 2
 Returns an array which contains items that are in the array 1 but not in the array 2.
ArrayIntersect (array)
  • array 1
  • array 2
 Returns an array which contains items that are in both provided arrays.
ArrayReverse (array)
  • array
 Reverses an array, i.e. first element becomes last and so on.
ArraySize (int)
  • array
 Calculates number of items in an array.
ArraySort (array)
  • array to sort
  • sort order (array)
 Sorts an array provided as a first parameter. The second parameter provides an array that contains an explicit order for the items to be used in sorting. The rest of the items that are not listed, are sorted normally and placed in the end of the outputed array.
ArrayUnion (array)
  • array 1
  • array 2
 Joins two arrays. Duplicates are not removed. If only one parameter is provided, that parameter is expected to contain an array, item of which are arrays. In that case all the items of all arrays are joined.
ArrayDistinct (array)
  • array
 Returns an array where duplicate values are removed. Order of items is preserved.
ArrayWhere (array)
  • array
  • expression (string)
 Filters out all items in an array, where the expression is evaluated as false. The expression must return boolean value. Available arguments in the expression:
- item (item in the array)
- index (iteration order number starting from 0)
Average (double)
  • array
 Calculates an average of array items. Array must contain numerical data.
Concatenate (string)
  • array
  • separator (string)
 Concatenates items of an array into a string separated by a provided separator. Array must contain items that can be converted into string.
IndexOfAnyInArray (int)
  • array 1
  • array 2
 Returns an index number of the any of objects of the second array in the first array. The second array objects are searched from left to right. First object array starting from index 0. If the first array doesn’t contain any of objects in the second array, -1 is returned.
IndexOfInArray (int)
  • array
  • item to search (object)
 Returns an index number of an object in the array. First object array starting from index 0. If object is not found, -1 is returned.
ItemAt (object)
  • array
  • int
 Returns an item from an array in the provided index number. The first item has an index number zero (0).
Median (double)
  • array
 Returns the median of the provided array.
Minimum (double)
  • array
 Returns the smallest number in the provided array.
Maximum (double)
  • array
 Returns the largest number in the provided array.
OnlyValue (object)
  • array
 Returns the only value of the array. Throws an exception if the array contains more than one value. Return null if the array contains no values.
Sum (double)
  • array
 Calculates a sum of array items. Array must contain numerical data.
Transform (array)
  • array
  • expression (string)
 Transforms every item of an array to another array using the provided expression. The expression has following arguments:
- value which gets the value of the item.
- index: iteration order number starting from 0

E.g. transform([inputarray], 'substring([value], 5)') returns first five characters from every string of the array.

Dictionary Functions

Function Parameters Description
Dictionary (dictionary)
  • keys (keys array)
  • values (object array)
 Creates a new dictionary based on two arrays. The two arrays must not be null, and must be the same length.
DictionaryContainsKey (boolean)
  • dictionary
  • key (string)
 Returns true if the dictionary contains the provided key; otherwise the function returns false.
GetDictionaryValue (object)
  • dictionary
  • key (string)
  • default value (object)
 Returns a value from a dictionary based on key. If the key is not found and a default value is defined, the default value is returned. If the key is not found and a default value is not defined, an exception is thrown.
ContainsValue (boolean)
  • dictionary
  • key (string)
 Returns true if the provided dictionary contains the provided value; otherwise false.

Conversion Functions

Function Parameters Description
Array (array)
  • object 1
  • object 2
  • ...
 Converts provided objects into an array. This is needed for example in functions which need their parameters as arrays.
ArrayToDataset (dataset)
  • array
  • column name (string)
 Converts an array to a dataset. The dataset has one column which name is given as a second parameter.
ByteArrayToString (string)
  • input byte array
  • encoding (string)
 Converts byte array into string using the provided encoding. Supported encodings depend on the system. Possible encodings are unicode, utf-8, utf-7, utf-32, iso8859-1, ascii. If null is provided, null is returned. If zero valued array is provided, an empty string is returned.
ConvertToBoolean (boolean)
  • input value (object)
  • value in error (boolean)
 Converts a value to a boolean. The second parameter is optional and it is returned if the conversion fails. If it is not defined and the conversion fails, an error is thrown.
ConvertToDouble (double)
  • input value (object)
  • value in error (double)
 Converts a value to a double. The second parameter is optional, and it’s returned if the conversion fails. If it’s not defined and the conversion fails, an error is thrown.
ConvertToInt (int)
  • input value (string)
  • value in error (int)
 Converts a value to an integer. The second parameter is optional, and it’s returned if the conversion fails. If it’s not defined and the conversion fails, an error is thrown.
ConvertToString (string)
  • input value (object)
  • date format (string)
  • value in error(string)
 Converts a variable of many datatypes to string as follows:
- null: empty
- DateTime: formatted using provided or XML date format
- Boolean: "true" or "false"
- Array: items comma separated enclosed with []

The second parameter is optional. The third parameter is optional, and it’s returned if the conversion fails. If it’s not defined and the conversion fails, an error is thrown.

DatasetColumnToArray (array)
  • dataset
  • column name (string)
 Converts a column of a dataset to an array. The column is referenced using the column name.
DatasetRowToArray (array)
  • dataset
  • row number (int)
 Converts a row of a dataset to an array. The row is referenced using the row number (the first is 1).
DateToString (string)
  • date to convert (datetime)
  • local time (boolean)
  • dateformat (string)
 Converts a datetime variable to a string. The local time parameter is optional; if omitted, the string is assumed to represent local time. If the third parameter is omitted, the string is interpreted as XML schema date format. More information of how to construct the dateformat:

https://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx

HtmlEncode (string)
  • string
 Performs an HTML encoding. More information:

https://msdn.microsoft.com/en-us/library/ee388364(v=vs.110).aspx

InitializeScriptEngine (ScriptEngine)
  • script sources (string or string array)
 Initializes a script engine and returns a script engine object. The script engine object can be used to run script functions (see RunScript function). A single source can be provided as a string or several sources as a string array.
InstanceIdFromFullId (string)
  • element full id (string)
 Extracts an instance id from a full id. The function returns an empty string, if the instance id cannot be extracted.

Example: InstanceIdFromFullId('SC.6346904. 5290187.4353690') returns 4353690

ModelIdFromFullId (string)
  • element full id (string)
 Extracts a model id from a full id. The function returns an empty string, if the model id cannot be extracted.

Example: ModelIdFromFullId('SC.6346904.5290187') returns 6346904

NumberToString (string)
  • value (int, double)
  • format (string)
  • value in error (string)
 Converts a numerical value to string. This function can be used when there are special requirements related to e.g. thousand separators. Behaviour of this function depends on the server’s locale settings. If the conversion fails and the value in error is defined, that value is returned. Otherwise an error is thrown. More information about formattings:

https://msdn.microsoft.com/en-us/library/dwhawy9k(v=vs.110).aspx#NFormatString https://msdn.microsoft.com/en-us/library/0c899ak8(v=vs.110).aspx

ObjectIdFromFullId (string)
  • element full id (string)
 Extracts an object id from a full id. The function returns an empty string, if the object id cannot be extracted.

Example: ObjectIdFromFullId('SC.6346904.5290187') returns 5290187

PresentObjectAsString
  • input object
  • date format (string)
  • value in error (object)
 Presents variables of many datatypes as string for debugging purposes as follows:
- null: "[null]"
- DateTime: formatted using provided or XML date format
- String: enclosed using quotes
- Boolean: "true" or "false"
- DataSet: {{}}
- Array: items comma separated enclosed with []

The second and third parameters are optional.

RunScript (object)
  • script engine
  • function name (string)
  • function parameters (object)
  • ...
 Runs a script engine function. A script engine used to run the script needs to be provided (use function InitializeScriptEngine). Function name is provided as a second parameter. Rest of the parameters are parameters for the function.
StringToArray (array)
  • string to convert
  • separator (string)
  • remove empties (bool)
 Converts a string to an array using provided separator string. If remove empties is true, there are no empty strings in the output array.
StringToDataFile
  • input string
  • encoding (string)
 Converts a string into a byte array using the provided encoding. Supported encodings depend on the system. Possible encodings are unicode, utf-8, utf-7, utf-32, iso8859-1, ascii.
StringToDate (datetime)
  • string to convert
  • local time (boolean)
  • dateformat (string)
  • alternative dateformat (string)
  • ...
 Converts a string variable to a datetime. The local time parameter is optional; if omitted, the string is assumed to represent local time. If the third parameter is omitted, the string is interpreted as XML schema date format.

There can also be alternative date formats provided starting from the 4. parameter in case that date conversion fails. All the provided date formats are tested and an exception is thrown only if none of them could be used for the formatting. More information of how to construct the dateformat: https://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx

ToBase64 (string)
  • byte array
 Converts a byte array (data file) into a base64 encoded string. More information: https://en.wikipedia.org/wiki/Base64
UrlEncode (string)
  • string
 Performs a URL encoding. Corresponds to JavaScript encodeURIComponent() function. More information: https://msdn.microsoft.com/en-us/library/system.net.webutility.urlencode(v=vs.110).aspx

QPR Web Services Functions

Function Parameters Description
BelongsToGroup (boolean)
  • group name (string)
 Returns true if the current user belongs to the provided group; otherwise false. QPR Web Services query is made to get the information. The current user is the user account which is logged to the QPR Web Services when the query is made. False is returned if the provided group doesn’t exist. Upper and lower case characters need to be written correctly.

Example: BelongsToGroup("Administrators")

CurrentUser (string)
  • attribute name (string)
 Returns any attribute of the current user object. If no attribute is provided, user id is returned (attribute "id").

Example: CurrentUser()

GetAttribute (string)
  • objectid (string)
  • attribute (string)
  • options (string)
 QPR Web Service’s GetAttributeAsString operation, see http://kb.qpr.com/qpr2017-1/index.html?getattributeasstring.htm.
GetAttributes (string array)
  • objectid (string)
  • attributes (string)
  • options (string)
 Returns multiple attributes of a single object as an array. Attributes are defined as comma separated. Based on QPR Web Service’s QueryObjects operation.
GetPortalUrl (string)
  • objectid (string)
  • view (string)
  • options (string)
 QPR Web Service’s GetPortalUrl operation, see http://kb.qpr.com/qpr2017-1/index.html?getportalurl.htm.
LatestValuePeriod (string)
  • objectid (string)
  • series symbol or series id (string)
 Returns the id of the period that contains the latest (newest) measure value. Return an empty string, if no period contains a value. This information can be used e.g. in web service attribute prettyvalue(period=' + [periodid] + ')
QueryObjects (string array)
  • query (string)
  • filter (string)
  • sortby (string)
  • attributes (string)
  • options (string)
 Executes QPR Web Service’s QueryObjects and returns results as a string array. Only one attribute should be defined for "attributes" parameter.
QueryObjectsAverage (double)
  • query (string)
  • filter (string)
  • attributes (string)
  • options (string)
 Average of queried objects numerical attributes. Only one attribute is defined in "attributes". Error is thrown if attribute values are not numerical.
QueryObjectsConcatenate (string)
  • query (string)
  • filter (string)
  • sortby (string)
  • attributes (string)
  • options (string)
  • separator (string)
 Concatenates attribute values of all queried objects using defined separator. Only one attribute is defined in "attributes".
QueryObjectsCount (int)
  • query (string)
  • filter (string)
  • options (string)
 Returns number of objects returned by QueryObjects. "sortby" and "attributes" cannot be defined as they don’t affect the result.
QueryObjectsFirstAttribute (string)
  • query (string)
  • filter (string)
  • sortby (string)
  • attributes (string)
  • options (string)
 Attribute value of the first object of queried objects. Only one attribute is defined in "attributes".
QueryObjectsSum (double)
  • query (string)
  • filter (string)
  • attributes (string)
  • options (string)
 Sum of queried objects of numerical attributes. Only one attribute is defined in "attributes". Error is thrown if attribute values are not numerical.
QueryObjectsUnique (string)
  • query (string)
  • filter (string)
  • sortby (string)
  • attributes (string)
  • options (string)
  • separator (string)
 Concatenates unique attribute values of all queried objects using defined separator. Only one attribute is defined in "attributes".
SubAttributesAsArray (array)
  • objectid (string)
  • attribute name (string)
  • subattribute name (string)
  • filter expression (string)
 Returns attribute values (as an array) which are presented in more complex structures. This type of attributes are e.g. graphicalproperties, customattributetypes and properties. The filter expression is for selecting desired rows. In the filter expression there are following arguments:
- all sub tag attributes (with their tag names)
- argument ordernumber having values 0, 1, … for xml tag order number.

Example, following graphicalproperties attribute: 

 <graphicalproperties>
 <symbol id="920256947" x="790" y="190" width="400" height="30" instanceid="2029031131" />
 <symbol id="2009602777" x="230" y="640" width="200" height="40" instanceid="0" />
 <symbol id="368985118" x="730" y="40" width="100" height="60" instanceid="369716419" />
 </graphicalproperties>

Calling: SubAttributesAsArray([instanceid], 'graphicalproperties', 'width', '[instanceid]=0') returns 200.

SubAttributesAsDataset (dataset)
  • objectid (string)
  • attribute name (string)
  • columns (array)
 Returns attributes which are presented in more complex structures as a dataset. This type of attributes are e.g. graphicalproperties, customattributetypes, properties and hotspots. Dataset columns (names of the sub attributes) are listed as an array (in lower case).

Dataset Functions

Dataset is a flat table like object containing multiple named columns and multiple rows. Dataset can contain zero rows, but at least one column must exist. Datasets may contain any type of data. Dataset is like a table in relational database or a worksheet in Excel. Usually dataset functions don’t change the input dataset, but create new dataset (exceptions are mentioned). Thus the input datasets may also be used in other functions.

Function Parameters Description
AddColumn (dataset)
  • dataset
  • columnName (string)
  • expression (string)
  • compare mode (boolean)
 Adds a new column to the dataset based on an expression. All other column values are available as arguments in the expression. Also there is an argument rowordernumber, which is the row order number starting from 0.

In the compare mode (true), there are also values of the previous and next rows available as arguments with suffixes _previous, _current and _next. For example if there are columns column1 and column2, and adding a new column column3, there are arguments column1_previous, column2_previous, column3_previous , column1_ current, column2_current, column1_next and column2_next.

AddDatasetRow (dataset)
  • Dataset to add row
  • column 1 value (object)
  • column 2 value (object)
  • ...
 Adds a new row to a dataset with the provided values. A row is added to the provided dataset, i.e. no new dataset is created.
BuildHierarchy (dataset)
  • dataset
  • instance id column name (string)
  • object id column name (string)
  • parent object id column name (string)
  • hierarchy id column name (string)
  • hierarchy parent id column name (string)
  • level column name (string)
  • sorting data column name (string)
  • filtering formula (string)
 Builds an element hierarchy based on element instances provided as a dataset, where there are following columns: instance id, object id and parent object id. In QPR Suite objects, the object id can be derived from instance id (see example).

The function constructs a new dataset where rows are in the hierarchy order. The function can handle situations where also other than the bottom level objects have been instantiated; it means that the result hierarchy will contain same instances multiple times. Following new columns are added:

  • hierarchy id (explained below)
  • hierarchy parent id (referring to the hierarchy id)
  • level (top level is 0, the level directly below top is 1, ...)

The hierarchy id is an id that is unique in the whole hierarchy. To enable this, following technique is used: If there is an instance id PG.x.y.z which appears multiple times, the first hierarchy id is PG.x.y.z, the second is PG.x.y.z.1, the third is PG.x.y.z.2, and so on.

When the sorting column is provided, the result data is sorted using data in that column. The hierarchy is still preserved, so only the rows in the same hierarchy level are sorted.

Example: 

BuildHierarchy(AddColumn(From('[PG.1836683727].attributeasfunction(attributeasfunction="mainlevelid").ChildObjects(hierarchy="processlevels",recursive=1)', '', '', 'id(as="instanceid"),parentobjects,name(as="diagramname")', ''), 'objectid', '\'PG.\' + ModelIdFromFullId([instanceid]) + \'.\' + ObjectIdFromFullId([instanceid])'), 'instanceid', 'objectid', 'parentobjects', 'hierarchyid', 'hierarchyparentid', 'level', 'diagramname')
CreateDataset (dataset)
  • column names (string array)
 Creates a new dataset with provided column names. The created dataset contains no rows.
DatasetCell (object)
  • dataset
  • column name (string)
  • row number (int)
 Gets an item from a dataset from the named column and index (first row is number 1). Returned type of data is the type of the cell.
DatasetRows (dataset)
  • input dataset
  • start row (int)
  • rows count (int)
 Returns a new dataset containing the defined rows (based on start row and rows count). Index is zero based.
DatasetSize (int)
  • dataset
 Number of rows in a dataset. If dataset is null, -1 is returned.
Distinct (dataset)
  • dataset
 Removes duplicate rows, i.e. rows that contains identical data.
Except (dataset)
  • dataset1
  • dataset2
 Result dataset contains rows that are in the first dataset but not in the second dataset. The datasets must have same columns.
From (dataset)
  • query (string)
  • filter (string)
  • sortby (string)
  • attributes (string)
  • options (string)
 Returns QPR Web Service’s QueryObjects result in a dataset as string values. Columns get their names from the result data. Column names can be changed with QueryObjects syntax attribute(as="ColumnName").
FromWithTypes (dataset)
  • query (string)
  • filter (string)
  • sortby (string)
  • attributes (string)
  • options (string)
 Same as From function but data is fetched to the dataset using datatypes that QPR Web Services provides. Available datatypes are string, datetime and double.
FullOuterJoin (dataset)
  • left dataset
  • right dataset
  • matching expression (string)
 Full outer join of two datasets. The expression has all column names as arguments. The datasets cannot have columns with the same names.
GroupBy (dataset)
  • dataset
  • list of grouped columns (string array)
  • list of combined column names (string array)
  • list of combine expressions (string array)
 Groups the dataset. Parameters:
  • 1. parameter is the dataset to group
  • 2. parameter is an array of columns to group
  • 3. parameter is an array of combined column names
  • 4. parameter is combine expression (e.g. count, sum, average or number of rows). All expressions have as arguments an array of objects to combine. Parameter names equal to column names.

Example: GroupBy([dataset1], Array('modelname', 'typename'), Array('count', 'objectnames'), Array('ArraySize([name])', 'Concatenate([name])'))

InnerJoin (dataset)
  • left dataset
  • right dataset
  • matching expression (string)
 Inner join of two datasets. The expression has columns names as available arguments. The datasets cannot have columns with same names. Efficiency is n2.
InnerJoinEquals (dataset)
  • left dataset
  • right dataset
  • left column name (string)
  • right column name (string)
 Inner join of two datasets where the matching expression is left column equals right column. Efficiency is n.
Intersect (dataset)
  • dataset1
  • dataset2
 Intersect of two datasets. Result dataset contains only those rows that are in both datasets. The datasets must have same columns.
JsonToDataset (dataset)
  • json object (string)
 Convert a JSON object into a string.
LeftJoin (dataset)
  • left dataset
  • right dataset
  • matching expression (string)
 Left join of two datasets. Expression have columns names as available arguments. The dataset cannot have columns with same names.
Lookup (object)
  • dataset
  • search column (string)
  • search value (object)
  • return column name (string)
  • error if not found (boolean)
 Makes a lookup to a dataset and returns the value of the return column for the first matching row.
Matrix (dataset)
  • dataset
  • row expression (string)
  • column expression (string)
  • value expression (string)
  • grouping expression (string)
  • row column name (string)
 Builds a matrix based on the provided dataset. Row and column expressions are executed for each row of the dataset, and the data is positioned to rows and columns in the matrix based on the row and column expression values.

The value for the input dataset row is determined by the value expression. The grouping expression has an input argument "value", containing an array of all calculated value expressions for the matrix cell. Row column name defines the column containing matrix row names. Columns are sorted as an alphabetical order (except the matrix row name is the first).

MetricsMeasureValue (object)
  • scorecard id (string)
  • measure symbol (string)
  • attribute name (string)
  • series symbol (string)
  • period name (string)
  • throw error if not found (boolean)
 Returns Metrics measure value based on following information: scorecard id, measure symbol, series symbol and period name. This can be used when scorecards are queried and there is a need to get data from different measures.

If set to throw errors, an error is thrown if scorecard is not found, measure is not found or value is not found.

MetricsMeasureValues (dataset)
  • element id’s (string array)
  • series symbols (string array)
  • value attribute names (string array)
  • value column names (string array)
 Returns Metrics measure values as a dataset from multiple periods (dataset rows) and multiple series or measures (dataset columns). Each period id will have a separate row, i.e. data from different measures are combines in a same row if the measures have a same period level.

Element id’s, series symbols and value column names in the result dataset are provided as separate arrays. If element id's, series symbols and value attribute names arrays have less items than in the value column names, the last item is used as a rest of the items. The result dataset will have columns, periodid, periodname, startdate and enddate. Valid value attribute names are value and prettyvalue (these are from QPR Web Service’s values attribute). Data in result dataset are strings, and in case "value" attribute is used, they can be converted to numeric.

Example: MetricsMeasureValues(Array('SC.1938773693.159'), Array('ACT_AVG'), Array('value', 'prettyvalue'), Array('valueColumn', 'prettyvalueColumn'))

RemoveColumns (dataset)
  • dataset
  • columnNames (string array)
 Removes the specified columns from a dataset. Example:

RemoveColumns([dataset1], Array('name'))

RightJoin (dataset)
  • left dataset
  • right dataset
  • matching expression (string)
 Right join of two datasets. Expression have columns names as available arguments. The dataset cannot have columns with same names.
SearchDataset (dataset)
  • input dataset
  • search string
  • date format (string)
 Searches a string in a dataset and returns only matched rows. Date format is needed to convert dataset to string for the searching.
SortBy (dataset)
  • dataset
  • sorting definition (string)
 Sorts the dataset. Sorting is defined as comma separating the sorted columns. Optionally asc (default) or desc can be added after the column name. E.g. attribute1 asc, attribute2 desc
Split (dataset)
  • dataset
  • split column name (string)
  • split expression (string)
 Splits every row of a dataset into multiple rows based on the split expression. The split expression must return an array containing splitted items for a single row. Thus, the number of items in the array determines, to how many rows a single row is splitted. The splitted items are stored in a new column.

The split expression has all dataset column values as arguments. Note that the splitting may also result to a single row or even zero rows, if the split expression returns an array of one or zero items.

Example: Split([dataset], 'splitted', 'StringToArray([column_1], \',\')')

Transpose (dataset)
  • dataset
  • column name for headers (string)
 Transposes a dataset, i.e. changes its rows to columns and columns to rows.

If column name for headers is not provided, in the transposed dataset first column name is headers and it contains the header names of the original dataset. Rest of the header names are column_1, columns_2, … If column name for headers is provided, data in that column is used as headers for the new dataset (instead of generated headers described above).

Union (dataset)
  • dataset1
  • dataset2
 Union of two datasets. Results dataset contains all rows of the input datasets. The datasets must have same columns.
UnionDatasetArray (dataset)
  • input dataset (dataset array)
 Unions multiple datasets provided in an array.
Where (dataset)
  • dataset
  • expression (string)
 Filters out all rows in the dataset where expression is evaluated as false. The expression has all columns as arguments. Also, there is an argument rowordernumber, which is the row order number starting from 0.

XML Functions

Function Parameters Description
SelectXMLAttribute (string)
  • XDocument or XElement
  • XPath expression (string)
  • namespace prefix (string)
  • namespace URI (string)
  • attribute name (string)
 Gets an XML attribute value from an XML element that is selected from an XML document using an XPath expression. An empty string is returned if no element matches or if the attribute is not found.
SelectXMLAttributes (string array)
  • XDocument or XElement
  • XPath expression (string)
  • namespace prefix (string)
  • namespace URI (string)
  • attribute name (string)
 Gets a list of XML attribute values from a list of XML elements that are selected from an XML document using an XPath expression. An empty set is returned if no element matches.
SelectXMLElement (XElement)
  • XDocument or XElement
  • XPath expression (string)
  • namespace prefix (string)
  • namespace URI (string)
 Selects an element from an XML document using XPath expression. The namespace definition is for the XPath expression. Null value is returned if no element matches.
SelectXMLElements (XElement array)
  • XDocument or XElement
  • XPath expression (string)
  • namespace prefix (string)
  • namespace URI (string)
 Selects an array of elements from an XML document using XPath expression. The namespace definition is for the XPath expression. An empty set is returned if no element matches.
XmlAttribute (string)
  • XElement
  • attribute name (string)
  • default value (string)
 Gets an XML attribute value from an XML element. Empty value (or optional default value) is returned if the attribute is not found.
XmlDocument (XDocument)
  • XML data (string)
  • XML schema (string)
  • custom error message (string)
 Constructs an XML document from a string and validates it. Type of the returned object is XDocument. The XML data is provided as the first parameter and an XML schema as a second parameter. If the XML document is not compatible with the schema (i.e. valid), an error is thrown.
XPathEncode (string)
  • string
 Encodes a string to be suitable for an XPath expression. The function adds quotes ("") if needed.

Binary Data Functions

File data functions are for getting file contents as a byte array from different sources. There are also functions for getting media types of files.

Function Parameters Description
HttpFileData (byte array)
  • url (string)
  • alternateUrl (string)
 Fetches a file as byte array through an http(s) using the provided url address. The address must point directly to the file resource. If no resource is found from url, an alternateUrl is tried.
HttpFileMediaType (string)
  • url (string)
  • alternateUrl (string)
 Media type of the file fetched using HttpFileData function with a same parameter. If no resource is found from url, an alternateUrl is tried.
LoadFileFromDisk (byte array)
  • location (string)
 Loads a file from the local disk drive as byte array. To take into account data security, avoid implementation where users are able to select the file to load. Users need to have read access to the file.
QprEmbeddedFileData (byte array)
  • object (string)
  • attribute (string)
  • options (string)
 Gets an embedded file from QPR system as byte array using QPR Web Service’s GetBinaryData operation with provided parameters. More information http://kb.qpr.com/qpr2017-1/index.html?getbinarydata.htm.
QprGraphData (byte array)
  • object (string)
  • options (string)
 Gets an image file as byte array using QPR Web Service’s GetGraph operation with provided parameters.
QprGraphDimensions
  • object (string)
  • options (string)
 Returns width and height of the bitmap image that is returned by QPR Web Services GetGraph method. Width and height is returned as comma separated in the following format: width,height

For example: 700,300

QprGraphMediaType (string)
  • object (string)
  • options (string)
 Media type of the file fetched using qprEmbeddedFileData function with same parameters.
WebpageAsImage (byte array)
  • pageUrl (string)
  • width (int)
  • height (int)
  • zoom (float)
  • renderingdelay (int)
  • timeout (int)
 More information in chapter WebpageAsImage Function.

Other Functions

Function Parameters Description
AggregateIntervals (dataset)
  • input dataset
  • numeric behaviour (string)
  • aggregation type (string)
  • calculate empties (boolean)
  • value column (string)
  • period start column (string)
  • period end column (string)
  • target intervals (datetime array)
 Aggregates a dataset of values in one interval to another interval e.g. month to year. Works generically from any interval to another.

Parameters:

- numeric behaviour:
- sum
- average
- averageskiptempties - aggregation type:
- distribute
- first
- middle
- last
- calculate empties: true or false

Value column, period start column and period end column are the column names in the input dataset. Same column names are also used in the output dataset. The input dataset may also contain other columns and they are not in the output dataset. Target intervals is an array of datetimes representing output dataset intervals. There must be output intervals for all range of input intervals.

ApplicationSetting (string)
  • setting name (string)

Returns application setting value by its name. Settings depend on the application where the expression language is used. E.g. application settings for applications, that are part of QPR Web Services Extensions, are defined in web.config ofQPR Web Services Extensions.

Coalesce (object)
  • object_1
  • object_2, …

Returns first of the objects (counting from left to right) which value is - for strings, first string that is not null or empty string, or - for other than strings, first value that is not null. Minimum of two parameters are needed. If all parameters are nulls or empties, the first parameter is returned.

DataType (string)
  • object

Return the type of provided parameter, e.g. int, string, datetime.

DiagramPath (string array)
  • object id (string)
  • attribute name (string)
  • parent relation (string)
  • separator (string)
  • options (string)

Returns an array of EA/PD diagram paths for an object or an object instance. An array is returned as there can be several diagram paths if the object has several instances or if diagrams have been instantiated. Value of the provided attribute is used to identify an object in the diagram path (usually object "name" is used). Parent relation is the relation attribute name for getting parent objects (usually “parentobjects” is used). Also a "separator" character must be provided (usually "/"). "Options" is for QueryObjects operation for getting parent objects. This function can be used more generically to follow any relation path e.g. Metrics scorecards. Note that the object itself (1. parameter) is not part of the path.

DynamicWebViews (string)
  • template name (string)
  • parameter names (string array)
  • parameter values (object array)

Runs a QPR Web Views template.

DynamicWordReport (byte array)
  • report name (string)
  • parameter names (string array)
  • parameter values (object array)

Runs a QPR Word Reports report.

Escape (string)
  • input string escaping types (string)
  • number of escapings (int)

Available escaping types:

  • javascript: chars to escape: double-quote, single-quote, backslash ("'\)
  • json: chars to escape: double-quote, backslash ("\)
  • qprws: chars to escape: double-quote, backslash ("\)
  • xml:
  • expression: chars to escape: single-quote, backslash ("'\)
  • uri:
  • url:
  • html:
  • uridecode:
  • urldecode:
  • htmldecode:

In all escaping the escape char is \. Number of escapings means how many times the escapings are applied to the input string. It's optional (default is 1).

Eval (object)
  • expression (string)

Evaluates an expression.

ExecuteRecursion (object)
  • return expression (string)
  • recursion expression (string)
  • recursion initial value (object)
  • exclude traversed (boolean)

Executes a recursion based on provided expressions. There are two expressions:

  • return expression: Determines the value a recursion step returns. The expression contains an argument recursionresult which is an array containing the return expressions of all the one level below recursion steps.
  • recursion expression: Determines the next level recursions item. Must return an array. The value of this array item will be given to the next step of the recursion. The expression contains an argument currentrecursionstep which is the recursion value of the current step.

The recursion initial value is the value where the recursion starts. Exclude traversed means that items that have already been encountered during the recursion are excluded from next level recursion items. Example: ExecuteRecursion('1 + Sum([value])', 'QueryObjects(\'[\' + [value] + \'].childobjects\', \'\', \'\', \'id\', \'\')', 'PG.123.456')

ExecuteSearch (string array)
  • search configuration (XDocument)
 Executes search for QPR objects and returns matched object id's as a string array. The search is based on a search configuration provided as an xml document. The schema of the XML document is described in Appendix 1.
ExecuteTasks
  • configuration file contents (string)
  • default log file location (string)

Executes QPR Suite ETL Task Workbench task.

ExecuteVBScript
  • scriptParameters (string)
  • workingDirectory (string)
  • timeout (string)

Executes Visual Basic script using Windows Script Host.

GenerateNumberArray
  • start (int)
  • increment (int)
  • count (int)

Generates an integer array starting from start integer, using defined increment till the count is generated.

GetLog (string array) dateformat (string) Get QPR Web Services Extensions log written thus far as a string array.
GetSessionId (string) [none] Returns QPR Web Service session id, if there is a valid session. If not, returns an empty string.
If
  • condition (boolean)
  • true value (object)
  • false value (object)

Usual programming conditional statement. The condition is evaluated, and if the condition is true, the true value is returned; otwerwise it returns the false value. If the condition is evaluated to a null value, the false value is returned. The false value is optional, and in that case null is returned if condition is false. If the condition is true the false value (expression) is not evaluated and vice versa. Example: if([variable1] = 2, 'value is two', 'value is something else')

IsNull (boolean)
  • object

Return true if the value is null, otherwise false.

IsNumeric (boolean)
  • string

Returns true if the input string can be converted to a numerical value, otherwise false.

ListFiles (dataset)
  • directory (string)
  • recursive (bool)

Returns a list of files in the provided directory as a dataset. In the recursive mode, also files in the subdirectories are returned. Files full path is returned. Name of the column is "filepath".

Loop (object)
  • array expression (string)

Loops through an array and calculates an expression for every iteration. The function gives a value of the last iteration’s expression as a result. If array length is zero, a null value is returned. Following arguments are available in the expression:

  • value: Item in the array.
  • previousresult: Result of previous iteration’s calculated expression. For the first iteration, this value is null.
  • index: Iteration order number starting from 0.

Example: Loop(StringToArray('4,2,3', ','), 'coalesce([previousresult], 0) + [value]') (gives 4+2+3=9)

MeaPortalSessionId (string)
  • portal url (string)
  • username (string)
  • password (string)

Logins to QPR Portal and returns QPR Portal session id. Portal url is in the form http://HOSTNAME/QPR/Portal/QPR.Isapi.dll?QPRPORTAL&*pudev

NullValue (null) [none] Return a null value.
OdbcReadData (dataset)
  • connection string (string)
  • query (string)
  • connection timeout (int)
  • query timeout (int)
 Reads data from an ODBC source and returns the data in a dataset. To use the function, connection string and query need to be defined. Help for finding different kinds of connection strings can be found in http://connectionstrings.com.

Connection and query timeouts are optional, and they can be changed if needed. Note that appropriate ODBC drivers need to be installed. Example for reading reading all data from Excel file sheet "Sheet1": OdbcReadData('Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=C:\\test.xlsx;', 'Select * from [Sheet1$1]')

RaiseError
  • error message (string)

Raises (throws) an error and shows an error message that is passed as a parameter. This can be used if there is a more complex logic for parameter validation.

ReportsMenu (string)
  • menu configuration(s) (string or string array)
  • template name (string)

Returns html code for configured report items. Template must have parameter reportitems, which is a dataset containing report definitions. visibility, reporturl, reportname, visiblemessage, disabledmessage, target, width, height, scroll, preventcaching, x, y

SwitchCase (object)
  • control expression (object)
  • condition1 (object)
  • value1 (object)
  • condition2 (object)
  • value2 (object)
  • ...

Conventional programming "switch" statement. If control expression equals condition1, value1 is returned and so on. Control expression may be string, integer, double or date. If no condition matches, the default value is returned. Note that the number of parameters must be an equal number (4, 6, 8, …). Example: SwitchCase([variable1], 1, 'value is one', 2, 'value is two', 'value is something else')

ToJavascript (string)
  • input object

Convert an object into a notation that can be used in JavaScript code. Dates are converted into following kind of format: new Date(123456789)

ToJson (string)
  • input object

Converts an object into JSON string. Dates are converted into following kind of format /Date(123456789)/

UsageLog (string)
  • Log line (string)
  • Log file (string)
  • Log mode (string)
 Writes a line of text to a local file in disk. The function can be used e.g. for usage logging. Parameters:
- Log line is a line of text to append to the log file.
- Log file is the full path and name for the log file.
- Log mode is optional can be one of the following: o 1: Errors are skipped.
o 2: Error message is returned by function as a string.
o 3: An exception is thrown by the function. This means that the expression calculation fails.

If no errors occur, the function returns an empty string. When using Windows authentication, all users need to have write access to the log file. The function may be used in an Expression or Variable tag in Word reports. Note the possible security and performance issues when writing files. Example: UsageLog(CurrentDateTime () + ';' + CurrentUser() + ';' + ReportName(), 'C:/Logs/QPRReportsLog.txt')

QPR Word Reports Functions

These functions are only available when used in QPR Word Reports.

Function Parameters Description
ReportTemplateData (byte array) templatepath Gets a QPR Word Reports template Word file as byte array based on provided template path.
ReportName (string) [note] Returns the name of the current report.

QPR Web Views Functions

These functions are only available when used in QPR Web Views templates.

Function Parameters Description
ExistsTemplate (boolean) template name (string) Returns true if the template exists; otherwise false.
ReadTemplate(string) template name (string) Returns template contents as a string. Template must be referenced using absolute path (see DWV documentation). It’s possible to read any type of files by adding the file suffix to the file name (for tpl files, no suffix is added) Example: to read file schema.xsd in the root folder, use path / schema.xsd.
TemplateName (string) [none] The current template, i.e. the name of the template where the expression is run.
TemplateParameters (dictionary) [none] Returns all template parameters as a dictionary (string, object).
TemplatePath (string) [none] The current template’s path, i.e. the folder path of the template where the expression is run. The folder path starts from the templates root folder. No starting or ending slashes are part of the path.

WebpageAsImage Function

The expression language contains a function WebpageAsImage which takes a screenshot of a webpage and returns it as a PNG image (datatype is byte array). The function uses PhantomJS, a web browser that doesn’t contain a graphical user interface (headless browser), in the server to open the page. WebpageAsImage contains following parameters:

  • webpage url: Url address of the webpage. Mandatory parameter.
  • width (px): Width of the headless browser window in pixels. Mandatory parameter.
  • height (px): Height of the headless browser window in pixels. Mandatory parameter.
  • zoom: Web browser zooming given as a decimal number. E.g. value 0.75 equals 75% zoom. Parameter is optional and by default it's 1.0.
  • rendering delay (ms): After all http resources related to the webpage have been downloaded, the rendering delay time (defined in milliseconds) is waited until the screenshot is taken. Default value is 0 meaning the screenshot is taken immediately. Use the rendering delay, if it takes some time for the web page to render itself, or run animations and the screenshot should be taken when animation is finished.
  • timeout (ms): Timeout in milliseconds after which the function call is aborted and an error is returned. This parameter is optional and the default value is 30 seconds. Increase the timeout if the default is not enough to download and render the webpage.

Usage example: Following example uses the WebpageAsImage function to get a screenshot of QPR Community’s main page as an image to a Word report: 

<#image imagedata=="WebpageAsImage('https://community.qpr.com', 2000, 1000, 1.0, 1000)" maxwidth="17">

Following server side configurations are needed for QPR Reporting Add-on’s web.config to enable the WebpageAsImage function:

  • temppath: Folder path that is used by the WebpageAsImage function to temporarily store PhantomJS configuration files during function execution. No files are left after function is finished. This setting is mandatory. Make sure that the user running the IIS application has write permissions to the
  • installpath: Path for the PhantomJS executable file phantomjs.exe. This setting is optional, and by default it is C:\inetpub\wwwroot\QPRWebServicesExtensions\bin\phantomjs.exe. When making the installation as instructed, the default path is correct.

In addition, in IIS Management Console, application pool’s identity must be set to LocalSystem to that running PhantomJS has enough rights. These settings should have been set properly during the QPR Reporting Add-on’s installation.

More information about PhantomJS:

ExecuteSearch Function Configuration

The search is based in a defined scope, which is a set of objects where the search is targeted. A scope can be e.g. all published models, or all defined types of elements. The scope also includes element attributes where the search is targeted. A scope consist of multiple scope parts, which are QPR Web Services queries.

The objects defined by the scope are filtered using a criteria(the search results are the matching objects). Criteria can be any expression containing and, or, not and parenthesis.

XML element Description Attributes
executesearch (1)

Root element.

  • scopecombining: and, or
scopepart (1…n) Defines a scope part, which contains a web service query, transformation and attributes.

Possible parent elements: executesearch

  • query: QPR Web Services query
  • options: QPR Web Services query options
  • transformation: transformation relation name
criteria (1…n) Defines criteria for the search. There can be criteria elements inside other criteria elements to form a hierarchical structure which represents expression calculation order.

Criteria can be defined under executesearch for a criteria for all criteria parts, or the criteria can be defined under a certain criteria part.

Possible parent elements: executesearch, scopepart, criteria

  • type:
    • and (one to many sub criteria)
    • or (one to many sub criteria)
    • not (one sub criterion)
    • comparison (no sub criteria)

Rest of the attributes may be used when type is comparison:

  • text search: contains, begins, is
  • number/date search: =, <, >, <=, >=, <>
  • text/number/date search: isnull
  • text/number/date: reference value depending on search type.
  • attribute: compared QPR Web Services attribute.
  • matchcase: true or false. Used only in text search.
attribute (0..n) Defines searched attributes related to a scope part. Parent element is scopepart
  • name: name of the attribute

Example: 

<?xml version="1.0" encoding="utf-8"?>
<executesearch xmlns="http://www.qpr.com/QPRSuite/ExecuteSearch" scopecombining="or">
   <scopepart query="[PG.1127739389].activity" transformation="" options="">
      <criteria type="and">
         <criteria type="comparison" text="Review" attribute="name" comparisontype="begins" matchcase="true" />
         <criteria type="comparison" attribute="customattribute1.value" comparisontype="isnull" />
         <criteria type="or">
            <criteria type="comparison" attribute="startdate.value" comparisontype="isnull" />
            <criteria type="comparison" date="2016-12-18T00:00:00" attribute="startdate.value" comparisontype="<=" />
         </criteria>
         <criteria type="or">
            <criteria type="comparison" attribute="enddate.value" comparisontype="isnull" />
            <criteria type="comparison" date="2016-12-18T00:00:00" attribute="enddate.value" comparisontype=">=" />
         </criteria>
      </criteria>
   </scopepart>
   <scopepart query="[PG.1127739389].activity" transformation="käsitteenominaisuus">
      <criteria type="and">
         <criteria type="comparison" text="searchThisStringHere" attribute="name" comparisontype="contains" matchcase="false" />
      </criteria>
   </scopepart>
</executesearch>

Search configuration schema: 

<?xml version="1.0" encoding="UTF-8" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://www.qpr.com/ QPRSuite/ExecuteSearch" xmlns:search="http://www.qpr.com/QPRSuite/ExecuteSearch" elementFormDefault="qualified">
  <xs:complexType name="criteria">
    <xs:sequence>
      <xs:element name="criteria" type="search:criteria" minOccurs="0" maxOccurs="unbounded"/>
      </xs:sequence>
    <xs:attribute name="type" use="required">
      <xs:simpleType>
        <xs:restriction base="xs:string">
          <xs:enumeration value="textcomparison"/>
          <xs:enumeration value="and"/>
          <xs:enumeration value="or"/>
          <xs:enumeration value="not"/>
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
    <xs:attribute name="searchtexttext" type="xs:string"/>
    <xs:attribute name="attributenumber" type="xs:stringdouble"/>
    <xs:attribute name="comparisontypedate" type="xs:dateTime"/>
    <xs:attribute name="attribute">
      <xs:simpleType>
        <xs:restriction base="xs:string">
          <xs:minLength value="1"/>
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
    <xs:attribute name="comparisontype">
      <xs:simpleType>
        <xs:restriction base="xs:string">
          <xs:enumeration value="containbegins"/>
          <xs:enumeration value="contains"/>
          <xs:enumeration value="is"/>
          <xs:enumeration value="begin="="/>
          <xs:enumeration value="<"/>
          <xs:enumeration value=">"/>
          <xs:enumeration value="<="/>
          <xs:enumeration value=">="/>
          <xs:enumeration value="<>"/>
          <xs:enumeration value="isnull"/>
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
    <xs:attribute name="matchcase" type="xs:boolean"/>
    </xs:complexType>
  <xs:element name="executesearch">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="scopepart" maxOccurs="unbounded">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="criteria" type="search:criteria" minOccurs="0" maxOccurs="1"/>
              <xs:element name="attribute" minOccurs="0" maxOccurs="unbounded">
                <xs:complexType>
                  <xs:attribute name="name" type="xs:string"/>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
            <xs:attribute name="query" type="xs:string" use="required"/>
            <xs:attribute name="options" type="xs:string"/>
            <xs:attribute name="transformation" type="xs:string"/>
          </xs:complexType>
        </xs:element>
        <xs:element name="criteria" type="search:criteria" minOccurs="0" maxOccurs="1"/>
      </xs:sequence>
      <xs:attribute name="scopecombining">
        <xs:simpleType>
          <xs:restriction base="xs:string">
            <xs:enumeration value="and"/>
            <xs:enumeration value="or"/>
          </xs:restriction>
        </xs:simpleType>
      </xs:attribute>
    </xs:complexType>
  </xs:element>
</xs:schema>

QPR Reporting Expression Engine

QPR Reporting Expression Engine contains the following components: Expression Engine Web Service and Expression Engine Service Tester.

  • Expression Engine Web Service is a web service used to run expressions. This expression engine is embedded into many other accelerators, such as DWR and DWV.
  • Expression Engine Service Tester is an extension to QPR Web Service Tester, and it can be used to test expressions when e.g. developing report templates or configuration files for other accelerators. It’s possible to define multiple expressions which are calculated consecutively in a single web service operation. In that case expression results are stored in variables, and variables can be used in subsequent expression. Example:
var1='''3 + 9 / 3''' 
var2='''2*[var1]''' 
var3=''''Value is ' + [var2]'''

Expression Engine Web Services is a IIS hosted web application, and Expression Engine Service Tester is implemented with changes to QPR Web Service Tester web page html template.

Expression Engine Input Parameters:

Type: rootobject
Attribute Type Description
webServiceSessionId string QPR Web Service’s session id. Used only if authenticationmode=passedsession.
expressionSet string[] List of expressions.
variableNameSet string[] List of variable names. The variables get the calculated expression values, and the variables are available as arguments for the next expressions. The value of the last expression is returned by the operation.

Following output data format is used by both services.

Following image shows QPR Web Service tester where Expression Engine Service Tester is installed. 

Example.jpg
Retrieved from "https://wiki.onqpr.com/mea/index.php?title=QPR_Reporting_Expression_Language&oldid=9349"