Operators, Functions, Constants, and Criteria in the Report Expression Editor

Reports
The following table contains constants, operators, and functions you can use in expressions.

Constants

Constant
Description
Example
String constants
Wrap string constants in apostrophes.
If a string contains an apostrophe, double the apostrophe.
[Country] == 'France'
[Last Name] == 'O''Neil'
Date-time constants
Wrap date-time constants in '#'.
[Date Submitted] >= #2018-03-22 13:18:51.94944#
True
Represents the Boolean True value.
[Inactive] == True
False
Represents the Boolean False value.
[Inactive] == False
Enumeration
Specify an enumeration value using its underlying integer value.
[Status] == 1
Note that you cannot specify an enumeration value using its qualified name. The following criteria is incorrect:
[Status] = Status.InProgress
Guid
Wrap a Guid constant in curly braces. Use Guid constants in a relational operation with equality or inequality operators only.
[OrderID] == {513724e5-17b7-4ec6-abc4-0eae12c72c1f}
Numeric
Specify different numeric constant types in a string form using suffixes:
  • Int32 (int) - 1
  • Int16 (short) - 1s
  • Byte (byte) - 1b
  • Double (double) - 1.0
  • Single (float) - 1.0f
  • Decimal (decimal) - 1.0m
[Price] == 25.0m
?
Represents a null reference that does not refer to any object.
We recommend using the IsNull unary operator (for example, "[Country] is null") or the IsNull logical function (for example, "IsNull([Country])") instead.
[Country] != ?

Operators

Operator
Description
Example
+
Adds the value of one numeric expression to another or concatenates two strings.
[Quantity] + 4
[First Name] + ' ' + [Last Name]
-
Finds the difference between two numbers.
[Price1] - [Price2]
*
Multiplies the value of two expressions.
[Quantity] * [Price] * (1 - [BonusAmount])
/
Divides the first operand by the second.
[Quantity] / 2
%
Returns the remainder (modulus) obtained by dividing one numeric expression by another.
[Quantity] % 3
|
The bitwise OR operator. Compares each bit of its first operand to the corresponding bit of its second operand. If either bit is 1, the corresponding resulting bit is set to 1. Otherwise, the corresponding resulting bit is set to 0.
[Flag1] | [Flag2]
&
The bitwise AND operator. Compares each bit of its first operand to the corresponding bit of its second operand. If both bits are 1, the corresponding resulting bit is set to 1. Otherwise, the corresponding resulting bit is set to 0.
[Flag] & 10
^
Performs a logical exclusive OR on two Boolean expressions, or a bitwise exclusive OR on two numeric expressions.
[Flag1] ^ [Flag2]
==
=
Returns true if both operands have the same value; otherwise, it returns false.
[Quantity] == 10
!=
Returns true if the operands do not have the same value; otherwise, it returns false.
[Country] != 'France'
< 
Less than operator. Used to compare expressions.
[Price] < 20
<=
Less than or equal to operator. Used to compare expressions.
[Price] <= 20
>=
Greater than or equal to operator. Used to compare expressions.
[Price] >= 30
> 
Greater than operator. Used to compare expressions.
[Price] > 30
In (,,,)
Tests for the existence of a property in an object.
[Country] In ('USA', 'UK', 'Italy')
Between (,)
Specifies a range to test. Returns true if a value is greater than or equal to the first operand and less than or equal to the second operand.
[Quantity] Between (10, 20)
And
&&
Performs a logical conjunction on two expressions.
[Inactive] And ([Price]> 100)
[Inactive] && ([Price]> 100)
Or
||
Performs a logical disjunction on two Boolean expressions.
[Country]=='USA' Or [Country]=='UK'
[Country]=='USA' || [Country]=='UK'
!
Performs logical negation on an expression.
![Inactive]
 
Unary Operators
Unary operators perform operations on a single expression.
Name
Description
Usage
BitwiseNot
Represents the bitwise NOT operator.
"~[Quantity] = 251"
Not
Represents the logical NOT.
"Not [Inactive]"
Minus
Represents the unary negation (-) operator.
"[Value] = -20"
Plus
Represents the unary plus (+) operator.
"[Value] = +10"
IsNull
Represents a null reference, one that does not refer to any object.
"[Country] is null"

