Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Expand]Controls and Libraries
 [Collapse]Common Features
  [Expand]Data Binding Common Concepts
  [Expand]Data Source Wizard
  [Collapse]Expressions
    Expression Editor
    Expression Operators, Functions and Constants
    Implementing Custom Functions
    Member Table: Expressions
  [Expand]Behaviors
  [Expand]Application Appearance
  [Expand]Filtering UI Context
  [Expand]High DPI Support
  [Expand]Scaffolding Wizard
  [Expand]Formatting Values
   HTML Text Formatting
  [Expand]Menus
  [Expand]Tooltip Management
  [Expand]Saving and Restoring Layouts
   Clipboard - Copy Data and Formatting
   Version Compatibility: Default Property Values
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Expression Operators, Functions and Constants

This topic lists operators and functions supported by expressions. It also provides information on how constants can be specified in expressions.

Expanded Operators

Operator Description Example XLS(x) Format Export-Friendly See Note
+ Adds the value of one numeric expression to another or concatenates two strings. [UnitPrice] + 4
[FirstName] + ' ' + [LastName]
Yes
- Finds the difference between two numbers. [Price1] - [Price2] Yes
* Multiplies the value of two expressions. [Quantity] * [UnitPrice] * (1 - [BonusAmount]) Yes
/ Divides the first operand by the second. [Quantity] / 2 Yes
% Returns the remainder (modulus) obtained by dividing one numeric expression by another. [Quantity] % 3 Yes
| Compares each bit of its first operand to the corresponding bit of its second operand. If either bit is 1, the corresponding result bit is set to 1. Otherwise, the corresponding result bit is set to 0. [Flag1] | [Flag2] Yes
& Performs a bitwise logical AND operation between two integer values. [Flag] & 10 Yes
^ Performs a logical exclusion on two Boolean expressions, or a bitwise exclusion on two numeric expressions. [Flag1] ^ [Flag2] Yes
== Returns true if both operands have the same value; otherwise, it returns false. [Quantity] == 10 Yes
!= Returns true if the operands do not have the same value; otherwise, it returns false. [Country] != 'France' Yes
< Less than operator. Used to compare expressions. [UnitPrice] < 20 Yes
<= Less than or equal to operator. Used to compare expressions. [UnitPrice] <= 20 Yes
>= Greater than or equal to operator. Used to compare expressions. [UnitPrice] >= 30 Yes
> Greater than operator. Used to compare expressions. [UnitPrice] > 30 Yes
In (,,,) Tests for the existence of a property in an object. [Country] In ('USA', 'UK', 'Italy') Yes
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) Yes
And
&&
Performs a logical conjunction on two expressions. [InStock] And ([ExtendedPrice]> 100)
[InStock] && ([ExtendedPrice]> 100)
Yes
Or
||
Performs a logical disjunction on two Boolean expressions. [Country]=='USA' Or [Country]=='UK'
[Country]=='USA' || [Country]=='UK'
Yes
Not
!
Performs logical negation on an expression. Not [InStock]
![InStock]
Yes

Expanded Functions

Aggregate Functions

Function Description Example XLS(x) Format Export-Friendly See Note
Avg(Value) Evaluates the average of the values in the collection. [].Avg([UnitPrice]) -
Count() Returns the number of objects in a collection. [].Count() -
Exists() Determines whether the object exists in the collection. [][[CategoryID] == 7].Exists() -
Max(Value) Returns the maximum expression value in a collection. [].Max([UnitPrice]) -
Min(Value) Returns the minimum expression value in a collection. [].Min([UnitPrice]) -
Single() Returns a single object from the collection. [][^.ParentId = Id].Single([This]) -
Sum(Value) Returns the sum of all the expression values in the collection. [].Sum([UnitsInStock]) -

Date-time Functions

