Expression Editor Function References

Article Goal

This article provides an organized reference for commonly used expression functions in Finished Reports. Functions are grouped by category and include syntax, examples, and plain-language explanations to help you quickly understand how and when to use each one.

For deeper explanations, refer to the linked in-depth guides where available.

Aggregate Functions

Aggregate functions operate on collections of data and return a single calculated value.

Avg

Syntax:
[Collection][Condition].Avg(Expression)

Example:
[Products][Discontinued == False].Avg([UnitsInStock])

Description:
Returns the average of all expression values in the collection. The condition is optional.

Count

Syntax:
[Collection][Condition].Count()

Example:
[Products][Discontinued == False].Count()

Description:
Returns the number of entries in the collection. The condition is optional.

Exists

Syntax:
[Collection][Condition].Exists()

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

Description:
Returns True if at least one item in the collection meets the condition.

Max

Syntax:
[Collection][Condition].Max(Expression)

Example:
[Products][Discontinued == False].Max([UnitPrice])

Description:
Returns the highest expression value in the collection.

Min

Syntax:
[Collection][Condition].Min(Expression)

Example:
[Products][Discontinued == False].Min([UnitPrice])

Description:
Returns the lowest expression value in the collection.

Sum

Syntax:
[Collection][Condition].Sum(Expression)

Example:
[Products][Discontinued == False].Sum([UnitsInStock])

Description:
Returns the total sum of expression values in the collection.

Single

Syntax:
[Collection][Condition].Single(Expression)

Example:
[Products][UnitPrice > 200].Single('True')

Description:
Returns the expression if exactly one object in the collection meets the condition.

DateTime Functions

DateTime functions allow you to manipulate, compare, and evaluate date and time values.

Date Adjustment Functions

These functions add or subtract time from a DateTime value.

• AddDays(DateTime, DaysCount)
  Example: AddDays([OrderDate], 30)

• AddHours(DateTime, HoursCount)
  Example: AddHours([StartTime], 2)

• AddMinutes(DateTime, MinutesCount)
  Example: AddMinutes([StartTime], 30)

• AddSeconds(DateTime, SecondsCount)
  Example: AddSeconds([StartTime], 60)

• AddMonths(DateTime, MonthsCount)
  Example: AddMonths([OrderDate], 1)

• AddYears(DateTime, YearsCount)
  Example: AddYears([OrderDate], -10)

Date Difference Functions

These functions return the number of time boundaries between two dates.

• DateDiffDay(StartDate, EndDate)

• DateDiffHour(StartDate, EndDate)

• DateDiffMinute(StartDate, EndDate)

• DateDiffSecond(StartDate, EndDate)

• DateDiffMonth(StartDate, EndDate)

• DateDiffYear(StartDate, EndDate)

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

Date Component Functions

Extract specific parts of a DateTime value.

• GetYear(DateTime)

• GetMonth(DateTime)

• GetDay(DateTime)

• GetHour(DateTime)

• GetMinute(DateTime)

• GetSecond(DateTime)

• GetDayOfWeek(DateTime)

• GetDayOfYear(DateTime)

Example:
GetMonth([OrderDate])

Date Evaluation Functions

Return True or False based on date conditions.

Examples include:

• IsThisMonth(DateTime)

• IsLastYear(DateTime)

• IsNextWeek(DateTime)

• IsYearToDate(DateTime)

Example:
IsThisYear([OrderDate])

System Date Functions

Return system-based or relative date values.

• Today()

• Now()

• UtcNow()

• LocalDateTimeNow()

• LocalDateTimeTomorrow()

• LocalDateTimeYesterday()

Example:
AddDays(Today(), 7)

Logical Functions

Logical functions evaluate conditions and return values based on Boolean logic.

Iif

Syntax:
Iif(Condition, TrueValue, FalseValue)

Example:
Iif(?ViewParameter_FilterBy = 'City', [City], [OrderID])

Description:
Returns the value associated with the first True condition.

IsNull

Syntax:
IsNull(Value)
IsNull(Value1, Value2)

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

Description:
Checks for NULL values or provides a fallback value.

IsNullOrEmpty

Syntax:
IsNullOrEmpty(String)

Example:
IsNullOrEmpty([ProductName])

Math Functions

Math functions perform numeric calculations.

Common examples include:

• Abs(Value)

• Round(Value, Precision)

• Power(Value, Power)

• Sqr(Value)

• Max(Value1, Value2)

• Min(Value1, Value2)

• Rnd()

Example:
Round([Value], 2)

String Functions

String functions manipulate text values.

Examples include:

• Concat(String1, String2)

• Substring(String, Start, Length)

• Replace(String, Old, New)

• Trim(String)

• Upper(String)

• Lower(String)

• FormatString(Format, Values...)

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

Color & Collection Functions

Color Functions

• Rgb(Red, Green, Blue)

• Argb(Alpha, Red, Green, Blue)

Collection Functions

• Join(Collection)

• Join(Collection, Separator)

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

Article Summary

This article provides a structured reference for expression functions used in Finished Reports, including aggregate, DateTime, logical, math, string, color, and collection functions. Each section includes syntax and examples to help you quickly understand how to apply these functions in real-world reporting scenarios.