Expression functions and operators

This topic lists and describes the functions and operators that you can use to construct expressions, using the Edit Expression Binding dialog box.

Math operators

Operator Description Expression
NEGATE Returns the negative value of a number -( number ) or

-( number1 + number2 )
+ Returns the sum of two numbers or concatenation of two strings
Note: For date values, use the DATEADD function.
 value1 + value2

where

value is a number or string
- Returns the difference or negation of two numbers
Note: For date values, use the DATEDIFF function.
 number1 - number2
* Returns the product of two numbers number1 * number2
/ Returns the quotient of two numbers number1 / number2
% Returns the remainder after dividing two numbers number1 % number2

Math functions

Operator Description Expression
ABS Returns the absolute value of a number ABS ( number )
CEILING Returns the least integer that is greater than or equal to the value of a number CEILING ( number )
FLOOR Returns the first integer that is less than or equal to the value of a number FLOOR ( number )
MAX Returns the larger of two numbers MAX ( number1, number2 )
MIN Returns the smaller of two numbers MIN ( number1, number2 )
POWER Returns the value of a number raised to a power POWER ( number1, number2 )

where

  • number1 is the value
  • number2 is the exponent
ROUND Returns the value of a number rounded to the number of decimal places ROUND ( number1, number2 )

where

  • number1 is the value
  • number2 is the number of decimal places
SIGN Returns 1 or -1 to indicate positive or negative value of a number SIGN ( number )
SQRT Returns the square root of a number SQRT ( number )
TRUNC Returns the value of a number rounded to the number of decimal places TRUNC ( number1, number2 )

where

  • number1 is the specified value
  • number2 is the number of decimal places

Comparison operators

Operator Description Expression
== or = Compares two values and returns True if they are equal and False otherwise value1 == value2

where

value is a number, Boolean value, or string
>= Compares two values and returns True if the first value is greater than or equal to the second value and False otherwise value1 >= value2

where

value is a number, Boolean value, or string
<= Compares two values and returns True if the first value is less than or equal to the second value and False otherwise value1 <= value2

where

value is a number, Boolean value, or string
> Compares two values and returns True if the first value is greater than the second value and False otherwise value1 > value2

where

value is a number, Boolean value, or string
< Compares two values and returns True if the first value is less than the second value and False otherwise value1 < value2

where

value is a number, Boolean value, or string
!= or <> Compares two values and returns True if they are not equal and False otherwise value1 != value2

where

value is a number, Boolean value, or string

Logical operators

Operator Description Expression
AND or && Logically And’s two Boolean values BooleanValue1 && BooleanValue2
NOT or ! Logically negates a Boolean value !( BooleanValue )
OR or || Logically Or’s two Boolean values BooleanValue1 || BooleanValue2

Logical functions

Operator Description Expression
CONTAINS Returns True if the string to seek is contained within the string to be searched and False otherwise CONTAINS ( string1, string2 )

where

  • string1 is the string to be searched
  • string2 is the string to seek
IF Evaluates the condition and returns the value based on result
Note: The value can be a number, Boolean value, or string.
 IF ( value1, value2, value3 )

where

  • value1 is the comparison expression
  • value2 is the result if true
  • value3 is the result if false
ISEMPTY Returns True if the string is empty and False otherwise ISEMPTY ( string )

Text functions

Operator Description Expression
CONCAT Returns the concatenation of two strings CONCAT ( string1, string2 )
INSTR Returns the 1-based starting index of the search string within the value and 0 if the search string is not found within the value INSTR ( string1, string2 ) or

INDEXOF ( string1, string2 )

where

  • string1 is the specified value
  • string2 is the search string
LEFT Returns a part of a string based on the number of characters from the start of the strin LEFT ( string, number )

where

  • string is the specified value
  • number is the number of characters
LEN Returns the length of a string, as number of characters LEN ( string ) or

LENGTH ( string )
LOWER Returns a string in lower case LOWER ( string )
LTRIM Returns a string in which whitespace is removed from the start of the string
Note: Quotation marks are included to display that the trailing whitespace was removed.
 LTRIM ( string )
REPLACE Returns a string in which all occurrences of the search string in the value are replaced by the replacement string REPLACE ( string1, string2, string3 )

where

  • string1 is the specified value
  • string2 is the search string
  • string3 is the replacement string
RIGHT Returns a part of a string based on the number of characters from the end of the string RIGHT ( string, number )

where

  • string is the specified value
  • number is the number of characters
RTRIM Returns a string in which whitespace is removed from the end of the string
Note: Quotation marks are included to display that the trailing whitespace was removed.
 RTRIM ( string )
