Aggregate Functions

For a more in-depth guide on aggregates click here.

Avg: [Collection]​[Condition].​Avg​(Expression)
Example: [Products][Discontinued == False].Avg([UnitsInStock])
Returns the average of all the Expression values in the Collection based on the specified Condition (Optional)

 

 

Count: [Collection]​[Condition].​Count​()
Example: [Products][Discontinued == False].Count()
Returns the number of entries in the Collection based on the specified Condition (optional)

 

 

Exists: [Collection]​[Condition].​Exists​()
Example: [Products][UnitPrice > 0].Exists()

Determines whether the specified Condition holds true for the defined Collection.

 

 

Max: [Collection]​[Condition].​Max​(Expression)
Example: [Products][Discontinued == False].Max([UnitPrice])

Returns the maximum Expression value in the specified Collection based on the defined Condition (optional).

 

 

Min: [Collection]​[Condition].​Min​(Expression)
Example: [Products][Discontinued == False].Min([UnitPrice])

Returns the minimum Expression value in the specified Collection based on the defined Condition (optional).

 

 

Sum: [Collection]​[Condition].​Sum​(Expression)
Example: [Products][Discontinued ==False].Sum([UnitsInStock])

Returns the sum of all the Expression values in the Collection based on the specified Condition (optional).

 

 

Single: [Collection]​[Condition].​Single​(Expression)
Example: [Products][UnitPrice > 200].Single('True')

Returns the Expression if the Collection contains only one object meeting the specified Condition (optional).

 

DateTime Functions

For a more in-depth guide on DateTime Functions click here.

 

AddDays: AddDays(DateTime, DaysCount)
Returns a date-time value that is the specified number of DaysCount away from the specified DateTime.

Example: AddDays([OrderDate], 30)

 

 

AddHours: AddHours(DateTime, HoursCount)
Returns a date-time value that is the specified number of HoursCount away from the specified DateTime.

Example: AddHours([StartTime], 2) 

 

 

AddMilliSeconds: AddMilliSeconds(DateTime, MilliSecondsCount)
Returns a date-time value that is the specified number of MilliSecondsCount away from the specified DateTime.

Example: AddMilliSeconds([StartTime], 5000)

 

 

Addminutes: AddMinutes(DateTime, MinutesCount)
Returns a date-time value that is the specified number of MinutesCount away from the specified DateTime.

Example: AddMinutes([StartTime], 30)

 

 

AddMonths: AddMonths(DateTime, MonthsCount)
Returns a date-time value that is the specified number of MonthsCount away from the specified DateTime.

Example: AddMonths([OrderDate], 1)

 

 

AddSeconds: AddSeconds(DateTime, SecondsCount)
Returns a date-time value that is the specified number of SecondsCount away from the specified DateTime.

Example: AddSeconds([StartTime], 60)

 

 

AddTicks: AddTicks(DateTime, AddTicks)
Returns a date-time value that is the specified number of TicksCount away from the specified DateTime.

Example: AddTicks([StartTime], 5000)

 

 

AddTimeSpan: AddTimeSpan(DateTime, TimeSpan)
Returns a date-time value that is away from the specified DateTime for the given TimeSpan.

Example: AddTimeSpan([StartTime], [TimeSpan])

 

 

AddYears: AddYears(DateTime, YearsCount)
Returns a date-time value that is the specified number of YearsCount (Negative Values Subract Years) away from the specified DateTime.

Example: AddYears([OrderDate], 10)
Example 2: AddYears([OrderDate], -10)

 

 

DateDiffDay: DateDiffDay(StartDate, EndDate)
Returns the number of day boundaries between two non-nullable dates.

Example: DateDiffDay([OrderDate], [ShippedDate])

 

 

DateDiffHour: DateDiffHour(StartDate, EndDate)
Returns the number of hour boundaries between two non-nullable dates.

Example: DateDiffHour([OrderDate], [ShippedDate])

 

 

DateDiffMilliSecond: DateDiffMilliSecond(StartDate, EndDate)
Returns the number of millisecond boundaries between two non-nullable dates.