Function Description Example XLS(x) Format Export-Friendly See Note
AddDays(DateTime, DaysCount) Returns a date-time value that is the specified number of days away from the specified DateTime. AddDays([OrderDate], 30) Yes
AddHours(DateTime, HoursCount) Returns a date-time value that is the specified number of hours away from the specified DateTime. AddHours([StartTime], 2) Yes
AddMilliSeconds(DateTime, MilliSecondsCount) Returns a date-time value that is the specified number of milliseconds away from the specified DateTime. AddMilliSeconds(([StartTime], 5000)) -
AddMinutes(DateTime, MinutesCount) Returns a date-time value that is the specified number of minutes away from the specified DateTime. AddMinutes([StartTime], 30) Yes
AddMonths(DateTime, MonthsCount) Returns a date-time value that is the specified number of months away from the specified DateTime. AddMonths([OrderDate], 1) Yes
AddSeconds(DateTime, SecondsCount) Returns a date-time value that is the specified number of seconds away from the specified DateTime. AddSeconds([StartTime], 60) Yes
AddTicks(DateTime, TicksCount) Returns a date-time value that is the specified number of ticks away from the specified DateTime. AddTicks([StartTime], 5000) -
AddTimeSpan(DateTime, TimeSpan) Returns a date-time value that is away 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 away from the specified DateTime. AddYears([EndDate], -1) Yes
DateDiffDay(startDate, endDate) Returns the number of day boundaries between two non-nullable dates. DateDiffDay([StartTime], Now()) Yes
DateDiffHour(startDate, endDate) Returns the number of hour boundaries between two non-nullable dates. DateDiffHour([StartTime], Now()) Yes
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()) Yes
DateDiffMonth(startDate, endDate) Returns the number of month boundaries between two non-nullable dates. DateDiffMonth([StartTime], Now()) Yes
DateDiffSecond(startDate, endDate) Returns the number of second boundaries between two non-nullable dates. DateDiffSecond([StartTime], Now()) Yes
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()) Yes
GetDate(DateTime) Extracts a date from the defined DateTime. GetDate([OrderDateTime]) Yes
GetDay(DateTime) Extracts a day from the defined DateTime. GetDay([OrderDate]) Yes
GetDayOfWeek(DateTime) Extracts a day of the week from the defined DateTime. GetDayOfWeek([OrderDate]) Yes
GetDayOfYear(DateTime) Extracts a day of the year from the defined DateTime. GetDayOfYear([OrderDate]) Yes
GetHour(DateTime) Extracts an hour from the defined DateTime. GetHour([StartTime]) Yes
GetMilliSecond(DateTime) Extracts milliseconds from the defined DateTime. GetMilliSecond([StartTime]) -
GetMinute(DateTime) Extracts minutes from the defined DateTime. GetMinute([StartTime]) Yes
GetMonth(DateTime) Extracts a month from the defined DateTime. GetMonth([StartTime]) Yes
GetSecond(DateTime) Extracts seconds from the defined DateTime. GetSecond([StartTime]) Yes
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]) Yes
IsThisMonth(DateTime) Returns True if the specified date falls within the current month. IsThisMonth([OrderDate]) Yes
IsThisWeek(DateTime) Returns True if the specified date falls within the current week. IsThisWeek([OrderDate]) Yes
IsThisYear(DateTime) Returns True if the specified date falls within the current year. IsThisYear([OrderDate]) Yes
LocalDateTimeDayAfterTomorrow() Returns a date-time value corresponding to the day after Tomorrow. AddDays(LocalDateTimeDayAfterTomorrow(), 5) Yes
LocalDateTimeLastWeek() Returns a date-time value corresponding to the first day of the previous week. AddDays(LocalDateTimeLastWeek(), 5) Yes
LocalDateTimeNextMonth() Returns a date-time value corresponding to the first day of the next month. AddMonths(LocalDateTimeNextMonth(), 5) Yes
LocalDateTimeNextWeek() Returns a date-time value corresponding to the first day of the following week. AddDays(LocalDateTimeNextWeek(), 5) Yes
LocalDateTimeNextYear() Returns a date-time value corresponding to the first day of the following year. AddYears(LocalDateTimeNextYear(), 5) Yes
LocalDateTimeNow() Returns a date-time value corresponding to the current moment in time. AddDays(LocalDateTimeNow(), 5) Yes
LocalDateTimeThisMonth() Returns a date-time value corresponding to the first day of the current month. AddMonths(LocalDateTimeThisMonth(), 5) Yes
LocalDateTimeThisWeek() Returns a date-time value corresponding to the first day of the current week. AddDays(LocalDateTimeThisWeek(), 5) Yes
LocalDateTimeThisYear() Returns a date-time value corresponding to the first day of the current year. AddYears(LocalDateTimeThisYear(), 5) Yes
LocalDateTimeToday() Returns a date-time value corresponding to Today. AddDays(LocalDateTimeToday(), 5) Yes
LocalDateTimeTomorrow() Returns a date-time value corresponding to Tomorrow. AddDays(LocalDateTimeTomorrow(), 5) Yes
LocalDateTimeTwoWeeksAway() Returns a date-time value corresponding to the first day of the week that is after next week. AddDays(LocalDateTimeTwoWeeksAway(), 5) Yes
LocalDateTimeYesterday() Returns a date-time value corresponding to Yesterday. AddDays(LocalDateTimeYesterday(), 5) Yes
Now() Returns the current system date and time. AddDays(Now(), 5) Yes
Today() Returns the current date. Regardless of the actual time, this function returns midnight of the current date. AddMonths(Today(), 1) Yes
UtcNow() Returns the current system date and time, expressed as Coordinated Universal Time (UTC). AddDays(UtcNow(), 7) -

Logical Functions

Function Description Example XLS(x) Format Export-Friendly See Note
Iif(Expression, TruePart, FalsePart) Returns either TruePart or FalsePart, depending on the evaluation of the Boolean Expression. Iif([Quantity]>=10, 10, 0 ) -
IsNull(Value) Returns True if the specified Value is NULL. IsNull([OrderDate]) Yes
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]) Yes

Math Functions

