Expression Constants, Operators, and Functions

• Jun 01, 2022
• 25 minutes to read

The table below contains constants, operators, and functions you can use in expressions.

Note

This topic provides information on basic and reporting-specific constants, operators, and functions. Refer to the Criteria Language Syntax topic in the Cross-Platform Core Libraries documentation for a list of basic constants, operators, and functions you can use in DevExpress products.

Constants

Constant	Description	Example
String constants	Wrap string constants in apostrophes. If a string contains an apostrophe, double the apostrophe.	[Country] == ‘France’ [Name] == ‘O’’Neil’
Date-time constants	Wrap date-time constants in ‘#’.	[OrderDate] >= #2018-03-22 13:18:51.94944#
True	Represents the Boolean True value.	[InStock] == True
False	Represents the Boolean False value.	[InStock] == False
Enumeration	Specify an enumeration value using its underlying integer value.	[Status] == 1 You cannot specify an enumeration value using its qualified name. The following criteria is incorrect: [Status] = Status.InProgress Use the EnumProcessingHelper class’ static methods to register custom enumerations and refer to enumeration values as follows: Status = ##Enum#MyNamespace.Status,InProgress#
Guid	Wrap a Guid constant in curly braces. Use Guid constants in a relational operation with equality or inequality operators only.	[OrderID] == {513724e5-17b7-4ec6-abc4-0eae12c72c1f}
Numeric	Specify different numeric constant types in a string form using suffixes: Int32 (int) - 1 Int16 (short) - 1s Byte (byte) - 1b Double (double) - 1.0 Single (float) - 1.0f Decimal (decimal) - 1.0m	[Price] == 25.0m
?	Represents a null reference that does not refer to any object. We recommend using the IsNull unary operator (for example, “[Region] is null”) or the IsNull logical function (for example, “IsNull([Region])”) instead.	[Region] != ?

Enumerations

Do one of the following:

• Specify an enumeration value using its underlying integer value.

  [Borders] = 1

• Use the EnumProcessingHelper.RegisterEnum static methods to register an enumeration at application startup:

  DevExpress.Data.Filtering.EnumProcessingHelper.RegisterEnum(
      typeof(DevExpress.XtraPrinting.BorderSide));
  

  After that, you can refer to enumeration values as follows:

  [Borders] = ##Enum#DevExpress.XtraPrinting.BorderSide,Left#

• The Expression Editor can help you to specify a string value for built-in enumerations:

  

Operators

Operator	Description	Example
+	Adds the value of one numeric expression to another or concatenates two strings.	[UnitPrice] + 4 [FirstName] + ‘ ‘ + [LastName]
-	Finds the difference between two numbers.	[Price1] - [Price2]
*	Multiplies the value of two expressions.	[Quantity] * [UnitPrice] * (1 - [BonusAmount])
/	Divides the first operand by the second.	[Quantity] / 2
%	Returns the remainder (modulus) obtained by dividing one numeric expression by another.	[Quantity] % 3
|	Performs a bitwise inclusive OR on two numeric expressions. 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.	[Number] | [Number]
&	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.	[Number] & 10
^	Performs a bitwise exclusive OR on two numeric expressions.	[Number] ^ [Number]
== =	Returns true if both operands have the same value; otherwise, it returns false.	[Quantity] == 10
!=	Returns true if the operands do not have the same value; otherwise, it returns false.	[Country] != ‘France’
<	Less than operator. Used to compare expressions.	[UnitPrice] < 20
<=	Less than or equal to operator. Used to compare expressions.	[UnitPrice] <= 20
>=	Greater than or equal to operator. Used to compare expressions.	[UnitPrice] >= 30
>	Greater than operator. Used to compare expressions.	[UnitPrice] > 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.	[Quantity] Between (10, 20)
And &&	Performs a logical conjunction on two Boolean expressions.	[InStock] And ([ExtendedPrice]> 100) [InStock] && ([ExtendedPrice]> 100)
Or ||	Performs a logical disjunction on two Boolean expressions.	[Country]==’USA’ Or [Country]==’UK’ [Country]==’USA’ || [Country]==’UK’
~	Performs a bitwise negation on a numeric expression.	~[Roles] = 251
Not !	Performs a logical negation on a Boolean expression.	Not [InStock] ![InStock]
+	Returns a numeric expression’s value (a unary operator).	+[Value] = 10
-	Returns the negative of a numeric expression’s value (a unary operator).	-[Value] = 20
Is Null	Returns true if an expression is a null reference, the one that does not refer to any object.	[Region] is null

