Operators, Literals, and Functions

This page lists the operators, literals, and functions that can be used in expressions.

Operators

Expression	Description	Example
`+`	Adds the value of one numeric expression to another. or Concatenates two strings.	`[Quantity] + 1` or `'State: ' + [STATENAME]`
`-`	Finds the difference between two numbers.	`[pop2018] - [pop2014]`
`*`	Multiplies the value of two expressions.	`[Quantity] * [UnitCost] * (1 - [BonusAmount])`
`/`	Divides the first operand by the second operand.	`[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`
`^`	Returns `True` if both operands have the same value. Otherwise, returns `False`.	`[Flag1] ^ [Flag2]`
`==` or `=`	Returns `True` if both operands have the same value. Otherwise, returns `False`.	`[Quantity] == 10` or `[Quantity] = 10`
`!=`	Returns `True` if the operands do not have the same value. Otherwise, returns `False`.	`[Country] != 'France'`
`<`	Less than operator. Used to compare expressions.	`[Quantity] < 20`
`<=`	Less than or equal to operator. Used to compare expressions.	`[Quantity] <= 20`
`>=`	Greater than or equal to operator. Used to compare expressions.	`[Quantity] >= 30`
`>`	Greater than operator. Used to compare expressions.	`[Quantity] > 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; otherwise, returns false.	`[Quantity] Between (10, 20)`
`And` or `&&`	Performs a logical conjunction on two Boolean expressions.	`[InStock] And ([Price]> 100)` or `[InStock] && ([Price]> 100)`
`Or` or `\|\|`	Performs a logical disjunction on two Boolean expressions.	`[Country]=='USA' Or [Country]=='UK'` or `[Country]=='USA' \|\| [Country]=='UK'`
`Not` or `!`	Performs logical negation on a Boolean expression.	`Not [RoadClosed]` or `![RoadClosed]`

Literals

Literal	Description	Example
String literals	String literals must be surrounded by apostrophes (`'`). If a string literal needs to have an apostrophe within it, the apostrophe must be doubled.	`[Country] == 'France'` `[Name] == 'O''Neil'`
Numeric literals	Numeric literals (numbers) do not require any special punctuation.	`[age] >= 21`
Boolean literals	The Boolean literals are `True` and `False`.	`[InStock] == True` `[InStock] == False`
Date-time literals	Date-time literals must be surrounded by hash marks (`#`).	`[OrderDate] >= #1/1/2009#`
`?`	Represents a null reference, in other words, a reference that does not refer to any object.	`[Region] != ?`

Functions

Functions are organized into the following categories:

Aggregate Functions
Array Functions
Date-Time Functions
GIS Functions
Image Functions
JSON Functions
Logical Functions
Math Functions
String Functions
User Functions

Aggregate Functions

Function	Description	Example
`Avg(Value)`	Evaluates the average of the values in the collection.	`[].Avg([UnitPrice])`
`Count()`	Returns the number of objects in a collection.	`[].Count()`
`Exists()`	Determines whether the object exists in the collection.	`[][[CategoryID] == 7].Exists()`
`Max(Value)`	Returns the maximum expression value in a collection.	`[].Max([UnitPrice])`
`Min(Value)`	Returns the minimum expression value in a collection.	`[].Min([UnitPrice])`
`Single()`	Returns a single object from the collection.	`[][^.ParentId = Id].Single([This])`
`Sum(Value)`	Returns the sum of all the expression values in the collection.	`[].Sum([UnitsInStock])`

Array Functions

Function	Description	Example
`IndexOf`	Returns the first index at which a given element can be found in the array, or -1 if it is not present.	`IndexOf(['a','b','c'], 'b') returns 1`
`ElementAt`	Returns the element at the given index. Alternatively, null is returned if the index is out of range.	`ElementAt(['a','b','c', 2) returns 'c'`

Date-Time Functions

VertiGIS Studio Reporting offers additional date-time functions that are not listed in the table. Use the Expression Editor to browse the full list of functions.