Example: DateDiffMilliSecond([OrderDate], [ShippedDate])

 

 

DateDiffMinute: DateDiffMinute(StartDate, EndDate)
Returns the number of minute boundaries between two non-nullable dates.

Example: DateDiffMinute([OrderDate], [ShippedDate])

 

 

DateDiffMonth: DateDiffMonth(StartDate, EndDate)
Returns the number of month boundaries between two non-nullable dates.

Example: DateDiffMonth([OrderDate], [ShippedDate])

 

 

DateDiffSecond: DateDiffSecond(StartDate, EndDate)
Returns the number of second boundaries between two non-nullable dates.

Example: DateDiffSecond([OrderDate], [ShippedDate])

 

 

DateDiffTick: DateDiffTick(StartDate, EndDate)
Returns the number of tick boundaries between two non-nullable dates.

Example: DateDiffTick([OrderDate], [ShippedDate])

 

 

DateDiffYear: DateDiffYear(StartDate, EndDate)
Returns the number of year boundaries between two non-nullable dates.

Example: DateDiffYear([OrderDate], [ShippedDate])

 

 

GetDate: GetDate(DateTime)
Extracts a date from the defined DateTime.

Example: GetDate([OrderDate])

 

 

GetDay: GetDay(DateTime)
Extracts a day from the defined DateTime.

Example: GetDay([OrderDate])

 

 

GetDayOfWeek: GetDayOfWeek(DateTime)
Extracts a day of the week from the defined DateTime.

Example: GetDayOfWeek([OrderDate])

 

 

GetDayOfYear: GetDayOfYear(DateTime)
Extracts a day of the year from the defined DateTime.

Example: GetDayOfYear([OrderDate])

 

 

GetHour: GetHour(DateTime)
Extracts an hour from the defined DateTime, in ticks.

Example: GetHour([OrderDate])

 

 

GetMilliSecond: GetMilliSecond(DateTime)
Extracts milliseconds from the defined DateTime.

Example: GetMilliSecond([OrderDate])

 

 

GetMinute: GetMinute(DateTime)
Extracts minutes from the defined DateTime.

Example: GetMinute([OrderDate])

 

 

GetMonth: GetMonth(DateTime)
Extracts a month from the defined DateTime.

Example: GetMonth([OrderDate])

 

 

GetSecond: GetSecond(DateTime)
Extracts seconds from the defined DateTime.

Example: GetSecond([OrderDate])

 

 

GetTimeOfDay: GetTimeOfDay(DateTime)
Extracts the time of the day from the defined DateTime, in ticks.

Example: GetTimeOfDay([OrderDate])

 

 

GetYear: GetYear(DateTime)
Extracts a year from the defined DateTime, in ticks.

Example: GetYear([OrderDate])

 

 

IsApril: IsApril(DateTime)
Returns True if the specified DateTime falls within April.

Example: IsApril([OrderDate])

 

 

IsAugust: IsAugust(DateTime)
Returns True if the specified DateTime falls within August.

Example: IsAugust([OrderDate])

 

 

IsDecember: IsDecember(DateTime)
Returns True if the specified DateTime falls within December.

Example: IsDecember([OrderDate])

 

 

IsFebruary: IsFebruary(DateTime)
Returns True if the specified DateTime falls within February.

Example: IsFebruary([OrderDate])

 

 

IsJanuary: IsJanuary(DateTime)
Returns True if the specified DateTime falls within January.

Example: IsJanuary([OrderDate])

 

 

IsJuly: IsJuly(DateTime)
Returns True if the specified DateTime falls within July.

Example: IsJuly([OrderDate])

 

 

IsJune: IsJune(DateTime)
Returns True if the specified DateTime falls within June.

Example: IsJune([OrderDate])

 

 

IsLastMonth: IsLastMonth(DateTime)
Returns True if the specified DateTime falls within the previous month.

Example: IsLastMonth([OrderDate])

 

 

IsLastYear: IsLastYear(DateTime)
Returns True if the specified DateTime falls within the previous year.