Functions

Aggregate Functions
Function
Description
Example
Avg(Value)
Evaluates the average of the values in the collection.
[Price Modifiers].Avg([Price])
Count()
Returns the number of objects in a collection.
[Price Modifiers].Count()
Exists()
Determines whether the object exists in the collection.
[Price Modifiers][[Type] == 1].Exists()
Max(Value)
Returns the maximum expression value in a collection.
[Price Modifiers].Max([Price])
Min(Value)
Returns the minimum expression value in a collection.
[Price Modifiers].Min([Price])
Single()
Returns a single object from the collection.
[Price Modifiers].Single() is not null
Sum(Value)
Returns the sum of all the expression values in the collection.
[Price Modifiers].Sum([Price])
 
Date-time Functions
Function
Description
Example
AddDays(DateTime, DaysCount)
Returns a date-time value that is the specified number of days from the specified DateTime.
AddDays([Date Submitted], 30)
AddHours(DateTime, HoursCount)
Returns a date-time value that is the specified number of hours from the specified DateTime.
AddHours([StartTime], 2)
AddMilliSeconds(DateTime, MilliSecondsCount)
Returns a date-time value that is the specified number of milliseconds from the specified DateTime.
AddMilliSeconds(([StartTime], 5000))
AddMinutes(DateTime, MinutesCount)
Returns a date-time value that is the specified number of minutes from the specified DateTime.
AddMinutes([StartTime], 30)
AddMonths(DateTime, MonthsCount)
Returns a date-time value that is the specified number of months from the specified DateTime.
AddMonths([OrderDate], 1)
AddSeconds(DateTime, SecondsCount)
Returns a date-time value that is the specified number of seconds from the specified DateTime.
AddSeconds([StartTime], 60)
AddTicks(DateTime, TicksCount)
Returns a date-time value that is the specified number of ticks from the specified DateTime.
AddTicks([StartTime], 5000)
AddTimeSpan(DateTime, TimeSpan)
Returns a date-time value that is from the specified DateTime for the given TimeSpan.
AddTimeSpan([StartTime], [Duration])
AddYears(DateTime, YearsCount)
Returns a date-time value that is the specified number of years from the specified DateTime.
AddYears([EndDate], -1)
DateDiffDay(startDate, endDate)
Returns the number of day boundaries between two non-nullable dates.
DateDiffDay([StartTime], Now())
DateDiffHour(startDate, endDate)
Returns the number of hour boundaries between two non-nullable dates.
DateDiffHour([StartTime], Now())
DateDiffMilliSecond(startDate, endDate)
Returns the number of millisecond boundaries between two non-nullable dates.
DateDiffMilliSecond([StartTime], Now())
DateDiffMinute(startDate, endDate)
Returns the number of minute boundaries between two non-nullable dates.
DateDiffMinute([StartTime], Now())
DateDiffMonth(startDate, endDate)
Returns the number of month boundaries between two non-nullable dates.
DateDiffMonth([StartTime], Now())
DateDiffSecond(startDate, endDate)
Returns the number of second boundaries between two non-nullable dates.
DateDiffSecond([StartTime], Now())
DateDiffTick(startDate, endDate)
Returns the number of tick boundaries between two non-nullable dates.
DateDiffTick([StartTime], Now())
DateDiffYear(startDate, endDate)
Returns the number of year boundaries between two non-nullable dates.
DateDiffYear([StartTime], Now())
GetDate(DateTime)
Extracts a date from the defined DateTime.
GetDate([Date Submitted])
GetDay(DateTime)
Extracts a day from the defined DateTime.
GetDay([Date Submitted])
GetDayOfWeek(DateTime)
Extracts a day of the week from the defined DateTime.
GetDayOfWeek([Date Submitted])
GetDayOfYear(DateTime)
Extracts a day of the year from the defined DateTime.
GetDayOfYear([Date Submitted])
GetHour(DateTime)
Extracts an hour from the defined DateTime.
GetHour([StartTime])
GetMilliSecond(DateTime)
Extracts milliseconds from the defined DateTime.
GetMilliSecond([StartTime])
GetMinute(DateTime)
Extracts minutes from the defined DateTime.
GetMinute([StartTime])
GetMonth(DateTime)
Extracts a month from the defined DateTime.
GetMonth([StartTime])
GetSecond(DateTime)
Extracts seconds from the defined DateTime.
GetSecond([StartTime])
GetTimeOfDay(DateTime)
Extracts the time of the day from the defined DateTime in ticks.
GetTimeOfDay([StartTime])
GetYear(DateTime)
Extracts a year from the defined DateTime.
GetYear([StartTime])
IsApril(DateTime)
Returns True if the specified date falls within April.
IsApril([Date Submitted])
IsAugust(DateTime)
Returns True if the specified date falls within August.
IsAugust([Date Submitted])
IsDecember(DateTime)
Returns True if the specified date falls within December.
IsDecember([Date Submitted])
IsFebruary(DateTime)
Returns True if the specified date falls within February.
IsFebruary([Date Submitted])
IsJanuary(DateTime)
Returns True if the specified date falls within January.
IsJanuary([Date Submitted])
IsJuly(DateTime)
Returns True if the specified date falls within July.
IsJuly([Date Submitted])
IsJune(DateTime)
Returns True if the specified date falls within June.
IsJune([Date Submitted])
IsLastMonth(DateTime)
Returns True if the specified date falls within the previous month.
IsLastMonth([Date Submitted])
IsLastYear(DateTime)
Returns True if the specified date falls within the previous year.
IsLastYear([Date Submitted])
IsMarch(DateTime)
Returns True if the specified date falls within March.
IsMarch([Date Submitted])
IsMay(DateTime)
Returns True if the specified date falls within May.
IsMay([Date Submitted])
IsNextMonth(DateTime)
Returns True if the specified date falls within the next month.
IsNextMonth([Date Submitted])
IsNextYear(DateTime)
Returns True if the specified date falls within the next year.
IsNextYear([Date Submitted])
IsNovember(DateTime)
Returns True if the specified date falls within November.
IsNovember([Date Submitted])
IsOctober(DateTime)
Returns True if the specified date falls within October.
IsOctober([Date Submitted])
IsSameDay(DateTime)
Returns True if the specified date/time values fall within the same day.
IsSameDay([Date Submitted])
IsSeptember(DateTime)
Returns True if the specified date falls within September.
IsSeptember([Date Submitted])
IsThisMonth(DateTime)
Returns True if the specified date falls within the current month.
IsThisMonth([Date Submitted])
IsThisWeek(DateTime)
Returns True if the specified date falls within the current week.
IsThisWeek([Date Submitted])
IsYearToDate(DateTime)
Returns True if the specified date falls within the year-to-date period. This period starts from the first day of the current year and continues to the current date (including the current date).
IsYearToDate([Date Submitted])
IsThisYear(DateTime)
Returns True if the specified date falls within the current year.
IsThisYear([Date Submitted])
LocalDateTimeDayAfterTomorrow()
Returns a date-time value corresponding to the day after Tomorrow.
AddDays(LocalDateTimeDayAfterTomorrow(), 5)
LocalDateTimeLastMonth()
Returns the DateTime value corresponding to the first day of the previous month.
AddMonths(LocalDateTimeLastMonth(), 5)
LocalDateTimeLastWeek()
Returns a date-time value corresponding to the first day of the previous week.
AddDays(LocalDateTimeLastWeek(), 5)
LocalDateTimeLastYear()
Returns the DateTime value corresponding to the first day of the previous year.
AddYears(LocalDateTimeLastYear(), 5)
LocalDateTimeNextMonth()
Returns a date-time value corresponding to the first day of the next month.
AddMonths(LocalDateTimeNextMonth(), 5)
LocalDateTimeNextWeek()
Returns a date-time value corresponding to the first day of the following week.
AddDays(LocalDateTimeNextWeek(), 5)
LocalDateTimeNextYear()
Returns a date-time value corresponding to the first day of the following year.
AddYears(LocalDateTimeNextYear(), 5)
LocalDateTimeNow()
Returns a date-time value corresponding to the current moment in time.
AddDays(LocalDateTimeNow(), 5)
LocalDateTimeThisMonth()
Returns a date-time value corresponding to the first day of the current month.
AddMonths(LocalDateTimeThisMonth(), 5)
LocalDateTimeThisWeek()
Returns a date-time value corresponding to the first day of the current week.
AddDays(LocalDateTimeThisWeek(), 5)
LocalDateTimeThisYear()
Returns a date-time value corresponding to the first day of the current year.
AddYears(LocalDateTimeThisYear(), 5)
LocalDateTimeToday()
Returns a date-time value corresponding to Today.
AddDays(LocalDateTimeToday(), 5)
LocalDateTimeTomorrow()
Returns a date-time value corresponding to Tomorrow.
AddDays(LocalDateTimeTomorrow(), 5)
LocalDateTimeTwoMonthsAway()
Returns the DateTime value corresponding to the first day of the following month.
AddMonths(LocalDateTimeTwoMonthAway(), 5)
LocalDateTimeTwoWeeksAway()
Returns the DateTime value corresponding to the first day of the following week.
AddDays(LocalDateTimeTwoWeeksAway(), 5)
LocalDateTimeTwoYearsAway()
Returns the DateTime value corresponding to the first day of the following year.
AddYears(LocalDateTimeTwoYearsAway(), 5)
LocalDateTimeYearBeforeToday()
Returns the DateTime value corresponding to the day one year ago.
AddYears(LocalDateTimeYearBeforeToday(), 5)
LocalDateTimeYesterday()
Returns a date-time value corresponding to Yesterday.
AddDays(LocalDateTimeYesterday(), 5)
Now()
Returns the current system date and time.
AddDays(Now(), 5)
Today()
Returns the current date. Regardless of the actual time, this function returns midnight of the current date.
AddMonths(Today(), 1)
UtcNow()
Returns the current system date and time, expressed as Coordinated Universal Time (UTC).
AddDays(UtcNow(), 7)
 