Functions (Basic)

Aggregate Functions

Function	Description	Example
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()
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 single 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 the expression values in the collection.	[Products].Sum([UnitsInStock])

Date-time Functions

Function	Description	Example
AddDays(DateTime, DaysCount)	Returns a date-time value that is the specified number of days from the specified DateTime.	AddDays([OrderDate], 30)
AddHours(DateTime, HoursCount)	Returns a date-time value that is the specified number of hours from the specified DateTime.	AddHours([StartTime], 2)
AddMilliSeconds(DateTime, MilliSecondsCount)	Returns a date-time value that is the specified number of milliseconds from the specified DateTime.	AddMilliSeconds(([StartTime], 5000))
AddMinutes(DateTime, MinutesCount)	Returns a date-time value that is the specified number of minutes from the specified DateTime.	AddMinutes([StartTime], 30)
AddMonths(DateTime, MonthsCount)	Returns a date-time value that is the specified number of months from the specified DateTime.	AddMonths([OrderDate], 1)
AddSeconds(DateTime, SecondsCount)	Returns a date-time value that is the specified number of seconds from the specified DateTime.	AddSeconds([StartTime], 60)
AddTicks(DateTime, TicksCount)	Returns a date-time value that is the specified number of ticks from the specified DateTime.	AddTicks([StartTime], 5000)
AddTimeSpan(DateTime, TimeSpan)	Returns a date-time value that is 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 from the specified DateTime.	AddYears([EndDate], -1)
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])
IsApril(DateTime)	Returns True if the specified date falls within April.	IsApril([OrderDate])
IsAugust(DateTime)	Returns True if the specified date falls within August.	IsAugust([OrderDate])
IsDecember(DateTime)	Returns True if the specified date falls within December.	IsDecember([OrderDate])
IsFebruary(DateTime)	Returns True if the specified date falls within February.	IsFebruary([OrderDate])
IsJanuary(DateTime)	Returns True if the specified date falls within January.	IsJanuary([OrderDate])
IsJuly(DateTime)	Returns True if the specified date falls within July.	IsJuly([OrderDate])
IsJune(DateTime)	Returns True if the specified date falls within June.	IsJune([OrderDate])
IsLastMonth(DateTime)	Returns True if the specified date falls within the previous month.	IsLastMonth([OrderDate])
IsLastYear(DateTime)	Returns True if the specified date falls within the previous year.	IsLastYear([OrderDate])
IsMarch(DateTime)	Returns True if the specified date falls within March.	IsMarch([OrderDate])
IsMay(DateTime)	Returns True if the specified date falls within May.	IsMay([OrderDate])
IsNextMonth(DateTime)	Returns True if the specified date falls within the next month.	IsNextMonth([OrderDate])
IsNextYear(DateTime)	Returns True if the specified date falls within the next year.	IsNextYear([OrderDate])
IsNovember(DateTime)	Returns True if the specified date falls within November.	IsNovember([OrderDate])
IsOctober(DateTime)	Returns True if the specified date falls within October.	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.	IsSeptember([OrderDate])
IsThisMonth(DateTime)	Returns True if the specified date falls within the current month.	IsThisMonth([OrderDate])
IsThisWeek(DateTime)	Returns True if the specified date falls within the current week.	IsThisWeek([OrderDate])
IsYearToDate(DateTime)	Returns True if the specified date falls within the year-to-date period. This period starts from the first day of the current year and continues to the current date (including the current date).	IsYearToDate([OrderDate])
IsThisYear(DateTime)	Returns True if the specified date falls within the current year.	IsThisYear([OrderDate])
LocalDateTimeDayAfterTomorrow()	Returns a date-time value corresponding to the day after Tomorrow.	AddDays(LocalDateTimeDayAfterTomorrow(), 5)
LocalDateTimeLastMonth()	Returns the DateTime value corresponding to the first day of the previous month.	AddMonths(LocalDateTimeLastMonth(), 5)
LocalDateTimeLastWeek()	Returns a date-time value corresponding to the first day of the previous week.	AddDays(LocalDateTimeLastWeek(), 5)
LocalDateTimeLastYear()	Returns the DateTime value corresponding to the first day of the previous year.	AddYears(LocalDateTimeLastYear(), 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)
LocalDateTimeTwoMonthsAway()	Returns the DateTime value corresponding to the first day of the following month.	AddMonths(LocalDateTimeTwoMonthAway(), 5)
LocalDateTimeTwoWeeksAway()	Returns the DateTime value corresponding to the first day of the following week.	AddDays(LocalDateTimeTwoWeeksAway(), 5)
LocalDateTimeTwoYearsAway()	Returns the DateTime value corresponding to the first day of the following year.	AddYears(LocalDateTimeTwoYearsAway(), 5)
LocalDateTimeYearBeforeToday()	Returns the DateTime value corresponding to the day one year ago.	AddYears(LocalDateTimeYearBeforeToday(), 5)
LocalDateTimeYesterday()	Returns a date-time value corresponding to Yesterday.	AddDays(LocalDateTimeYesterday(), 5)
Now()	Returns the current system date and time.	AddDays(Now(), 5)
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)