Function	Description	Example
`AddDays(DateTime, DaysCount)`	Returns a date-time value that is the specified number of days away from the specified `DateTime`.	`AddDays([OrderDate], 30)`
`AddHours(DateTime, HoursCount)`	Returns a date-time value that is the specified number of hours away from the specified `DateTime`.	`AddHours([StartTime], 2)`
`AddMilliSeconds(DateTime, MilliSecondsCount)`	Returns a date-time value that is the specified number of milliseconds away from the specified `DateTime`.	`AddMilliSeconds([StartTime], 5000)`
`AddMinutes(DateTime, MinutesCount)`	Returns a date-time value that is the specified number of minutes away from the specified DateTime.	`AddMinutes([StartTime], 30)`
`AddMonths(DateTime, MonthsCount)`	Returns a date-time value that is the specified number of months away from the specified `DateTime`.	`AddMonths([OrderDate], 1)`
`AddSeconds(DateTime, SecondsCount)`	Returns a date-time value that is the specified number of seconds away from the specified `DateTime`.	`AddSeconds([StartTime], 60)`
`AddTicks(DateTime, TicksCount)`	Returns a date-time value that is the specified number of ticks away from the specified `DateTime`.	`AddTicks([StartTime], 5000)`
`AddTimeSpan(DateTime, TimeSpan)`	Returns a date-time value that is away 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 away from the specified `DateTime`.	`AddYears([EndDate], -1)`
`AdjustTimeZone(DateTime, 'sourceTimeZoneId', 'destinationTimeZoneId')`	Adjusts the given source date to the specified time zone using Windows time zone identifiers. The `DateTime` is the source date to be adjusted. The `sourceTimeZoneId` is the time zone of the source date. The `destinationTimeZoneId` is the time zone of the desired output date. Supported Windows time zones are documented here.	`AdjustTimeZone([DATE_FIELD], 'UTC', 'Pacific Standard Time')`
`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([OrderDateTime])`
`GetDay(DateTime)`	Extracts a day from the defined `DateTime`.	`GetDay([OrderDate])`
`GetDayOfWeek(DateTime)`	Extracts a day of the week from the defined `DateTime`.	`GetDayOfWeek([OrderDate])`
`GetDayOfYear(DateTime)`	Extracts a day of the year from the defined `DateTime`.	`GetDayOfYear([OrderDate])`
`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])`
`IsThisMonth(DateTime)`	Returns `True` if the specified date falls within the current month. Otherwise, returns `False`.	`IsThisMonth([OrderDate])`
`IsThisWeek(DateTime)`	Returns `True` if the specified date falls within the current week. Otherwise, returns `False`.	`IsThisWeek([OrderDate])`
`IsThisYear(DateTime)`	Returns `True` if the specified date falls within the current year. Otherwise, returns `False`.	`IsThisYear([OrderDate])`
`LocalDateTimeDayAfterTomorrow()`	Returns a date-time value corresponding to the day after `Tomorrow`.	`AddDays(LocalDateTimeDayAfterTomorrow(), 5)`
`LocalDateTimeLastWeek()`	Returns a date-time value corresponding to the first day of the previous week.	`AddDays(LocalDateTimeLastWeek(), 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)`
`LocalDateTimeTwoWeeksAway()`	Returns a date-time value corresponding to the first day of the week that is after next week.	`AddDays(LocalDateTimeTwoWeeksAway(), 5)`
`LocalDateTimeYesterday()`	Returns a date-time value corresponding to `Yesterday`.	`AddDays(LocalDateTimeYesterday(), 5)`
`Now()`	Returns the current system date and time.	`AddDays(Now(), 5)`
`ParseDate(Value)`	Converts a text or numeric representation of a date and time into a DateTime value. This function accepts ISO 8601 pattern strings and integer values representing milliseconds since January 1, 1970, 00:00:00 UTC.	`ParseDate('2023-03-20T18:54:26.000Z')` `ParseDate(1679338466000)`
`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)`

GIS Functions

To evaluate a GIS function, VertiGIS Studio Reporting queries ArcGIS for the field information that the function will operate on. The [@Feature] column contains the source feature information returned by the ArcGIS query.

Function	Description	Example
`GetBoundingBox(@Feature, ExpandFactor)`	Returns the bounding coordinates of the feature. The optional expand factor is value greater than zero used to scale the bounding box. A value of 1.1 expands the bounding box by 10%. A value of 0.9 reduces the bounding box by 10%.	`GetBoundingBox(@Feature, 1.1)`
`GetDomainDescription([@Feature], 'fieldName')`	Returns the description of a coded value domain field for the given feature. The field name is case sensitive.	`GetDomainDescription([@Feature], 'LANDUSE')`
`GetSubtypeName([@Feature])`	Returns the name of the subtype for the given feature.	`GetSubtypeName([@Feature])`
`GetMCoordinate([@Feature])`	Returns the M coordinate value of the specified feature. The feature must be a point feature that supports M-values and the data source must be configured to return M-values.	`GetMCoordinate([@Feature])`
`GetXCoordinate([@Feature])`	Returns the X coordinate value of the specified feature. The feature must be a point feature.	`GetXCoordinate([@Feature])`
`GetYCoordinate([@Feature])`	Returns the Y coordinate value of the specified feature. The feature must be a point feature.	`GetYCoordinate([@Feature])`
`GetZCoordinate([@Feature])`	Returns the Z coordinate value of the specified feature. The feature must be a point feature that supports Z-values and the data source must be configured to return Z-values.	`GetZCoordinate([@Feature])`

Image Functions

Function	Description	Example
`CorrectImageOrientation(Bytes)`	Corrects the orientation of an image based on the image's EXIF metadata. This function accepts a byte array representing the binary content of the source image and returns a new byte array representing the corrected image.	`CorrectImageOrientation([ImageField])`
`ReduceImageQuality(Bytes, MaximumDpi, TargetControl)`	Reduces the quality of an image by resizing it to achieve the desired maximum DPI. This function accepts a byte array representing the binary content of the source image and returns a new byte array representing the reduced image. The MaximumDpi value is a positive integer. The TargetControl is the image control within the report that will receive the image.	`ReduceImageQuality([ImageField], 300, [ReportItems.pictureBox1])`