Logical Functions
Function
Description
Example
Iif(Expression, TruePart, FalsePart)
Returns one of several specified values depending upon the values of logical expressions.
The function can take n operands of the CriteriaOperator class:
1 - determines the first logical expression;
2 - specifies the value that is returned if the first logical expression evaluates to true;
...
n-2 - determines the n-2 logical expression;
n-1 - specifies the value that is returned if the n-2 logical expression evaluates to true;
n - specifies the value that is returned if the previously evaluated logical expressions yielded false.
Iif(Name = 'Bob', 1, Name = 'Dan', 2, Name = 'Sam', 3, 4)")
IsNull(Value)
Returns True if the specified Value is NULL.
IsNull([Date Submitted])
IsNull(Value1, Value2)
Returns Value1 if it is not set to NULL; otherwise, Value2 is returned.
IsNull([Date Submitted], [RequiredDate])
IsNullOrEmpty(String)
Returns True if the specified String object is NULL or an empty string; otherwise, False is returned.
IsNullOrEmpty([Company Name])
 
Math Functions
Function
Description
Example
Abs(Value)
Returns the given numeric expression's absolute, positive value.
Abs(1 - [Discount])
Acos(Value)
Returns a number's arccosine (the angle in radians, whose cosine is the given float expression).
Acos([Value])
Asin(Value)
Returns a number's arcsine (the angle in radians, whose sine is the given float expression).
Asin([Value])
Atn(Value)
Returns a number's arctangent (the angle in radians, whose tangent is the given float expression).
Atn([Value])
Atn2(Value1, Value2)
Returns the angle whose tangent is the quotient of two specified numbers in radians.
Atn2([Value1], [Value2])
BigMul(Value1, Value2)
Returns an Int64 containing the full product of two specified 32-bit numbers.
BigMul([Amount], [Quantity])
Ceiling(Value)
Returns the smallest integer that is greater than or equal to the numeric expression.
Ceiling([Value])
Cos(Value)
Returns the angle's cosine, in radians.
Cos([Value])
Cosh(Value)
Returns the angle's hyperbolic cosine, in radians.
Cosh([Value])
Exp(Value)
Returns the float expression's exponential value.
Exp([Value])
Floor(Value)
Returns the largest integer less than or equal to the numeric expression.
Floor([Value])
Log(Value)
Returns a specified number's natural logarithm.
Log([Value])
Log(Value, Base)
Returns the logarithm of a specified number in a specified Base.
Log([Value], 2)
Log10(Value)
Returns a specified number's base 10 logarithm.
Log10([Value])
Max(Value1, Value2)
Returns the maximum value from the specified values.
Max([Value1], [Value2])
Min(Value1, Value2)
Returns the minimum value from the specified values.
Min([Value1], [Value2])
Power(Value, Power)
Returns a specified number raised to a specified power.
Power([Value], 3)
Rnd()
Returns a random number that is less than 1, but greater than or equal to zero.
Rnd()*100
Round(Value)
Rounds the given value to the nearest integer.
Round([Value])
Round(Value, Precision)
Rounds the given value to the nearest integer, or to a specified number of decimal places.
Round([Value], 2)
Sign(Value)
Returns the positive (+1), zero (0), or negative (-1) sign of the given expression.
Sign([Value])
Sin(Value)
Returns the sine of the angle defined in radians.
Sin([Value])
Sinh(Value)
Returns the hyperbolic sine of the angle defined in radians.
Sinh([Value])
Sqr(Value)
Returns the square root of a given number.
Sqr([Value])
Tan(Value)
Returns the tangent of the angle defined in radians.
Tan([Value])
Tanh(Value)
Returns the hyperbolic tangent of the angle defined in radians.
Tanh([Value])
ToDecimal(Value)
Converts Value to an equivalent decimal number.
ToDecimal([Value])
ToDouble(Value)
Converts Value to an equivalent 64-bit double-precision floating-point number.
ToDouble([Value])
ToFloat(Value)
Converts Value to an equivalent 32-bit single-precision floating-point number.
ToFloat([Value])
ToInt(Value)
Converts Value to an equivalent 32-bit signed integer.
ToInt([Value])
ToLong(Value)
Converts Value to an equivalent 64-bit signed integer.
ToLong([Value])
 
