Functions in Expressions

• Jun 20, 2024
• 29 minutes to read

This topic lists functions that can be used in an expression.

Aggregate Functions

Avg(Value)

Evaluates the average of the values in the collection.

[Products].Avg([UnitPrice])

Count()

Returns the number of objects in a collection.

[Products].Count()

Exists()

Determines whether the object exists in the collection.

[Categories][[CategoryID] == 7].Exists()

Join()

Concatenates all Expression values in the Collection based on the specified Condition (optional) into a single string separated by the specified Separator (optional). If you do not specify a Separator, the function uses a comma.

The function has the following overloads:

[Collection][Condition].Join(Expression)

[Collection][Condition].Join(Expression, Separator)

Example:

The following expression concatenates CompanyName field values within a report grouped by the CategoryID field into a single string separated by a semicolon:

[][[CategoryID] == [^.CategoryID]].Join([CompanyName], ';')

Max(Value)

Returns the maximum expression value in a collection.

[Products].Max([UnitPrice])

Min(Value)

Returns the minimum expression value in a collection.

[Products].Min([UnitPrice])

Single()

Returns an object if it is the only element in a collection.

[Accounts].Single() is not null

Single(Expression)

You can pass an expression as a parameter: [Collection][Condition].Single(Expression).

This function returns the Expression if the Collection contains only one object that meets the specified Condition (optional).

[Collection].Single([Property1]) — returns the found object’s property value.

Sum(Value)

Returns the sum of all expression values in the collection.

[Products].Sum([UnitsInStock])

View Example: How to use aggregate functions in Calculated Field expressions

Date and Time Functions

AddDays(DateTime, DaysCount)

Returns a DateTime/DateOnly value that is the specified number of days before or after a specified start date.

The operands are:

1 - the DateTime/DateOnly value that is the start date.

2 - the Double value that is the number of days before or after the start date. This number can be negative or positive. Its decimal part is a fraction of a day.

Example: AddDays([OrderDate], 30)

AddHours(DateTime, HoursCount)

Returns a DateTime/TimeOnly value that is the specified number of hours before or after a specified start date/time.

The operands are:

1 - the DateTime/TimeOnly value that is the start date.

2 - the Double value that is the number of hours before or after the start date. This number can be negative or positive. Its decimal part is a fraction of an hour.

Example: AddHours([StartTime], 2)

AddMilliSeconds(DateTime, MilliSecondsCount)

Returns a DateTime/TimeOnly value that is the specified number of milliseconds before or after a specified start date/time.

The operands are:

1 - the DateTime/TimeOnly value that is the start date.

2 - the Double value that is the number of milliseconds before or after the start date. This number can be negative or positive. Its decimal part is a fraction of a millisecond.

Example: AddMilliSeconds(([StartTime], 5000))

AddMinutes(DateTime, MinutesCount)

Returns a DateTime/TimeOnly value that is the specified number of minutes before or after a specified start date/time.

The operands are:

1 - the DateTime/TimeOnly value that is the start date.

2 - the Double value that is the number of minutes before or after the start date. This number can be negative or positive. Its decimal part is a fraction of a minute.

Example: AddMinutes([StartTime], 30)

AddMonths(DateTime, MonthsCount)

Returns a DateTime/DateOnly value that is the specified number of months before or after a specified start date.

The operands are:

1 - the DateTime/DateOnly value that is the start date.

2 - the integer value that is the number of months before or after the start date. This number can be negative or positive.

Example: AddMonths([OrderDate], 1)

AddSeconds(DateTime, SecondsCount)

Returns a DateTime/TimeOnly value that is the specified number of seconds before or after a specified start date/time.

The operands are:

1 - the DateTime/TimeOnly value that is the start date.

2 - the Double value that is the number of seconds before or after the start date. This number can be negative or positive. Its decimal part is a fraction of a second.

Example: AddSeconds([StartTime], 60)

AddTicks(DateTime, TicksCount)

Returns a DateTime value that is the specified number of ticks before or after a specified start date.

The operands are:

1 - the DateTime value that is the start date.

2 - the integer number that is the number of 100-nanosecond ticks. This number can be negative or positive.

Example: AddTicks([StartTime], 5000)

AddTimeSpan(DateTime, TimeSpan)

Returns a DateTime value that differs by a specified amount of time from a specified date.

The operands are:

1 - the DateTime value that is the start date.

2 - the TimeSpan object that is the time period before or after the start date.

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

AddYears(DateTime, YearsCount)