Example: IsLastYear([OrderDate])

 

 

IsMarch: IsMarch(DateTime)
Returns True if the specified DateTime falls within March.

Example: IsMarch([OrderDate])

 

 

IsMay: IsMay(DateTime)
Returns True if the specified DateTime falls within May.

Example: IsMay([OrderDate])

 

 

IsNextMonth: IsNextMonth(DateTime)

Returns True if the specified DateTime falls within the next month.

Example: IsNextMonth([OrderDate])

 

 

IsNextYear: IsNextYear(DateTime)
Returns True if the specified DateTime falls within the next year.

Example: IsNextYear([OrderDate])

 

 

IsNovember: IsNovember(DateTime)
Returns True if the specified DateTime falls within November.

Example: IsNovember([OrderDate])

 

 

IsOctober: IsOctober(DateTime)
Returns True if the specified DateTime falls within October.

Example: IsOctober([OrderDate])

 

 

IsSeptember: IsSeptember(DateTime)
Returns True if the specified DateTime falls within September.

Example: IsSeptember([OrderDate])

 

 

IsThisMonth: IsThisMonth(DateTime)
Returns True if the specified DateTime falls within the current month.

Example: IsThisMonth([OrderDate])

 

 

IsThisWeek: IsThisWeek(DateTime)
Returns True if the specified DateTime falls within the current week.

Example: IsThisWeek([OrderDate])

 

 

IsThisYear: IsThisYear(DateTime)
Returns True if the specified DateTime falls within the current year.

Example: IsThisYear([OrderDate])

 

 

IsYearToDate: IsYearToDate(DateTime)
Returns True if the specified DateTime falls within the year-to-date period (starting from the first day of the current year and continuing up to the current date).

Example: IsYearToDate([OrderDate])

 

 

LocalDateTimeDayAfterTomorrow: LocalDateTimeDayAfterTomorrow()
Returns a date-time value corresponding to the day after Tomorrow.

Example: AddDays(LocalDateTimeDayAfterTomorrow(), 1)

 

 

LocalDateTimeLastMonth: LocalDateTimeLastMonth()
Returns a date-time value corresponding to the first day of the previous month.

Example: AddMonths(LocalDateTimeLastMonth(), 1)

 

 

LocalDateTimeLastWeek: LocalDateTimeLastWeek()
Returns a DateTime value corresponding to the first day of the previous week.

Example: AddDays(LocalDateTimeLastWeek(), 1)

 

 

LocalDateTimeLastYear: LocalDateTimeLastYear()
Returns a date-time value corresponding to the first day of the previous year.

Example: AddMonths(LocalDateTimeLastYear(),1)

 

 

LocalDateTimeNextMonth: LocalDateTimeNextMonth()
Returns a DateTime value corresponding to the first day of next month.

Example: AddMonths(LocalDateTimeNextMonth(),1)

 

 

LocalDateTimeNextWeek: LocalDateTimeNextWeek()
Returns a DateTime value corresponding to the first day of the following week.

Example: AddDays(LocalDateTimeNextWeek(),1)

 

 

LocalDateTimeNextYear: LocalDateTimeNextYear()
Returns a DateTime value corresponding to the first day of the following year.

Example: AddDays(LocalDateTimeNextYear(),1)

 

 

LocalDateTimeNow: LocalDateTimeNow()
Returns a DateTime value corresponding to the current moment in time.

Example: AddDays(LocalDateTimeNow(),1)

 

 

LocalDateTimeThisMonth: LocalDateTimeThisMonth()
Returns a DateTime value corresponding to the first day of the current month.

Example: AddDays(LocalDateTimeThisMonth(),1)

 

 

LocalDateTimeThisWeek: LocalDateTimeThisWeek()
Returns a DateTime value corresponding to the first day of the current week.

Example: AddDays(LocalDateTimeThisWeek(),1)

 

 

LocalDateTimeThisYear: LocalDateTimeThisYear()
Returns a DateTime value corresponding to the first day of the current year.