SUBSTRING Returns a part of a string based on the 1-based starting index and optionally the number of characters SUBSTRING ( string, number1, number2 )

where

  • string is the specified value
  • number1 is the 1-based start index
  • number2 is the number of characters (optional)
TRIM Returns a string in which whitespace is removed from the start and end of the string
Note: Quotation marks are included to display that the trailing whitespace was removed.
 TRIM ( string )
UPPER Returns a string in upper case UPPER ( string )

Accumulator functions

Operator Description Expression
AVGACCUM Returns the average of the property value optionally grouped by property
Note: Properties of numeric data type are supported. This function is available for report forms only.
 AVGACCUM (string1,string2)

where

  • string1 is the property name
  • string2 is the group property name (optional)
CNTACCUM Returns the count of rows where the property value is not blank optionally grouped by property
Note: This function is available for report forms only.
 CNTACCUM (string1,string2)

where

  • string1 is the property name
  • string2 is the group property name (optional)
MAXACCUM Returns the maximum of the property value optionally grouped by property
Note: This function is available for report forms only.
 MAXACCUM (string1,string2)

where

  • string1 is the property name
  • string2 is the group property name (optional)
MINACCUM Returns the minimum of the property value optionally grouped by property
Note: This function is available for report forms only.
 MINACCUM (string1,string2)

where

  • string1 is the property name
  • string2 is the group property name (optional)
SUMACCUM Returns the summation of the property value optionally grouped by property
Note: Properties of numeric data type are supported. This function is available for report forms only.
 SUMACCUM (string, string2)

where

  • string1 is the property name
  • string2 is the group property name (optional)

Aggregate functions

Operator Description Expression
AVG Returns the average of a property that is optionally grouped or filtered by a group property AVG ( string1, string2 )

where

  • string1 is the property name
  • string2 is the group property (optional)
AVGEXP Returns the average of an expression that is optionally grouped of filtered by a group property.

AVGEXP (string1,string2,string3)

where

  • string1 is the collectionID of the collection which the expression is evaluated.

  • string2 is the expression to be evaluated for each row in the collection.

  • string3 is the group property name (optional).
CNT Returns the count of non-null or non-blank values a property that is optionally grouped or filtered by a group property CNT ( string1, string2 )

where

  • string1 is the property name
  • string2 is the group property (optional)
CNTEXP Returns the count of non-null or non-blank values of an expression that is optionally grouped or filtered by a group property.

CNTEXP (string1,string2,string3)

where

  • string1 is the collectionID of the collection which the expression is evaluated.

  • string2 is the expression to be evaluated for each row in the collection.

  • string3 is the group property name (optional).
MAXAGG Returns the maximum value of a property that is optionally grouped or filtered by a group property
Note: The value can be a number, date, or string depending on the data type of the property.
 MAXAGG ( string1, string2 )

where

  • string1 is the property name
  • string2 is the group property (optional)
MAXEXP
Returns the maximum value of an expression that is optionally grouped or filtered by a group property.
Note: The value can be a number, date, or string depending on the data type of the expression.

MAXEXP (string1,string2,string3)

where

  • string1 is the collectionID of the collection which the expression is evaluated.

  • string2 is the expression to be evaluated for each row in the collection.

  • string3 is the group property name (optional).
MINAGG Returns the minimum value of a property that is optionally grouped or filtered by a group property
Note: The value can be a number, date, or string depending on the data type of the property.
 MINAGG ( string1, string2 )

where

  • string1 is the property name
  • string2 is the group property (optional)
MINEXP
Returns the minimum value of an expression that is optionally grouped or filtered by a group property.
Note: The value can be a number, date, or string depending on the data type of the expression.

MINEXP (string1,string2,string3)

where

  • string1 is the collectionID of the collection which the expression is evaluated.

  • string2 is the expression to be evaluated for each row in the collection.

  • string3 is the group property name (optional).
SUM Returns the summation of a property that is optionally grouped or filtered by a group property SUM ( string1, string2 )

where

  • string1 is the property name
  • string2 is the group property (optional)
SUMEXP Returns the summation of an expression that is optionally grouped or filtered by a group property

SUMEXP (string1,string2,string3)

where

  • string1 is the collectionID of the collection which the expression is evaluated.

  • string2 is the expression to be evaluated for each row in the collection.

  • string3 is the group property name (optional).

Date functions