Function Description Example XLS(x) Format Export-Friendly See Note
Abs(Value) Returns the absolute, positive value of the given numeric expression. Abs(1 - [Discount]) Yes
Acos(Value) Returns the arccosine of a number (the angle in radians, whose cosine is the given float expression). Acos([Value]) Yes
Asin(Value) Returns the arcsine of a number (the angle in radians, whose sine is the given float expression). Asin([Value]) Yes
Atn(Value) Returns the arctangent of a number (the angle in radians, whose tangent is the given float expression). Atn([Value]) Yes
Atn2(Value1, Value2) Returns the angle whose tangent is the quotient of two specified numbers in radians. Atn2([Value1], [Value2]) Yes
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 given numeric expression. Ceiling([Value]) Yes
Cos(Value) Returns the cosine of the angle defined in radians. Cos([Value]) Yes
Cosh(Value) Returns the hyperbolic cosine of the angle defined in radians. Cosh([Value]) Yes
Exp(Value) Returns the exponential value of the given float expression. Exp([Value]) Yes
Floor(Value) Returns the largest integer less than or equal to the given numeric expression. Floor([Value]) Yes
Log(Value) Returns the natural logarithm of a specified number. Log([Value]) Yes
Log(Value, Base) Returns the logarithm of a specified number in a specified Base. Log([Value], 2) Yes
Log10(Value) Returns the base 10 logarithm of a specified number. Log10([Value]) Yes
Max(Value1, Value2) Returns the maximum value from the specified values. Max([Value1], [Value2]) Yes
Min(Value1, Value2) Returns the minimum value from the specified values. Min([Value1], [Value2]) Yes
Power(Value, Power) Returns a specified number raised to a specified power. Power([Value], 3) Yes
Rnd() Returns a random number that is less than 1, but greater than or equal to zero. Rnd()*100 Yes
Round(Value) Rounds the given value to the nearest integer. Round([Value]) Yes
Round(Value, Precision) Rounds the given value to the nearest integer, or to a specified number of decimal places. Round([Value], 2) Yes
Sign(Value) Returns the positive (+1), zero (0), or negative (-1) sign of the given expression. Sign([Value]) Yes
Sin(Value) Returns the sine of the angle defined in radians. Sin([Value]) Yes
Sinh(Value) Returns the hyperbolic sine of the angle defined in radians. Sinh([Value]) Yes
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]) Yes
Tanh(Value) Returns the hyperbolic tangent of the angle defined in radians. Tanh([Value]) Yes
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 XLS(x) Format Export-Friendly See Note
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) Yes
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]) Yes
Contains(String1, SubString1) Returns True if SubString1 occurs within String1; otherwise, False is returned. Contains([ProductName], 'dairy') Yes
EndsWith(String1, SubString1) Returns True if the end of String1 matches SubString1; otherwise, False is returned. EndsWith([Description], 'The end.') Yes
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]) Yes
Lower(String) Returns String in lowercase. Lower([ProductName]) Yes
PadLeft(String, Length) Left-aligns characters in the defined string, padding its left side with white space characters up to a specified total length. PadLeft([Name], 30) -
PadLeft(String, Length, Char) Left-aligns characters in the defined string, padding its left side with the specified Char up to a specified total length. PadLeft([Name], 30, '<') -
PadRight(String, Length) Right-aligns characters in the defined string, padding its left side with white space characters up to a specified total length. PadRight([Name], 30) -
PadRight(String, Length, Char) Right-aligns characters in the defined string, padding its left side with the specified Char up to a specified total length. PadRight([Name], 30, '>') -
Remove(String, StartPosition) Deletes all 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(String, 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 is returned. StartsWith([Title], 'The best') Yes
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]) Yes
Upper(String) Returns String in uppercase. Upper([ProductName]) Yes

Expanded Constants

Constant Description Example XLS(x) Format Export-Friendly See Note
String constants String constants must be wrapped in apostrophes.

If a string contains an apostrophe, the apostrophe must be doubled.

[Country] == 'France'

[Name] == 'O''Neil'

Yes
Date-time constants Date-time constants must be wrapped in '#'. [OrderDate] >= #1/1/2009# Yes
True Represents the Boolean True value. [InStock] == True Yes
False Represents the Boolean False value. [InStock] == False Yes
? Represents a null reference, one that does not refer to any object. [Region] != ? Yes

Expanded Note - Operators, Functions and Constants That Can be Exported to XLS(X) Format When Used in Expression Based Conditional Formatting and Unbound Columns

The following restrictions apply when exporting certain DevExpress controls (Data Grid and Tree List controls for WinForms and WPF) to XLS(X) format in Data-Aware Export Mode.

If these controls use expression-based conditional formatting and/or expression-based unbound columns, expressions are only exported to XLS(X) format if they are made of export-friendly functions. To find out if a function can be exported to XLS(x) format, refer to the XLS(x) Format Export-Friendly column in the tables above.

Note

To export expressions for unbound columns, ensure that a dedicated setting (XlsExportOptionsEx.UnboundExpressionExportMode/XlsxExportOptionsEx.UnboundExpressionExportMode) is set to AsFormula.

See the following links to learn more.

How would you rate this topic?​​​​​​​