Returns a DateTime/DateOnly value that is the specified number of years before or after a specified start date.

The operands are:

1 - the DateTime/DateOnly value that is the start date.

2 - the integer value that is the number of years before or after the start date. This number can be negative or positive.

Example: AddYears([EndDate], -1)

DateDiffDay(startDate, endDate)

Returns the number of day boundaries between the specified dates.

The operands are:

1 - the DateTime/DateOnly value that is the start date.

2 - the DateTime/DateOnly value that is the end date.

Example: DateDiffDay([StartTime], Now())

DateDiffHour(startDate, endDate)

Returns the number of hour boundaries between the specified dates/times.

The operands are:

1 - the DateTime/TimeOnly value that is the start date.

2 - the DateTime/TimeOnly value that is the end date.

Example: DateDiffHour([StartTime], Now())

DateDiffMilliSecond(startDate, endDate)

Returns the number of millisecond boundaries between the specified dates/times.

The operands are:

1 - the DateTime/TimeOnly value that is the start date.

2 - the DateTime/TimeOnly value that is the end date.

Example: DateDiffMilliSecond([StartTime], Now())

DateDiffMinute(startDate, endDate)

Returns the number of minute boundaries between the specified dates/times.

The operands are:

1 - the DateTime/TimeOnly value that is the start date.

2 - the DateTime/TimeOnly value that is the end date.

Example: DateDiffMinute([StartTime], Now())

DateDiffMonth(startDate, endDate)

Returns the number of month boundaries between the specified dates.

The operands are:

1 - the DateTime/DateOnly value that is the start date.

2 - the DateTime/DateOnly value that is the end date.

Example: DateDiffMonth([StartTime], Now())

DateDiffSecond(startDate, endDate)

Returns the number of second boundaries between the specified dates/times.

The operands are:

1 - the DateTime/TimeOnly value that is the start date.

2 - the DateTime/TimeOnly value that is the end date.

Example: DateDiffSecond([StartTime], Now())

DateDiffTick(startDate, endDate)

Returns the number of tick boundaries between the specified dates.

The operands are:

1 - the DateTime value that is the start date.

2 - the DateTime value that is the end date.

Example: DateDiffTick([StartTime], Now())

DateDiffYear(startDate, endDate)

Returns the number of year boundaries between the specified dates.

The operands are:

1 - the DateTime/DateOnly value that is the start date.

2 - the DateTime/DateOnly value that is the end date.

Example: DateDiffYear([StartTime], Now())

DateTimeFromParts(Year, Month, Day, Hour, Minute, Second, Millisecond)

Returns a date value constructed from the specified Year, Month, Day, Hour, Minute, Second, and Millisecond.

The operands are:

1 - (Required) - an integer value that is the full year value (four digits, century included).

2 - (Required) - an integer value that is the month number (1-12).

3 - (Required) - an integer value that is the day of the month (1-31).

4 - (Optional) - an hour value in 24-hour format (0-23).

5 - (Optional) - a minute value (0-59).

6 - (Optional) - a second value (0-59).

7 - (Optional) - a millisecond value.

Example: DateTimeFromParts(2018, 5, 5, 20)

GetDate(DateTime)

Returns the date part of the specified date.

The operand must be of the DateTime type.

The return value is a DateTime object with the same date part. The time part is 00:00:00.

Example: GetDate([OrderDateTime])

GetDay(DateTime)

Gets the day of the month in the specified date.

The operand must be of the DateTime/DateOnly type.

The return value is an integer in the range between 1 and 31.

Example: GetDay([OrderDate])

GetDayOfWeek(DateTime)

Gets the day of the week in the specified date.

The operand must be of the DateTime/DateOnly type.

The return value is an integer value of the DayOfWeek enumeration. It does not depend on the current culture.

Example: GetDayOfWeek([OrderDate])

GetDayOfYear(DateTime)

Gets the day of the year in the specified date.

The operand must be of the DateTime/DateOnly type.

The return value is an integer in the range between 1 and 366.

Example: GetDayOfYear([OrderDate])

GetHour(DateTime)

Returns the hours value in the specified date/time.

The operand must be of the DateTime/TimeOnly type.

The return value is an integer in the range between 0 and 23.

Example: GetHour([StartTime])

GetMilliSecond(DateTime)

Returns the milliseconds value in the specified date/time.

The operand must be of the DateTime/TimeOnly type.

The return value is an integer in the range between 0 and 999.

Example: GetMilliSecond([StartTime])

