Skip to main content

Expression Editor Function References

Garrick Guillory
Updated

This article provides a reference for commonly used expression functions in Finished Reports. Functions are grouped by category and include syntax, examples, and short descriptions to help you identify the right function for a report expression.

Aggregate Functions

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

For more detailed examples of group-level expressions, see:

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

These 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

These 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

These 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)

or:

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)

For a detailed example of comparing multiple values with nested Min() functions, see Using Nested Min Expressions in Finished Reports.

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])

For more information about formatting expression results, see Using Format Strings in the Finished Reports Expression Editor.

Color & Collection Functions

Color Functions

  • Rgb(Red, Green, Blue)
  • Argb(Alpha, Red, Green, Blue)

Collection Functions

  • Join(Collection)
  • Join(Collection, Separator)

Example:

Join([ContactName], '-')

For a detailed example of combining grouped values into a single string, see Using the Join Aggregate Function in Finished Reports.

Related Articles

Related articles

Comments

0 comments

Please sign in to leave a comment.