Logical Functions

Function	Description	Example
Iif(Expression1, True_Value1, …, ExpressionN, True_ValueN, False_Value)	Returns one of several specified values depending upon the values of logical expressions. The function can take 2N+1 arguments (N - 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 yielded false.	Iif(Name = ‘Bob’, 1, Name = ‘Dan’, 2, Name = ‘Sam’, 3, 4)”)
IsNull(Value)	Returns True if the specified Value is NULL.	IsNull([OrderDate])
IsNull(Value1, Value2)	Returns Value1 if it is not set to NULL; otherwise, Value2 is returned.	IsNull([ShipDate], [RequiredDate])
IsNullOrEmpty(String)	Returns True if the specified String object is NULL or an empty string; otherwise, False is returned.	IsNullOrEmpty([ProductName])

Math Functions

Function	Description	Example
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 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 numeric expression.	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 integer less than or equal to the numeric expression.	Floor([Value])
Log(Value)	Returns a specified number’s natural logarithm.	Log([Value])
Log(Value, Base)	Returns the logarithm of a specified number in a specified Base.	Log([Value], 2)
Log10(Value)	Returns a specified number’s base 10 logarithm.	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

Function	Description	Example
Ascii(String)	Returns the ASCII code value of the leftmost character in a character expression.	Ascii(‘a’)
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’, ‘devexpress’)
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’, ‘devexpress’, 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, False is returned.	Contains([ProductName], ‘dairy’)
EndsWith(String1, SubString1)	Returns True if the end of String1 matches SubString1; otherwise, False is returned.	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])
PadLeft(String, Length)	Left-aligns the defined string’s characters, padding its left side with white space characters up to a specified total length.	PadLeft([Name], 30)
PadLeft(String, Length, Char)	Left-aligns the defined string’s characters, padding its left side with the specified Char up to a specified total length.	PadLeft([Name], 30, ‘<’)
PadRight(String, Length)	Right-aligns the defined string’s characters, padding its left side with empty space characters up to a specified total length.	PadRight([Name], 30)
PadRight(String, Length, Char)	Right-aligns the defined string’s characters, padding its left side with the specified Char up to a specified total length.	PadRight([Name], 30, ‘>’)
Remove(String, StartPosition)	Deletes all the characters from this instance, beginning at a specified position.	Remove([Name], 3)
Remove(String, StartPosition, Length)	Deletes a specified number of characters from this instance, beginning at a specified position.	Remove([Name], 0, 3)
Replace(String1, 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, False.	StartsWith([Title], ‘The best’)
Substring(String, StartPosition, Length)	Retrieves a substring from String. The substring starts at StartPosition and has a specified Length.	Substring([Description], 2, 3)
Substring(String, StartPosition)	Retrieves a substring from String. The substring starts at StartPosition.	Substring([Description], 2)
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])