String Functions
Function
Description
Example
Ascii(String)
Returns the ASCII code value of the leftmost character in a character expression.
Ascii('a')
Char(Number)
Converts an integerASCIICode to a character.
Char(65) + Char(51)
CharIndex(String1, String2)
Returns the starting position of String1 within String2, beginning from the zero character position to the end of a string.
CharIndex('m', 'ontime')
CharIndex(String1, String2, StartLocation)
Returns the starting position of String1 within String2, beginning from the StartLocation character position to the end of a string.
CharIndex('m', 'ontime', 2)
Concat(String1, ... , StringN)
Returns a string value containing the concatenation of the current string with any additional strings.
Concat('A', ')', [ProductName])
Contains(String1, SubString1)
Returns True if SubString1 occurs within String1; otherwise, False is returned.
Contains([ProductName], 'dairy')
EndsWith(String1, SubString1)
Returns True if the end of String1 matches SubString1; otherwise, False is returned.
EndsWith([Description], 'The end.')
Insert(String1, StartPosition, String2)
Inserts String2 into String1 at the position specified by StartPositon
Insert([Name], 0, 'ABC-')
Len(Value)
Returns an integer containing either the number of characters in a string or the nominal number of bytes required to store a variable.
Len([Description])
Lower(String)
Returns String in lowercase.
Lower([ProductName])
PadLeft(String, Length)
Left-aligns the defined string's characters, padding its left side with white space characters up to a specified total length.
PadLeft([Name], 30)
PadLeft(String, Length, Char)
Left-aligns the defined string's characters, padding its left side with the specified Char up to a specified total length.
PadLeft([Name], 30, '<')
PadRight(String, Length)
Right-aligns the defined string’s characters, padding its left side with empty space characters up to a specified total length.
PadRight([Name], 30)
PadRight(String, Length, Char)
Right-aligns the defined string’s characters, padding its left side with the specified Char up to a specified total length.
PadRight([Name], 30, '>')
Remove(String, StartPosition)
Deletes all the characters from this instance, beginning at a specified position.
Remove([Name], 3)
Remove(String, StartPosition, Length)
Deletes a specified number of characters from this instance, beginning at a specified position.
Remove([Name], 0, 3)
Replace(String, SubString2, String3)
Returns a copy of String1, in which SubString2 has been replaced with String3.
Replace([Name], 'The ', '')
Reverse(String)
Reverses the order of elements within String.
Reverse([Name])
StartsWith(String1, SubString1)
Returns True if the beginning of String1 matches SubString1; otherwise, False.
StartsWith([Description], 'The best')
Substring(String, StartPosition, Length)
Retrieves a substring from String. The substring starts at StartPosition and has a specified Length.
Substring([Description], 2, 3)
Substring(String, StartPosition)
Retrieves a substring from String. The substring starts at StartPosition.
Substring([Description], 2)
ToStr(Value)
Returns a string representation of an object.
ToStr([ID])
Trim(String)
Removes all leading and trailing SPACE characters from String.
Trim([Company Name])
Upper(String)
Returns String in uppercase.
Upper([Company Name])