Example: AddDays(LocalDateTimeThisYear(),1)

 

 

LocalDateTimeToday: LocalDateTimeToday()
Returns a DateTime value corresponding to Today.

Example: AddMinutes(LocalDateTimeToday(),60)

 

 

LocalDateTimeTomorrow: LocalDateTimeTomorrow()
Returns a date-time value corresponding to Tomorrow.

Example: AddDays(LocalDateTimeTomorrow(),1)

 

 

LocalDateTimeTwoMonthsAway: LocalDateTimeTwoMonthsAway()
Returns a date-time value corresponding to the first day of the month after next.

Example: AddMonths(LocalDateTimeTwoMonthsAway(),1)

 

 

LocalDateTimeTwoWeeksAway: LocalDateTimeTwoWeeksAway()
Returns a date-time value corresponding to the first day of the week that is after next week.

Example: AddDays(LocalDateTimeTwoWeeksAway(),1)

 

 

LocalDateTimeTwoYearsAway: LocalDateTimeTwoYearsAway()
Returns a date-time value corresponding to the first day of the year after next.

Example: AddDays(LocalDateTimeTwoYearsAway(),1)

 

 

LocalDateTimeYearBeforeToday: LocalDateTimeYearBeforeToday()
Returns a date-time value corresponding to the day one year ago.

Example: AddDays(LocalDateTimeYearBeforeToday(),1)

 

 

LocalDateTimeYesterday: LocalDateTimeYesterday()
Returns a date-time value corresponding to Yesterday.

Example: AddDays(LocalDateTimeYesterday(),1)

 

 

Now: Now()
Returns the current system date and time.

Example: AddDays(Now(),7)

 

 

Today: Today()
Returns the current date. Regardless of the actual time, this function returns midnight of the current date.

Example: AddDays(Today(),7)

 

 

UtcNow: UtcNow()
Returns the current system date and time, expressed as Coordinated Universal Time (UTC).

Example: AddDays(UtcNow(),1)

 

 

IsSameDay: IsSameDay(DateTime, DateTime)
Returns True if the specified DateTime values fall within the same day.

Example: IsSameDay([OrderDate], [ShippedDate])

 

Logical Functions

For a more in-depth guide on Logical Functions click here.

Iif: Iif(FirstClause, FirstClauseTrueValue, ..., LastClause, LastClauseTrueValue, FalseValue)
Example 1: Iif(?ViewParameter_FilterBy = 'Contact Name', [ContactName], [City])

Example 2: IIf(?ViewParameter_FilterBy = 'Contact Name', [ContactName], Iif(?ViewParameter_FilterBy = 'City', [City], [OrderID])))
Returns either a value defined for the first Boolean condition that is True, or the last value if none of these conditions is True.

This function can accept any number of arguments.

 

IsNull: IsNull(Value)
Returns True if the specified Value is NULL.

Example: IsNull([OrderDate])

 

 

IsNull: IsNull(Value1, Value2)
Returns the Value2 if the first operand is null, otherwise, Value1 is returned.

Example: IsNull([ShippedDate], [RequiredDate])

 

 

IsNullOrEmpty: IsNullOrEmpty(String)
Returns True if the specified String object is NULL or an empty string; otherwise, False is returned.

Example: IsNullOrEmpty([ProductName])

 

Math Functions

Abs: Abs(Value)
Returns the absolute, positive value of the given Value.

Example: Abs(1 - [Discount])

 

 

Acos: Acos(Value)
Returns the arccosine of a number (the angle, in radians, whose cosine is the given float expression).

Example: Acos([Value])

 

 

Asin: Asin(Value)
Returns the arcsine of a Value (the angle, in radians, whose sine is the given float expression).

Example: Asin([Value])

 

 

Atn: Atn(Value)
Returns the arctangent of a Value (the angle, in radians, whose tangent is the given float expression).

Example: Atn([Value])

 

 

Atn: Atn(Value1, Value2)
Returns the angle whose tangent is the quotient of two specified Values, in radians.

Example: Atn([Value1], [Value2])

 

 