GetMinute(DateTime)

Returns the minutes value in the specified date/time.

The operand must be of the DateTime/TimeOnly type.

The return value is an integer in the range between 0 and 59.

Example: GetMinute([StartTime])

GetMonth(DateTime)

Gets the month in the specified date.

The operand must be of the DateTime/DateOnly type.

The return value is an integer and depends on the current calendar.

Example: GetMonth([StartTime])

GetSecond(DateTime)

Returns the seconds value in the specified date/time.

The operand must be of the DateTime/TimeOnly type.

The return value is an integer in the range between 0 and 59.

Example: GetSecond([StartTime])

GetTimeOfDay(DateTime)

Gets the time part of the specified date.

The operand must be of the DateTime type.

The return value is the Int64 object that is the number of 100-nanosecond ticks that have elapsed since midnight.

Example: GetTimeOfDay([StartTime])

GetYear(DateTime)

Gets the year in the specified date.

The operand must be of the DateTime/DateOnly type.

The return value is an integer in the range between 1 and 9999.

Example: GetYear([StartTime])

InDateRange(DateTime1, DateTime2, DateTime3)

Returns True if the date part of the first operand is greater than or equal to the date part of the second operand and less than or equal to the date part of the third operand. Otherwise, returns False. If operands cannot be compared, returns null.

The operands are:

1 - the DateTime/DateOnly value that is the date to test.

2 - the DateTime/DateOnly value that is the start date.

3 - the DateTime/DateOnly value that is the end date.