Collection Elements Verification

Use brackets "[]" to check if a collection contains an element that satisfies a condition. The following expression returns true if the Price Modifiers collection contains at least one element that satisfies the [Price] > 100 condition:
[Price Modifiers][[Price] > 100]
The following expression returns false if the Price Modifiers collection is empty:
[Price Modifiers][]

Parent Relating Operator

Use the parent relating operator ('^' character) to refer to a parent in expressions written in the context of a child. You can apply this operator successively to navigate multiple parent relationships. In the expression below, the "Total Cost" field refers to an Order (Collection Location's parent) and the "Price" field refers to Price Modifiers. This expression returns true if there is at least one Price Modifier that has a price equal to the Order's total cost:
[Price Modifiers][[^.Total Cost] == Price]

Grouping Clauses with Brackets

It is important to use brackets to ensure that your expression returns the intended results. For instance, the following expression for objects of the Order type returns all of the Orders where an Order exists with a Date Submitted of 8/25/2018 and where an Order exists with a Total Cost greater than 100:
[Orders][[Date Submitted] == #8/25/2018#] && [Orders][[Total Cost] > 100]
Construct the expression as in the following example to search for all Orders that have Date Submitted of 8/25/2018 and a Total Cost greater than 100:
[Orders][[Date Submitted] == #8/25/2018# && [Total Cost] > 100]

Operator Precedence

When an expression contains multiple operators, their precedence controls the order in which expression elements are evaluated.
  • Literal values
  • Parameters
  • Identifiers
  • OR (left-associative)
  • AND (left-associative)
  • '.' relationship qualifier (left-associative)
  • ==, !=
  • <, >, <=, >=
  • -, + (left-associative)
  • *, /, % (left-associative)
  • NOT
  • unary -
  • In
  • Iif
  • Trim(), Len(), Substring(), IsNull()
  • '[]' (for set-restriction)
  • '()'
The default precedence can be changed by grouping elements with parentheses. For instance, the operators are performed in a default order in the first of the following two code samples. In the second code sample, the addition operation is performed first, because its associated elements are grouped with parentheses, and the multiplication operation is performed last.
[Price Modifiers][Price == 2 + 48 * 2]
[Price Modifiers][Price == (2 + 48) * 2]

Case Sensitivity

Operators are case insensitive. Field values are case sensitive. For example, this field will return results:
[Price Modifiers].[Name]
However, this field will fail due to the improper casing:
[price modifiers].[name]

Escaping Keywords

You can mark a keyword-like field name with an escape character (@ sign). In the expression below, the report interprets @Or as the field named "Or", not the logical operator OR.
@Or = 'value'
Article ID: 258, , Modified: September 18, 2019 at 6:15 PM

Share this article