BigMul: BigMul(Value1, Value2)
Returns an Int64 containing the full product of two specified 32-bit numbers.

 

Example: BigMul([Amount], [Quantity])

 

 

Ceiling: Ceiling(Value)
Returns the smallest integer that is greater than or equal to the given numeric expression.

Example: Ceiling([Value])

 

 

Cos: Cos(Value)
Returns the cosine of the angle defined in radians.

Example: Cos([Value])

 

 

Cosh: Cosh(Value)
Returns the hyperbolic cosine of the angle defined in radians.

Example: Cosh([Value])

 

 

Exp: Exp(Value)
Returns the exponential value of the given float expression.

Example: Exp([Value])

 

 

Floor: Floor(Value)
Returns the largest integer less than or equal to the given numeric expression.

Example: Floor([Value])

 

 

Log: Log(Value)
Returns the natural logarithm of a specified number.

Example: Log([Value])

 

 

Log: Log(Value, Base)
Returns the logarithm of a specified number Value in a specified Base.

Example: Log([Value], 2)

 

 

Log10: Log(Value)
Returns the base 10 logarithm of a specified number Value.

Example: Log10([Value])

 

 

Max: Max(Value1, Value2)
Returns the highest maximum value from the specified values.

Example: Max([Value], [Value2])

 

 

Min: Min(Value1, Value2)
Returns the lowest minimum value from the specified values.

Example: Min(Value1, Value2)

 

 

Power: Power(Value, Power)
Returns a specified number Value, raised to a specified Power.

Example: Power([Value], 3)

 

 

Rnd: Rnd()
Returns a random number that is less than 1, but greater than or equal to zero.

Example: Rnd()*100

 

 

Round: Round(Value)
Rounds the given Value to the nearest integer.

Example: Round([Value])

 

 

Round: Round(Value, Precision)
Rounds the given Value to the nearest integer, or to a specified Precision.

Example: Round([Value], 5)

 

 

Sign: Sign(Value)
Returns the positive (+1), zero (0), or negative (-1) sign of the given Value.

Example: Sign([Value])

 

 

Sin: Sin(Value)
Returns the sine of the angle, defined in radians.

Example: Sinh([Value])

 

 

Sinh: Sinh(Value)
Returns the hyperbolic sine of the angle defined in radians.

Example: 

 

 

Sqr: Sqr(Value)
Returns the square root of a given number Value.

Example: Sqr([Value])

 

 

Tan: Tan(Value)
Returns the tangent of the angle defined in radians.

Example: Tan([Value])

 

 

Tanh: Tanh(Value)
Returns the hyperbolic tangent of the angle defined in radians.

Example: Tanh([Value])

 

 

ToDecimal: ToDecimal(Value)
Converts Value to an equivalent decimal number.

Example: ToDecimal([Freight])

 

 

ToDouble: ToDouble(Value)
Converts Value to an equivalent 64-bit double-precision floating-point number.

Example: ToDouble([Freight])

 

 

ToFloat: ToFloat(Value)
Converts Value to an equivalent 32-bit single-precision floating-point number.

Example: ToFloat([Freight])

 

 

ToInt: ToInt(Value)
Converts Value to an equivalent 32-bit signed integer.

Example: ToInt([Freight])

 

 

ToLong: ToLong(Value)
Converts Value to an equivalent 64-bit signed integer.

Example: ToLong([Freight])

 

String Functions

Ascii: Ascii(String)
Returns the ASCII code value of the leftmost character in a character expression.

Example: Ascii('a')

 

 

Char: Char(Number)
Converts the specified ASCII integer Number to a character.

Example: Char(65)

 

 

CharIndex: CharIndex(String1, String2)
Returns the starting position of String1 within String2, beginning from the zero character position to the end of a string.

Example: CharIndex('r', 'Bridgeworks')

 

 

CharIndex: CharIndex(String1, String2, StartLocation)
Returns the starting position of String1 within String2, beginning from the StartLocation character position to the end of a string.

Example: CharIndex('r', 'Bridgeworks', 2)

 

 