Example: InDateRange([OrderDate], #2022-01-01 00:00:00#, #2022-12-31 23:59:59#)

IsApril(DateTime)

Returns True if the specified date falls within April. Example: IsApril([OrderDate])

IsAugust(DateTime)

Returns True if the specified date falls within August. Example: IsAugust([OrderDate])

IsDecember(DateTime)

Returns True if the specified date falls within December. Example: IsDecember([OrderDate])

IsFebruary(DateTime)

Returns True if the specified date falls within February. Example: IsFebruary([OrderDate])

IsJanuary(DateTime)

Returns True if the specified date falls within January. Example: IsJanuary([OrderDate])

IsJuly(DateTime)

Returns True if the specified date falls within July. Example: IsJuly([OrderDate])

IsJune(DateTime)

Returns True if the specified date falls within June. Example: IsJune([OrderDate])

IsLastMonth(DateTime)

Returns True if the specified date falls within the previous month. Example: IsLastMonth([OrderDate])

IsLastYear(DateTime)

Returns True if the specified date falls within the previous year. Example: IsLastYear([OrderDate])

IsMarch(DateTime)

Returns True if the specified date falls within March. Example: IsMarch([OrderDate])

IsMay(DateTime)

Returns True if the specified date falls within May. Example: IsMay([OrderDate])

IsNextMonth(DateTime)

Returns True if the specified date falls within the next month. Example: IsNextMonth([OrderDate])

IsNextYear(DateTime)

Returns True if the specified date falls within the next year. Example: IsNextYear([OrderDate])

IsNovember(DateTime)

Returns True if the specified date falls within November. Example: IsNovember([OrderDate])

IsOctober(DateTime)

Returns True if the specified date falls within October. Example: IsOctober([OrderDate])

IsSameDay(DateTime)

Returns True if the specified date/time values fall within the same day.

IsSameDay([OrderDate])

IsSeptember(DateTime)

Returns True if the specified date falls within September. Example: IsSeptember([OrderDate])

IsThisMonth(DateTime)

Returns True if the specified date falls within the current month.

To create the IsThisMonth operator using the CriteriaOperator.Parse method, use the following syntax: CriteriaOperator.Parse(“IsThisMonth(StartDate)”).

Example: IsThisMonth([OrderDate])

IsThisWeek(DateTime)

Returns True if the specified date falls within the current week. Example: IsThisWeek([OrderDate])

IsYearToDate(DateTime)

Returns True if the specified date falls within the period that starts from the first day of the current year and continues until the current date (including the current date).

IsYearToDate([OrderDate])

IsThisYear(DateTime)

Returns True if the specified date falls within the current year. Example: IsThisYear([OrderDate])

LocalDateTimeDayAfterTomorrow()

Returns the DateTime value that has the date part that is two days after the current date, and the time part of 00:00:00. Example: AddDays(LocalDateTimeDayAfterTomorrow(), 5)

LocalDateTimeLastMonth()

Returns the DateTime value that has the date part that is one month before the current date, and the time part of 00:00:00. Example: AddMonths(LocalDateTimeLastMonth(), 5)

LocalDateTimeLastWeek()

Returns the DateTime value that has the date part that is 7 days before the start of the current week, and the time part of 00:00:00. Example: AddDays(LocalDateTimeLastWeek(), 5)

LocalDateTimeLastYear()

Returns the DateTime value that has the date part that is the first day of the previous year, and the time part of 00:00:00. Example: AddYears(LocalDateTimeLastYear(), 5)

LocalDateTimeNextMonth()

Returns the DateTime value that has the date part that is the first day of the next month, and the time part of 00:00:00. Example: AddMonths(LocalDateTimeNextMonth(), 5)

LocalDateTimeNextWeek()

Returns the DateTime value that has the date part that is 7 days after the start of the current week, and the time part of 00:00:00. Example: AddDays(LocalDateTimeNextWeek(), 5)

LocalDateTimeNextYear()

Returns the DateTime value with the date part that corresponds to the first day of the next year, and the time part of 00:00:00. Example: AddYears(LocalDateTimeNextYear(), 5)

LocalDateTimeNow()

Returns the DateTime value that is the current moment in time. Example: AddDays(LocalDateTimeNow(), 5)

LocalDateTimeThisMonth()

Returns the DateTime value with the date part that is the first day of the current month, and the time part of 00:00:00. Example: AddMonths(LocalDateTimeThisMonth(), 5)

LocalDateTimeThisWeek()

Returns the DateTime value with the date part that is the first day of the current week, and the time part of 00:00:00. Example: AddDays(LocalDateTimeThisWeek(), 5)

LocalDateTimeThisYear()

Returns the DateTime value with the date part that is the first day of the current year, and the time part of 00:00:00. Example: AddYears(LocalDateTimeThisYear(), 5)

LocalDateTimeToday()

Returns the DateTime value with the date part that is the start of the current day, and the time part of 00:00:00. Example: AddDays(LocalDateTimeToday(), 5)

LocalDateTimeTomorrow()

Returns the DateTime value with the date part that is the next day, and the time part of 00:00:00. Example: AddDays(LocalDateTimeTomorrow(), 5)

LocalDateTimeTwoMonthsAway()

Returns the DateTime value with the date part that is the first day of the month after the next month, and the time part of 00:00:00. Example: AddMonths(LocalDateTimeTwoMonthAway(), 5)

LocalDateTimeTwoWeeksAway()

Returns the DateTime value with the date part that is the first day of the week after the next week, and the time part of 00:00:00. Example: AddDays(LocalDateTimeTwoWeeksAway(), 5)

LocalDateTimeTwoYearsAway()

Returns the DateTime value with the date part that is the first day of the year after the next year, and the time part of 00:00:00. Example: AddYears(LocalDateTimeTwoYearsAway(), 5)

LocalDateTimeYearBeforeToday()

Returns the DateTime value with the date part that is the date one year ago, and the time part of 00:00:00. Example: AddYears(LocalDateTimeYearBeforeToday(), 5)

LocalDateTimeYesterday()

Returns the DateTime value with the date part that is the previous day, and the time part of 00:00:00. Example: AddDays(LocalDateTimeYesterday(), 5)

Now()

Returns the DateTime value that is the current date and time. Example: AddDays(Now(), 5)

Today()

Returns a DateTime value that is the current date. The time part is set to 00:00:00. Example: AddMonths(Today(), 1)

UtcNow()

Returns a DateTime object that is the current date and time in Universal Coordinated Time (UTC).

Example: AddDays(UtcNow(), 7)

Logical Functions

Iif(Expression1, True_Value1, …, ExpressionN, True_ValueN, False_Value)

Returns one of the specified values depending upon the values of logical expressions.

The function can take 2N+1 arguments (where N is the number of specified logical expressions):

• Each odd argument specifies a logical expression.

• Each even argument specifies the value that is returned if the previous expression evaluates to True.

• The last argument specifies the value that is returned if the previously evaluated logical expressions yield False.

• If you pass only one argument, the passed argument is returned.

• If you compare a 0 (zero) with a Null value, the expression evaluates to True.

Examples:

Iif(Name = 'Bob', 1, 0)

Iif(Name = 'Bob', 1, Name = 'Dan', 2, Name = 'Sam', 3, 0)

InRange(Value1, Value2, Value3)

Returns True if the first operand is greater than or equal to the second operand and less than or equal to the third operand. Otherwise, returns False. If operands cannot be compared, returns null.

The operands are:

1 - the DateTime value that is the value to test.

2 - the DateTime value that is the minimum value.

3 - the DateTime value that is the maximum value.

Example: InRange([ProductPrice], 10.00, 50.00)

IsNull(Value)

Compares the first operand with the NULL value.

This function requires one or two operands of the CriteriaOperator class. The returned value depends on the number of arguments.

If a single operand is passed, the function returns True if the operand is null; otherwise, False.

If two operands are passed, the function returns the second operand if the first operand is null. Otherwise, the function returns the first operand.

Example: IsNull([OrderDate])

IsNullOrEmpty(String)

Returns True if the specified value is null or an empty string. Otherwise, returns False.

Example: IsNullOrEmpty([ProductName])

Math Functions

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 arctangent (the inverse tangent function) of the quotient of the two specified numeric operands. The arctangent is the angle in the range -π/2 to π/2 radians.

The operands are:

1 - the y coordinate of a point in Cartesian coordinates (x, y).

2 - the x coordinate of a point in Cartesian coordinates (x, y).

If the specified operands cannot be converted to Double, the NotSupportedException is thrown.

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

BigMul(Value1, Value2)

Calculates the full product of two integer operands.

If the specified operands cannot be converted to Int32, the NotSupportedException is thrown.

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

Ceiling(Value)

Returns the smallest integral value greater than or equal to the specified numeric operand.

If the specified operand is not numeric, the NotSupportedException is thrown.

Example: 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 integral value less than or equal to the specified numeric operand.

If the specified operand is not numeric, the NotSupportedException is thrown.

Example: Floor([Value])

Log(Value)

Returns a specified number’s natural logarithm.

Log([Value])

Log(Value, Base)

Returns the logarithm of the specified numeric operand. The return value depends upon the number of operands.

If one operand is passed, the function returns the natural (base e) logarithm of a specified operand.

If two operands are passed, the function returns the logarithm of the specified operand to the specified base. The operands are:

1 - a number whose logarithm is to be calculated.

2 - the base of the logarithm.

If the operand cannot be converted to Double, the NotSupportedException is thrown.

The Log function reverses the FunctionOperatorType.Exp function. To calculate the base-10 logarithm, use the FunctionOperatorType.Log10 function.

Example: Log([Value], 2)

Log10(Value)

Returns the specified number’s base 10 logarithm.

Log10([Value])

Max(Value1, Value2)

Returns the larger of two numeric values. Example: Max([Value1], [Value2])

Min(Value1, Value2)

Returns the smaller of two numeric values. Example: Min([Value1], [Value2])

Power(Value, Power)

Returns a specified numeric operand raised to a specified power.

The operands are:

1 - the base number.

2 - the exponent to which the base number is raised.

If the operand cannot be converted to Double, the NotSupportedException is thrown.

The Power function reverses the FunctionOperatorType.Log or FunctionOperatorType.Log10 function. Use the FunctionOperatorType.Exp operand to calculate powers of the number e.

Example: 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 a specified numeric operand to the nearest integer or to a specified number of fractional digits.

The operands are:

1 - a value to round.

2 - (optional) the number of decimal places to which to round. 0 indicates that the first operand is rounded to the nearest integer.

Example: 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 numeric operand, in radians.

If the operand cannot be converted to Double, the NotSupportedException is thrown.

Example: Sin([Value])

Sinh(Value)

Returns the hyperbolic sine of the numeric operand, in radians.

If the operand cannot be converted to Double, the NotSupportedException is thrown.

Example: Sinh([Value])

Sqr(Value)

Returns the square root of a specified numeric operand.

If the operand cannot be converted to Double, the NotSupportedException is thrown.

Example: Sqr([Value])

Tan(Value)

Returns the tangent of the specified numeric operand that is an angle in radians.

If the operand cannot be converted to Double, the NotSupportedException is thrown.

Example: Tan([Value])

Tanh(Value)

Returns the hyperbolic tangent of a specified numeric operand that is an angle in radians.

If the specified operands cannot be converted to Double, the NotSupportedException is thrown.

Example: Tanh([Value])

ToDecimal(Value)

Converts a numeric operand to a decimal value. Example: ToDecimal([Value])

ToDouble(Value)

Converts a numeric operand to a double value. Example: ToDouble([Value])

ToFloat(Value)

Converts a numeric operand to a floating-point value. Example: ToFloat([Value])

ToInt(Value)

Converts a numeric operand to an integer value. Example: ToInt([Value])

ToLong(Value)

Converts a numeric operand to a long integer value. Example: ToLong([Value])

Reporting Functions

Argb(Alpha, Red, Green, Blue)

Returns a string that defines a color using the Alpha, Red, Green, and Blue color channel values.

Argb(1, 200, 30, 200)
Result:
‘1,200,30,200’

ConvertDataToEPC(BeneficiaryName, IBAN, BIC, TransferAmount, CreditorReference, RemittanceInformation, TransferPurpose, Message)

Converts the specified data elements into a formatted string for the EPC QR Code. The TransferPurpose and Message parameters are optional.

ConvertDataToEPC('Red Cross of Belgium', 'BE72000000001616', 'BPOTBEB1','20.0', '', 'Urgency fund', 'CHAR', 'Sample EPC QR code')

GetDisplayText(?parameterName)

Returns text displayed for a parameter’s lookup value if a parameter value source is a dynamic or static list. For parameters with an empty value source (parameters whose values are not selected from the list), this function returns the parameter value converted to a string.

Consider the paramCompany parameter defined as follows:

The following expression displays parameter values and their display values:

FormatString('ID: {0}, Display: {1}', Join(?paramCompany), GetDisplayText(?paramCompany))

Note the function Join used to get a multi-value parameter value as a comma-separated string.

The following image shows the Parameter Editor and the expression calculation result:

Rgb(Red, Green, Blue)

Returns a string that defines color using Red, Green, and Blue color channel values.

Rgb(30,200,150)
Result:
‘30,200,150’

CurrentRowIndexInGroup()

Returns the current row’s index within the group.

The following expression adds row indexes in the group:

CurrentRowIndexInGroup(0) + 1

GroupIndex(level)

Locates the parent group row at the specified nesting level and returns that row’s index.

The following expression displays indexes of root-level groups:

GroupIndex(1) + 1

NextRowColumnValue(columnName)

Obtains the next row and returns the value from the specified column.

PrevRowColumnValue(columnName)

Obtains the previous row and returns the value from the specified column.

String Functions

Ascii(String)

Returns the ASCII code of the first character in a string operand.

If the argument is an empty string, the null value is returned.

Example: Ascii('a')

Char(Number)

Converts a numeric operand to a Unicode character.

If the specified operand is not numeric, the NotSupportedException is thrown.

Example: Char(65) + Char(51)

CharIndex(String1, String2, Start, Length)

Returns the index of the first occurrence of a specified string within another string.

The operands are:

1 - a string that you want to find in another string.

2 - a string that contains the string you are searching for.

3 - (optional) an integer that specifies the zero-based index at which the search starts. If this operand is not specified, the search begins from the start of the string.

4 - (optional) an integer that specifies the number of characters to examine, starting from the specified position. If this operand is not specified, the search continues until the end of the string.

This function performs a word (case-sensitive and culture-sensitive) search using the current culture. If a specified substring is found, the function returns its index. Otherwise, -1 is returned.

Example: CharIndex('e', 'devexpress')

Concat(String1, … , StringN)

Concatenates the specified strings. Example: Concat('A', ')', [ProductName])

Contains(String1, SubString1)

Returns True if the specified string occurs within another specified string; otherwise, False.

The operands are:

1 - a string that contains the string you are searching for.

2 - a string that you want to find in another string.

Example: Contains([ProductName], 'dairy')

EndsWith(String1, SubString1)

Returns True if the end of one specified string matches another specified string; otherwise, False.

The operands are:

1 - a string that contains the string you are searching for.

2 - a string to compare to the substring at the end of another string.

To create the EndsWith operator using the CriteriaOperator.Parse method, use the following syntax: CriteriaOperator.Parse("EndsWith(String, SubString)").

Example: EndsWith([Description], 'The end.')

Insert(String1, StartPosition, String2)

Returns a new string in which a specified string is inserted at a specified index position into another specified string.

The operands are:

1 - the string into which another string should be inserted.

2 - the zero-based index position of the insertion.

3 - the string to insert.

Example: Insert([Name], 0, 'ABC-')

Len(Value)

Returns the length of the string specified by an operand.

To create the Len operator using the CriteriaOperator.Parse method, use the following syntax: CriteriaOperator.Parse("Len(Field1)").

Example: Len([Description])

Lower(String)

Converts all characters in a string operand to lowercase in an invariant culture. Example: Lower([ProductName])

PadLeft(String, Length, Char)

Returns a new string that pads the character in the specified string on the left with a specified Unicode character, for a specified total length.

The operands are:

1 - a string to be padded.

2 - the total number of characters in the resulting string, including padding characters.

3 - (optional) a Unicode padding character. If not specified, the space character is used for padding. If a string is passed as this operand, its first character is used for padding.

Example: PadLeft([Name], 30)

PadRight(String, Length, Char)

Returns a new string of a specified length in which the end of a specified string is padded with spaces or with a specified Unicode character.

The operands are:

1 - a string to be padded.

2 - the total number of characters in the resulting string, including padding characters.

3 - (optional) a Unicode padding character. If not specified, the space character is used for padding. If a string is passed as this operand, its first character is used for padding.

Example: PadRight([Name], 30)

Remove(String, StartPosition, Length)

Returns a new string with the specified number of characters in the specified string removed, starting at the specified position.

The operands are:

1 - the string that needs to be shortened.

2 - the zero-based index at which character removal starts.

3 - (optional) an integer that specifies the number of characters to remove, starting at the specified position. If this operand is not specified, all characters between the starting position and the end of the string are removed.

Example: Remove([Name], 0, 3)

Replace(String1, SubString2, String3)

Returns a new string in which all occurrences of one specified string (string1) in another string (string2) are replaced with the specified string (string3).

The operands are:

1 - the string in which replacements are made.

2 - the string to be replaced.

3 - the string to replace all occurrences of the specified string.

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

Reverse(String)

Returns a string in which the character order of a specified string is reversed. Example: Reverse([Name])

StartsWith(String1, SubString1)

Returns True if the beginning of one string matches another string; otherwise, False.

The operands are:

1 - a string to test.

2 - a string to match the beginning of another string.

Example: StartsWith([Title], 'The best')

Substring(String, StartPosition, Length)

Returns a substring from the specified string. This function requires two or three operands.

If two operands are passed, the substring starts from the beginning of the specified string. The operands are:

1 - the source string.

2 - an integer that specifies the zero-based position at which the substring starts.

If three operands are passed, a substring starts from the specified position in the source string. The operands are:

1 - the source string.

2 - an integer that specifies the zero-based position at which the substring starts.

3 - an integer that specifies the length of the substring.

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

ToStr(Value)

Returns a string representation of the specified value or property.

If the operand cannot be converted to a string, the NotSupportedException is thrown.

Example: ToStr([ID])

Trim(String)

Returns a string that is a copy of the specified string with all white-space characters removed from the start and end of the specified string. Example: Trim([ProductName])

Upper(String)

Converts all characters in a string operand to uppercase in an invariant culture. Example: Upper([ProductName])

Functions for Expression Bindings and Calculated Fields

Below is a list of functions that are used to construct expression bindings and calculated fields:

NewLine()

Returns the newline string defined for the current environment.

[CategoryName]+NewLine()+[Description]
Result:
Beverages
Soft drinks, coffees, teas, beers and ales.

FormatString(Format, Value1, … , ValueN)

Returns the specified string with formatted field values.

See the following topic for details: Format Data.

FormatString('{0:$0.00}', [UnitPrice])

Result: $45.60

The following example format OrderDate values:

FormatString('{0:MM/dd}', [OrderDate])

Result: 04/15

Rgb(Red, Green, Blue)

Returns a string that defines color using Red, Green, and Blue color channel values.

Rgb(30,200,150)
Result: ‘30,200,150’

Join()

Concatenates multi-value report parameter values into a string. Use this function when you bind a multi-value parameter to a label to display parameter values in a report. Do not use this function as an aggregate function or to concatenate field values.

This function has two overloads:

• Join(parameter) — concatenates the specified parameter values using a comma as a separator.
• Join(parameter, separator) — concatenates specified parameter values using the specified separator.

Join(?CategoriesParameter)
Result: Beverages, Condiments Join(?CategoriesParameter, newline()) Result:
Beverages
Condiments

You can call the CustomFunctions.Unregister method to unregister functions listed in this section.^[1]

Functions for Stored Procedures

The following functions are used to bind a report to a stored procedure:

Join()

Concatenates the multi-value report parameter‘s values into a string.

This function can be used when mapping multi-value report parameters to query parameters generated from a stored procedure’s parameters. Refer to the following topic for more information: Query Parameters.

This function has two overloads:

• Join(parameter) — concatenates the specified parameter values using a comma as a separator.
• Join(parameter, separator) — concatenates the specified parameter values using the specified separator.

Join(?Parameter1)

CreateTable(Column1, …, ColumnN)

Creates a table from several multi-value parameter values.

This function can be used when mapping multi-value report parameters to the query parameter that is generated from a stored procedure’s User Defined Table Type parameter. Refer to the following topic for more information: Query Parameters.

CreateTable(?Parameter1, ..., ?ParameterN)

Functions for Summary Expression Editor

Use the following functions when you calculate a summary across a report and its groups:

sumAvg(Expression)

Calculates the average of all values within the specified summary region (group, page, or report).

sumAvg([UnitPrice])

sumCount(Expression)

Counts the number of values within the specified summary region (group, page, or report). In a simple scenario, you cannot pass a parameter.

When you use this function in a master-detail report‘s master band and pass a detail field as a parameter, the function counts the number of records within the detail band.

See also:

• Count the Number of Records in a Report or Group
• Count the Number of Groups in a Report

sumCount([UnitPrice])

sumCarryoverSum(Expression)

Calculates the carried forward and brought forward totals.

sumCarryoverSum([Amount])

sumDAvg(Expression)

Calculates the average of all distinct values within the specified summary region (group, page, or report).

sumDAvg([UnitPrice])

sumDCount(Expression)

Counts the number of distinct values within the specified summary region (group, page, or report). In a simple scenario, you cannot pass a parameter.

sumDCount([UnitPrice])

sumDStdDev(Expression)

Calculates the standard deviation of all distinct values within the specified summary region (group, page, or report).

sumDStdDev([UnitPrice])

sumDStdDevP(Expression)

Calculates the standard population deviation of all distinct values within the specified summary region (group, page, or report).

sumDStdDevP([UnitPrice])

sumDSum(Expression)

Calculates the total of all distinct values within the specified summary region (group, page, or report).

sumDSum([UnitPrice])

sumDVar(Expression)

Calculates the amount of variance for all distinct values within the specified summary region (group, page, or report).

sumDVar([UnitPrice])

sumDVarP(Expression)

Calculates the population variance of all distinct values within the specified summary region (group, page, or report).

sumDVarP([UnitPrice])

sumMax(Expression)

Calculates the maximum of all values within the specified summary region (group, page, or report).

sumMax([UnitPrice])

sumMedian(Expression)

Finds the middle number within a sequence.

Note that if the total number of elements is odd, this function returns the value of the middle number in a sequence. If the total number of elements is even, this function returns the arithmetic mean of the two middle numbers.

sumMedian([UnitPrice])

sumMin(Expression)

Calculates the minimum of all values within the specified summary region (group, page, or report).

sumMin([UnitPrice])

sumPercentage(Expression)

Calculates the percentage of the current row value relative to the total of all values in the specified summary area (group, page, or report).

sumPercentage([UnitPrice])

sumRecordNumber(Expression)

Returns the current record number in the specified summary region (group, page, or report). This means, for instance, if the summary is calculated for a group, then the record number is calculated only within that group, and is reset every time a new group is started.

In a simple scenario, you cannot pass a parameter.

See also: Display Row Numbers in a Report, Group or Page

sumRecordNumber()

sumRunningSum(Expression)

Calculates the sum of all previous values displayed in a data row prior to the current data row value.

sumRunningSum([UnitPrice])

sumStdDev(Expression)

Calculates the standard deviation of all values within the specified summary region (group, page, or report).

sumStdDev([UnitPrice])

sumStdDevP(Expression)

Calculates the standard population deviation of all values within the specified summary region (group, page, or report).

sumStdDevP([UnitPrice])

sumSum(Expression)

Calculates the total of all values within the specified summary region (group, page, or report).

sumSum([UnitsInStock])

sumVar(Expression)

Calculates the amount of variance for all values within the specified summary region (group, page, or report).

sumVar([UnitPrice])

sumVarP(Expression)

Calculates the population variance of all values within the specified summary region (group, page, or report).

sumVarP([UnitPrice])

sumWAvg(Expression, Expression)

Calculates the weighted average of all values within the specified summary region (group, page, or report). This summary type returns the result of the following operation: Sum(Expression1 * Expression2) / Sum(Expression2).

sumWAvg([UnitPrice])

Examples

This section contains topics that describe how to use expressions to accomplish specific tasks: Expressions - Tasks and Solutions.

Footnotes

1. Unregistered functions are not available in the Expression Editor. An expression that has unregistered functions is evaluated as an empty value.