JSON Functions

Function	Description	Example
`GetJsonValue(JSON, JSONPath)`	Gets a value from the JSON string using the JSON path expression.	`GetJsonValue([JsonField], '$.address.city')`

Logical Functions

Function	Description	Example
`Iif(Expression, TruePart, FalsePart)`	Returns either `TruePart` or `FalsePart`, depending on the evaluation of the Boolean expression.	`Iif([Quantity]>=10, 10, 0)`
`IsNull(Value)`	Returns `True` if the specified `Value` is null.	`IsNull([OrderDate])`
`IsNullOrEmpty(String)`	Returns `True` if the specified `String` object is null or an empty string. Otherwise, returns `False`.	`IsNullOrEmpty([ProductName])`

Math Functions

Function	Description	Example
`Abs(Value)`	Returns the absolute, positive value of the given numeric expression.	`Abs(1 - [Discount])`
`Acos(Value)`	Returns the arccosine of a number (the angle in radians, whose cosine is the given float expression)	`Acos([Value])`
`Asin(Value)`	Returns the arcsine of a number (the angle in radians, whose sine is the given float expression).	`Asin([Value])`
`Atn(Value)`	Returns the arctangent of a number (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 given numeric expression.	`Ceiling([Value])`
`Cos(Value)`	Returns the cosine of the angle defined in radians.	`Cos([Value])`
`Cosh(Value)`	Returns the hyperbolic cosine of the angle defined in radians.	`Cosh([Value])`
`Exp(Value)`	Returns the exponential value of the given float expression.	`Exp([Value])`
`Floor(Value)`	Returns the largest integer less than or equal to the given numeric expression.	`Floor([Value])`
`Log(Value)`	Returns the natural logarithm of a specified number.	`Log([Value])`
`Log(Value, Base)`	Returns the logarithm of a specified number in a specified Base.	`Log([Value], 2)`
`Log10(Value)`	Returns the base 10 logarithm of a specified number.	`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

VertiGIS Studio Reporting offers additional string functions that are not listed in the table. Use the Expression Editor to browse the full list of functions.

Function	Description	Example
`Ascii(String)`	Returns the ASCII code value of the leftmost character in a character expression.	`Ascii('a')`
`Base64Decode(String)`	Returns the decoded bytes of the given base 64-encoded string.	`Base64Decode([base64data])`
`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('e', 'geocortex')`
`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('e', 'geocortex', 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, returns `False`.	`Contains([ProductName], 'dairy')`
`EndsWith(String1, SubString1)`	Returns `True` if the end of `String1` matches `SubString1`. Otherwise, returns `False`.	`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])`
`NewLine()`	Advances to a new line.	`NewLine()`
`PadLeft(String, Length)`	Left-aligns characters in the defined `String`, padding its left side with white space characters up to a specified total `Length`.	`PadLeft([Name], 30)`
`PadLeft(String, Length, Char)`	Left-aligns characters in the defined `String`, padding its left side with the specified `Char` up to a specified total `Length`.	`PadLeft([Name], 30, '<')`
`PadRight(String, Length)`	Right-aligns characters in the defined `String`, padding its left side with white space characters up to a specified total `Length`.	`PadRight([Name], 30)`
`PadRight(String, Length, Char)`	Right-aligns characters in the defined `String`, padding its left side with the specified `Char` up to a specified total `Length`.	`PadRight([Name], 30, '>')`
`Remove(String, StartPosition)`	Deletes all characters from the `String`, beginning at a specified `StartPosition`.	`Remove([Name], 3)`
`Remove(String, StartPosition, Length)`	Deletes the number of characters specified by `Length` from the `String`, beginning at a specified `StartPosition`.	`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, returns `False`.	`StartsWith([Title], 'The best')`
`Substring(String, StartPosition)`	Retrieves a substring from `String`. The substring starts at `StartPosition`.	`Substring([Description], 2)`
`Substring(String, StartPosition, Length)`	Retrieves a substring from `String`. The substring starts at `StartPosition` and has a specified `Length`.	`Substring([Description], 2, 3)`
`ToStr(Value)`	Returns a string representation of an object.	`ToStr([ID])`
`Trim(String)`	Removes all leading and trailing space characters from `String`.	`Trim([ProductName])`
`Upper(String)`	Returns `String` in uppercase.	`Upper([ProductName])`
`UrlEncode(String)`	Returns the URL encoded form of the given string.	`UrlEncode('Hello world')`

User Functions

Function	Description	Example
`GetUserDisplayName()`	Gets the display name of the current user.	`GetUserDisplayName()`
`GetUserName()`	Gets the current user name.	`GetUserName()`