Operator Description Expression
CURDATE Returns the current date CURDATE ( )
CURDATETIME Returns the current date and time CURDATETIME ( )
DATE Returns a date that contains the year, month, and day DATE ( year, month, day )
DATEADD Returns a new date based on the interval that is added to the date

DATEADD ( string, number, date )

where

string is the date interval
DATEDIFF Returns the difference between two dates based on the interval

DATEDIFF ( string, date1, date2 )

where

string is the date interval
DATEPART Returns a part of the date based on the interval

DATEPART ( string, date )

where

string is the date interval
ISOWEEKNUM Returns the ISO 8601 week number of the date ISOWEEKNUM ( date )

Mongoose functions

Operator Description Expression
C Returns the value of a component

C ( string )

where

string is the component name
FORMAT Returns the formatted or translated value of a string by using substitution string(s)

FORMAT ( string1, string2, string3 )

where
  • string1 is the format
  • string2 and string3 are substitute strings (optional)
P Returns the value of a property

P ( string )

where

string is the property name
STRINGS Returns the translated value of a string

STRINGS ( string )

where

string is the string name
USERNAME Returns the current user name USERNAME ( )
V Returns the value of a variable

V ( string )

where

string is the variable name

DataView functions

Operator Description Expression
AVG Returns the average of a property where optionally all rows in the group are aggregated, or the group is ignored and all rows are aggregated AVG ( string1, string2 )

where

  • string1 is the property name
  • string2 is use all rows (optional)
AVGEXP Returns the average of an expression where optionally all rows in the group are aggregated, or the group is ignored and all rows are aggregated

AVGEXP (string1,string2,string3)

where

  • string1 is the idoAlias that identifies the IDO within the DataView.

  • string2 is the expression to be evaluated for each row in the collection.

  • string3 is use all rows (optional).
CNT Returns the count of non-null or non-blank values a property where optionally all rows in the group are aggregated, or the group is ignored and all rows are aggregated CNT ( string1, string2 )

where

  • string1 is the property name
  • string2 is use all rows (optional)
CNTEXP Returns the count of non-null or non-blank values of an expression where optionally all rows in the group are aggregated, or the group is ignored and all rows are aggregated

CNTEXP (string1,string2,string3)

where

  • string1 is the idoAlias that identifies the IDO within the DataView.

  • string2 is the expression to be evaluated for each row in the collection.

  • string3 is use all rows (optional).
DATAVIEWPROP Returns the value of a DataView property at the same level of the DataView as the expression DATAVIEWPROP ( string )

where

string is the property name
DATAVIEWEXP Returns the value of another expression column at the same level of the DataView as the expression DATAVIEWEXP ( string )

where

string is the name of another expression column
MAXAGG Returns the maximum value of a property where optionally all rows in the group are aggregated, or the group is ignored and all rows are aggregated
Note: The value can be a number, date, or string depending on the data type of the property.
 MAXAGG ( string1, string2 )

where

  • string1 is the property name
  • string2 is use all rows (optional)
MAXEXP
Returns the maximum value of an expression where optionally all rows in the group are aggregated, or the group is ignored and all rows are aggregated
Note: The value can be a number, date, or string depending on the data type of the expression.

MAXEXP (string1,string2,string3)

where

  • string1 is the idoAlias that identifies the IDO within the DataView.

  • string2 is the expression to be evaluated for each row in the collection.

  • string3 is use all rows (optional).
MINAGG Returns the minimum value of a property where optionally all rows in the group are aggregated, or the group is ignored and all rows are aggregated
Note: The value can be a number, date, or string depending on the data type of the property.
 MINAGG ( string1, string2 )

where

  • string1 is the property name
  • string2 is use all rows (optional)
MINEXP
Returns the minimum value of an expression where optionally all rows in the group are aggregated, or the group is ignored and all rows are aggregated
Note: The value can be a number, date, or string depending on the data type of the expression.

MINEXP (string1,string2,string3)

where

  • string1 is the idoAlias that identifies the IDO within the DataView

  • string2 is the expression to be evaluated for each row in the collection.

  • string3 is use all rows (optional)
SUM Returns the summation of a property where optionally all rows in the group are aggregated, or the group is ignored and all rows are aggregated SUM ( string1, string2 )

where

  • string1 is the property name
  • string2 is use all rows (optional)
SUMEXP Returns the summation of an expression where optionally all rows in the group are aggregated, or the group is ignored and all rows are aggregated

SUMEXP (string1,string2,string3)

where

  • string1 is the idoAlias that identifies the IDO within the DataView.

  • string2 is the expression to be evaluated for each row in the collection.

  • string3 is use all rows (optional).
Related topics