Functions for Expression Bindings and Calculated Fields

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

Function	Description	Example
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 Format Data for details.	FormatString(‘{0:$0.00}’, [UnitPrice]) Result: $45.60
Rgb(Red, Green, Blue)	Returns a string defining a color using the Red, Green, and Blue color channel values.	Rgb(30,200,150) Result: ‘30,200,150’
Argb(Alpha, Red, Green, Blue)	Returns a string defining a color using the Alpha, Red, Green, and Blue color channel values.	Argb(1,200, 30, 200) Result: ‘1,200,30,200’
Join()	Concatenates the multi-value report parameter‘s values into a string. This function is useful when you bind a multi-value parameter to a label to display the parameter’s values in a report. This function has two overloads: Join(parameter) - concatenates the specified parameter’s values using comma as a separator. Join(parameter, separator) - concatenates the specified parameter’s values using the specified separator.	Join(?CategoriesParameter) Result: Beverages, Condiments Join(?CategoriesParameter, newline()) Result: Beverages Condiments

Functions for Stored Procedure Binding

The following functions are specific for binding reports to a stored procedure:

Function	Description	Example
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 Query Parameters topic for more information. This function has two overloads: Join(parameter) - concatenates the specified parameter’s values using comma as a separator. Join(parameter, separator) - concatenates the specified parameter’s values using the specified separator.	Join(?Parameter1)
CreateTable(Column1, …, ColumnN)	Creates a table from several multi-value parameters’ 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 Query Parameters topic for more information.	CreateTable(?Parameter1, …, ?ParameterN)

Functions for Summary Expression Editor

Use the following functions when calculating summaries across a report and its groups:

Function	Description	Example
sumAvg(Expression)	Calculates the average of all the 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 may not pass a parameter. When using this function in a master-detail report‘s master band and passing a detail’s field as a parameter, it counts the number of records within the detail’s band. See also: Count the Number of Records in a Report or Group, Count the Number of Groups in a Report	sumCount([UnitPrice])
sumDAvg(Expression)	Calculates the average of all the 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 may not pass a parameter.	sumDCount([UnitPrice])
sumDStdDev(Expression)	Calculates the standard deviation of all the distinct values within the specified summary region (group, page or report).	sumDStdDev([UnitPrice])
sumDStdDevP(Expression)	Calculates the standard population deviation of all the distinct values within the specified summary region (group, page or report).	sumDStdDevP([UnitPrice])
sumDSum(Expression)	Calculates the total of all the distinct values within the specified summary region (group, page or report).	sumDSum([UnitPrice])
sumDVar(Expression)	Calculates the amount of variance for all the distinct values within the specified summary region (group, page or report).	sumDVar([UnitPrice])
sumDVarP(Expression)	Calculates the population variance of all the distinct values within the specified summary region (group, page or report).	sumDVarP([UnitPrice])
sumMax(Expression)	Calculates the maximum of all the 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 arithmetical mean of the two middle numbers.	sumMedian([UnitPrice])
sumMin(Expression)	Calculates the minimum of all the values within the specified summary region (group, page or report).	sumMin([UnitPrice])
sumPercentage(Expression)	Calculates the percent ratio of the current data row’s value to the total of all the values within the specified summary region (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 may not pass a parameter. See also: Display Row Numbers in a Report, Group or Page	sumRecordNumber()
sumRunningSum(Expression)	Summarizes all the values, which were printed before the current data row, with the current data row’s value.	sumRunningSum([UnitPrice])
sumStdDev(Expression)	Calculates the standard deviation of all the values within the specified summary region (group, page or report).	sumStdDev([UnitPrice])
sumStdDevP(Expression)	Calculates the standard population deviation of all the values within the specified summary region (group, page or report).	sumStdDevP([UnitPrice])
sumSum(Expression)	Calculates the total of all the values within the specified summary region (group, page or report).	sumSum([UnitsInStock])
sumVar(Expression)	Calculates the amount of variance for all the values within the specified summary region (group, page or report).	sumVar([UnitPrice])
sumVarP(Expression)	Calculates the population variance of all the values within the specified summary region (group, page or report).	sumVarP([UnitPrice])
sumWAvg(Expression, Expression)	Calculates the weighted average of all the values within the specified summary region (group, page or report). This type of summary returns the result of the following operation: Sum(Expression1 * Expression2) / Sum(Expression2).	sumWAvg([UnitPrice])

Refer to Report Items in Expressions

A report’s elements are displayed in the Report Designer’s Report Explorer. You can access these elements and their properties in an expression. The following example demonstrates how to set a label’s BackColor property to another label’s BackColor property value:

[ReportItems].[xrLabel2].[BackColor]

Tip

[ReportItems] is a plain list that provides access to all report items at one level.

Note

You cannot use the ReportItems collection in a Calculated Field‘s expression.

Calculate Group Summaries

Use the ^ operator to specify an expression that calculates a group summary.

• Sum up the EFC field values in a group:

  [][[GroupFieldName] == [^.GroupFieldName]].Sum([EFC])
  

• Specify the group header value:

  [][[CategoryID] == [^.CategoryID] and [ProductID] == [][[CategoryID] == [^.CategoryID]].Max([ProductID])].Max([ProductName])
  

• Count the number of a value’s occurrences:

  The following expression counts how many times the value 12 occurs in the data source:

  [][[FootSize]='12'].Count()
  

  The following expression counts the number of records with non-zero values:

  [][[FootSize]!=0].Avg([FootSize])
  

Specify Images for Picture Boxes

When you specify an expression for the XRPictureBox‘s ImageSource property, you can use image Ids from the report’s ImageResources collection.

IIf([MarchSales]>20, [Images.ArrowUp],[Images.ArrowDown])

Use Row/Column Indexes for Cross Tab Cells

Use the following variables in an expressions to change a Cross Tab cell’s appearance settings:

Variable	Description	Example
Arguments.GroupColumnIndex	Returns the index of a cell’s column within a group.	iif([Arguments.GroupColumnIndex] % 2 == 1, Rgb(235, 241, 252), ?) Result: The specified color applies if a cell is located in an odd column; if the cell is located in an even column, its color does not change.
Arguments.GroupRowIndex	Returns the index of a cell’s row within a group.	iif([Arguments.GroupRowIndex] % 2 == 1, Rgb(235, 241, 252), ?) Result: The specified color applies if a cell is located in an odd row; if the cell is located in an even row, its color does not change.

Use Variables for Event-Related Expressions

You can specify expressions for the XRControl.BeforePrint and XRControl.PrintOnPage events.

Use the following variables to specify an expression that the XRControl.BeforePrint event resolves:

Variable	Description	Example
DataSource.RowCount	Returns the total amount of data rows in a data source.	[DataSource.RowCount] != 0 Result: When this expression is applied to a control’s Visible property, the control is not displayed if there is no data in the data source.
DataSource.CurrentRowIndex	Returns a zero-based index of the current data row in a data source.	Iif([DataSource.CurrentRowIndex] % 2 = 0, ‘red’, ‘green’) Result: When this expression is used for a table row’s BackColor property, odd rows are colored in red, even rows are colored in green.
DataSource.CurrentRowHierarchyLevel	Returns a zero-based level of the current row in a hierarchical report.	Iif([DataSource.CurrentRowHierarchyLevel] == 0, Rgb(231,235,244), ?) Result: When this expression is used for the BackColor property of the Detail band that is printed in tree mode, the root level rows are highlighted.

Use the following variables to specify an expression that the XRControl.PrintOnPage event resolves:

Variable	Description	Example
Arguments.PageIndex	Returns a zero-based index of the currently generated report document page.	[Arguments.PageIndex] % 2 = 0 Result: The control is displayed on odd pages only when this expression is used for a control’s Visible property.
Arguments.PageCount	Returns the page count in a report document. Note The PageCount value is not valid if you use the CachedReportSource component to generate documents or use the PdfStreamingExporter instance to export reports.	Iif(([Arguments.PageIndex] = [Arguments.PageCount] - 1), ‘The last page!’, ‘’) Result: When this expression is applied to a label’s Text property, the ‘The last page!’ string is displayed on the last page.

Note

The Arguments.PageIndex and Arguments.PageCount variables are not valid in the following scenarios:

• the report is merged with another report
• the report includes a table of contents

Use Report Parameters

Use the following syntax to insert report parameters in an expression:

• Type a question mark before a parameter’s name.

  ?parameter1

• (Obsolete approach) Use the “Parameters.” prefix before a report parameter‘s name.

  [Parameters.parameter1]

Verify Collection Elements

Use brackets “[]” to check if a collection contains an element that satisfies your condition. The following expression returns true if the Accounts collection contains at least one element that satisfies the [Amount] == 100 condition:

[Accounts][[Amount] == 100]

The following expression returns false if the Accounts collection is empty:

[Accounts][]

Refer to the Calculate an Aggregate Function topic for an example on how to use this syntax.

Specify Parent Relations

Use the ‘^’ parent relation operator to refer to a parent in expressions that are written in the context of a child. You can apply this operator successively to span multi-level parent relationships.

You can use this operator to refer to the currently processed report group. This allows you to calculate aggregates within groups, as shown in the following expression:

[][[^.CategoryID] == [CategoryID]].Sum([UnitPrice])

Tip

The complete sample project is available in the following DevExpress Examples repository on GitHub: How to use aggregate functions in Calculated Field expressions.

Group Clauses with Brackets

It is important to use brackets to ensure that your expression returns the intended results.

For instance, the following expression returns all Customers where the account Date is 8/25/2006 ot where the account Amount is 100:

[Accounts][[Date] == #8/25/2006#] && [Accounts][[Amount] == 100]

Construct an expression as in the following example to search for all Customers that have an Account with both a Date of 8/25/2006 and an Amount of 100:

[Accounts][[Date] == #8/25/2006# && [Amount] == 100]

Change Operator Precedence

When an expression contains multiple operators, their precedence controls the order in which expression elements are evaluated.

• Literal values
• Parameters
• Identifiers
• OR (left-associative)
• AND (left-associative)
• The ‘.’ relationship qualifier (left-associative)
• ==, !=
• <, >, <=, >=
• -, + (left-associative)
• *, /, % (left-associative)
• NOT
• unary -
• In
• Iif
• Trim(), Len(), Substring(), IsNull()
• ‘[]’ (for set-restriction)
• ‘()’

Group elements with parentheses to change operator precedence. For instance, operators are applied in the default order in the first code sample below. In the second code sample, the addition operation is applied first, because its associated elements are grouped with parentheses, and the multiplication operation is applied last.

Accounts[Amount == 2 + 48 * 2]

Accounts[Amount == (2 + 48) * 2]

Case Sensitivity

Operators are case insensitive. However, field values’ case sensitivity depends on the data source.

Note

Data source specifics affect certain operators’ behavior. For instance, SQL Server Express 2005 is configured as case insensitive. In this case, the following expression always evaluates to true:

Lower(Name) == Upper(Name)

Escape Keywords

You can mark a keyword-like field name with the @ escape character. In the expression below, the CriteriaOperator.Parse method interprets @Or as a field named “Or”, not the logical operator OR.

@Or = ‘value’

Escape Characters

Use a backslash (\) as an escape character for characters in an expression, as shown below:

• \[
• \\
• \‘

Use a single quote (') as an escape character for string literals:

‘A parameter’’s value is:’ + ?parameter1