Concat: Concat(String1, ... ,StringN)
Returns a string value containing the concatenation of the current string with any additional strings.

Example: Concat('A', ')', [ProductName])

 

 

Contains: Contains(String, SubString)
Returns True if SubString occurs within String; otherwise, False is returned.

Example: Contains([ContactName], '-')

 

 

EndsWith: EndsWith(String, EndString)
Returns True if the end of String matches EndString; otherwise, False is returned.

Example: EndsWith('Hello World!', 'World!')

 

 

Insert: Insert(String1, StartPosition, String2)
Inserts String2 into String1 at the position specified by StartPositon.

Example: Insert('Hello World', 11, '!')

 

 

Len: Len(String)
Returns an integer containing either the number of characters in a String or the nominal number of bytes required to store a variable.

Example: Len([ContactName])

 

 

Lower: Lower(String)
Returns String in lowercase.

Example: Lower('RETURNS STRING IN LOWERCASE')

 

 

 

PadLeft: PadLeft(String, Length)
Left-aligns characters in the defined String, padding its left side with white space up to a specified total Length.

Example: PadLeft([Name], 30)

 

 

PadLeft: PadLeft(String, Length, Char)
Left-aligns characters in the defined String, padding its right side with the specified Char up to a specified total Length.

Example: PadLeft([Name], 30, '<')

 

 

PadRight: PadRight(String, Length)
Right-aligns characters in the defined String, padding its right side with white space up to a specified total Length.

Example: PadRight([Name], 30)

 

 

PadRight: PadRight(String, Length, Char)
Right-aligns characters in the defined String, padding its right side with the specified Char up to a specified total Length.

Example: PadRight([Name], 30, '>')

 

 

Remove: Remove(String, StartPosition)
Deletes all characters from specified String, beginning at a specified StartPosition.

Example: Remove('Contact Name', 3)

 

 

Remove: Remove(String, StartPosition, Length)
Deletes a specified Length of characters from specified String, beginning at a specified StartPosition.

Example: Remove('Contact Name', 0, 7)

 

 

Replace: Replace(String, SubString2, String3)
Returns a copy of String1, in which SubString2 has been replaced with String3.

Example: Replace([Name], 'The ', '')

 

 

Reverse: Reverse(String)
Reverses the order of elements within a String.

Example: Reverse('This will be reversed')

 

 

StartsWith: StartsWith(String, StartString)
Returns True if the beginning of String matches StartString; otherwise, False is returned.

Example: StartsWith([Description], 'IN')

 

 

Substring: Substring(String, StartPosition)
Retrieves a substring from String. The substring starts at StartPosition.

Example: Substring([Description],2)

 

 

Substring: Substring(String, StartPosition, Length)
Retrieves a substring from String. The substring starts at StartPosition and has the specified Length.

Example: Substring([Description], 2, 3)

 

 

ToStr: ToStr(Value)
Returns a string representation of a Value.

Example: ToStr([ID])

 

 

Trim: Trim(String)
Removes all leading and trailing SPACE characters from String.

Example: Trim([ProductName])

 

 

Upper: Upper(String)
Returns String in uppercase.

Example: Upper('returns string in uppercase')

 

 

NewLine: NewLine()
Returns the newline string defined for this environment.

 

 

FormatString: FormatString(Format, Value1, ... , ValueN)
Returns the specified string with formatted field values.

This function can accept any number of arguments.

Example: FormatString('{0} - {1:c}', [Column1], [Column2])

 

 

Rgb: Rgb(Red, Green, Blue)
Returns a string defining a color using the Red, Green, and Blue color channel values.

 

 

Argb: Argb(Alpha, Red, Green, Blue)
Returns a string defining a color using the Alpha, Red, Green, and Blue color channel values.

 

 

Join: Join(Collection)
Concatenates elements of the specified Collection, inserting comma between each element.

Example: Join([ContactName])

 

 

Join: Join(Collection, Separator)
Concatenates elements of the specified Collection, using the specified Separator between each element.
Example: Join([ContactName],'-')