Expression Constants, Operators, and Functions

• Nov 29, 2024
• 31 minutes to read

Note

This topic details basic and dashboard-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.

The DevExpress Dashboard uses criteria language that you can use in various DevExpress products for building expressions. An expression is a string that evaluates some value. The criteria language is based on the the Cross-Platform Class Library with some additions and subtractions specific for dashboards.

The tables below contain constants, operators, and functions you can use in dashboard expressions.

Constants

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

Note that you cannot specify an enumeration value using its qualified name. The following criteria is incorrect:

[Status] = Status.InProgress

You can register custom enumerations using the static EnumProcessingHelper class methods and then 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

?

A null reference that does not refer to any object.

We recommend that you use the IsNull unary operator (for example, [Region] is null) or the IsNull logical function (for example, IsNull([Region])) instead.

[Region] != ?

You can build parameterized criteria using any number of positional parameters. To do this, add parameter placeholders (question mark characters) to a criteria expression to identify parameter positions and provide a list of parameter values. When building criteria, parameter placeholders are substituted with parameter values in values in the order they are listed.

CriteriaOperator.Parse("[Name] == ? and [Age] == ?", "John", 33)

The following two examples are identical, but the second one allows you to avoid formatting errors:

CriteriaOperator.Parse("[OrderDate] >= #1/1/2009#")

CriteriaOperator.Parse("[OrderDate] >= ?", new DateTime(2009, 1, 1))

When parameters are not specified, a parameter placeholder is substituted with null:

CriteriaOperator.Parse("[Region] != ?")

Operators

+

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.

[Flag1] | [Flag2]

&

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.

[Flag] & 10

^

Performs a bitwise exclusive OR on two numeric expressions.

[Flag1] ^ [Flag2]

==, =

Returns true if both operands have the same value; otherwise, it returns false.

[Quantity] == 10

[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

Aggregate Functions

Avg(Value)

Returns the average of all the values in the expression.

Avg([Profit])

Count()

Returns the number of values.

Count()

CountNotNull(Value)

Returns a number of non-null objects in a collection.

CountNotNull([Orders])

CountDistinct(Value)

Returns the number of distinct values.

CountDistinct([Orders])

FirstValue()

Returns the first value from rows used to calculate an aggregate value.

FirstValue([ProductName])

Max(Value)

Returns the maximum value across all records.

Max([Profit])

Min(Value)

Returns the minimum value across all records.

Min([Profit])

Mode(Value)

Returns the mode of the values.

Mode([Profit])

Median(Value)

Returns the median of the values.

Median([Profit])

Sum(Value)

Returns the sum of all values.

Sum([Profit])

Var(Value)

Returns an estimate of the variance of a population, where the sample is a subset of the entire population.

Var([Orders])

Varp(Value)

Returns the variance of a population, where the population is the entire data to be summarized.

Varp([Orders])

StdDev(Value)

Returns an estimate of the standard deviation of a population, where the sample is a subset of the entire population.

StdDev([Orders])

StdDevp(Value)

Returns the standard deviation of a population, where the population is the entire data to be summarized.

StdDevp([Orders])

Advanced Functions

aggr(SummaryExpression, Dimensions)

Uses the detail level specified by a predefined set of dimensions and a specified summary function to aggregate underlying data.

aggr(Sum([Sales]), [Category], [Product])

joinRule(dimension, searchValue)

Specifies the dimension for the ‘aggr’ function and a column whose values are used in the join operation after aggregation is completed. The joinRule function can be used only as an aggr function argument.

aggr(Sum(Sales), joinRule(GetYear(Date), GetYear(Date) - 1))

w(WindowExpression, partitionByFunction, orderByFunction)

Calculates aggregated values with the specified window function for the window defined by the specified partitioning and ordering. w(RankDense(Sum([ProductSales]), 'desc'), partitionBy([CategoryName]), orderBy())

partitionBy(column1, column2, …)

Specifies the columns by which the data rows are grouped. The window function is applied to each partition separately. The partitionBy function can be used only as a w function argument.

partitionBy([Product])

orderBy(column1, column2, …)

Specifies the calculation order for the window function. The orderBy function can be used only as a w function argument.

orderBy(GetYear([Date]), desc(Sum([Sales])))

asc(column)

Specifies that the values in the specified column are sorted in ascending order. This is the default sort order. The asc function can be used only as a w function argument. w(Index(), partitionBy(), orderBy(asc(Sum([Sales]))))

desc(column)

Specifies that the values in the specified column are sorted in descending order. The desc function can be used only as a w function argument. w(Index(), partitionBy(), orderBy(desc(Sum([Sales]))))

filter(SummaryExpression, filterCriteria)

Filters underlying data with specified filter criteria and aggregates filtered data using current detail level and the summary function.

filter(Sum([Sales]), GetYear([Date]) = 2017)

filter(Sum([Sales]), [Category] = 'Bikes')

Window Functions

Index()

Returns the index of the current row in the window.

Index()

Size()

Returns the number of rows in the window.

Size()

Lookup(SummaryExpression, Position)

Returns the value of the expression in a target position specified as a relative offset from the current position.

Lookup(Sum([Sales]), 3)

Last()

Returns the number of rows from the current row to the last row in the window.

Last()

First()

Returns the number of rows from the current row to the first row in the window.

First()

RankCompetition(SummaryExpression, [ ‘asc’ | ‘desc’ ])

Returns the standard competition rank for the current row in the window.

RankCompetition(Sum([Sales]), 'asc')

RankDense(SummaryExpression, [ ‘asc’ | ‘desc’ ])

Returns the dense rank for the current row in the window.

RankDense(Sum([Sales]), 'asc')

RankUnique(SummaryExpression, [ ‘asc’ | ‘desc’ ])

Returns the unique rank for the current row in the window.

RankUnique(Sum([Sales]), 'asc') RankModified(SummaryExpression, [ ‘asc’ | ‘desc’ ]) Returns the modified competition rank for the current row in the window.

RankModified(Sum([Sales]), 'asc') RankPercentile(SummaryExpression, [ ‘asc’ | ‘desc’ ]) Returns the percentile rank for the current row in the window.

RankPercentile(Sum([Sales]), 'desc')

RunningAvg(SummaryExpression)

Returns the running average of the specified expression from the first row in the window to the current row.

RunningAvg(Sum([Sales]))

RunningAggregate(SummaryExpression, CustomAggregateName)

Returns the cumulative result of the custom function applied to specified expression from the first row in the window to the current row.

RunningAggregate(Min([City]), 'StringConcat')

RunningCount(SummaryExpression)

Returns the running count of the specified expression from the first row in the window to the current row.

RunningCount(Sum([Sales]))

RunningMax(SummaryExpression)

Returns the running maximum of the specified expression from the first row in the window to the current row.

RunningMax(Sum([Sales]))

RunningMin(SummaryExpression)

Returns the running minimum of the specified expression from the first row in the window to the current row.

RunningMin(Sum([Sales]))

RunningSum(SummaryExpression)

Returns the running sum of the specified expression from the first row in the window to the current row.

RunningSum(Sum([Sales]))

WindowAggregate(SummaryExpression, StartOffset, EndOffset, CustomAggregateName)

Returns the result of the custom function applied to the specified expression in the window.

WindowAggregate(Min([CompanyName]), -1, 1, 'StringConcat')

WindowAvg(SummaryExpression, StartOffset, EndOffset)

Returns the average of the expression within the window, which is defined using offsets from the current row.

WindowAvg(Sum([Sales]), First(), Last())

WindowCount(SummaryExpression, StartOffset, EndOffset)

Returns the count of the expression within the window.

WindowCount(Sum([Sales]), First()+2, Last())

WindowCountDistinct(SummaryExpression, StartOffset, EndOffset)

Returns the distinct count of the expression within the window.

WindowCountDistinct(Sum([Sales]), First(), Last())

WindowFirstValue(SummaryExpression, StartOffset, EndOffset)

Returns the first value of the expression within the window.

WindowFirstValue((Sum([Sales]), First(), Last())

WindowMax(SummaryExpression, StartOffset, EndOffset)

Returns the maximum of the expression within the window.

WindowMax(Sum([Sales]), First(), Last())

WindowMin(SummaryExpression, StartOffset, EndOffset)

Returns the minimum of the expression within the window.

WindowMin(Sum([Sales]), First(), Last())

WindowMode(SummaryExpression, StartOffset, EndOffset)

Returns the statistical mode in the window (the element that occurs most often in the values calculated with the specified expression within the window).

WindowMode(Sum([Sales]), First(), Last())

WindowMedian(SummaryExpression, StartOffset, EndOffset)

Returns the median of the expression within the window.

WindowMedian(Sum([Sales]), First(), Last())

WindowSum(SummaryExpression, StartOffset, EndOffset)

Returns the sum of the expression within the window.

WindowSum(Sum([Sales]), First()+2, Last())

WindowVar(SummaryExpression, StartOffset, EndOffset)

Returns the variance of the expression within the window.

WindowVar(Sum([Sales]), First(), Last())

WindowVarp(SummaryExpression, StartOffset, EndOffset)

Returns the biased variance of the expression within the window.

WindowVarp(Sum([Sales]), First(), Last())

WindowStdDev(SummaryExpression, StartOffset, EndOffset)

Returns the sample standard deviation of the expression within the window.

WindowStdDev(Sum([Sales]), First(), Last())

WindowStdDevp(SummaryExpression, StartOffset, EndOffset)

Returns the biased standard deviation of the expression within the window.

WindowStdDevp(Sum([Sales]), First(), Last()) Total(SummaryExpression)

Returns the total for the specified expression in a calculation window. Note that the Total function calculates the total based on values from the underlying data source.

Total(Sum([Sales]))

Date and Time Functions

IsThisWeek(Date)

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

IsThisWeek([OrderDate])

IsThisMonth(Date)

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)”).

IsThisMonth([OrderDate])

IsThisYear(Date)

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

IsThisYear([OrderDate])

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.

AddMonths(LocalDateTimeLastMonth(), 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.

AddYears(LocalDateTimeLastYear(), 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.

AddMonths(LocalDateTimeTwoMonthAway(), 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.

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.

AddYears(LocalDateTimeYearBeforeToday(), 5)

InDateRange(Date, FromDate, ToDate)

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.

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

IsJanuary(Date)

Returns True if the specified date falls within January.

IsJanuary([OrderDate])

IsFebruary(Date)

Returns True if the specified date falls within February.

IsFebruary([OrderDate])

IsMarch(Date)

Returns True if the specified date falls within March.

IsMarch([OrderDate])

IsApril(Date)

Returns True if the specified date falls within April.

IsApril([OrderDate])

IsMay(Date)

Returns True if the specified date falls within May.

IsMay([OrderDate])

IsJune(Date)

Returns True if the specified date falls within June.

IsJune([OrderDate])

IsJuly(Date)

Returns True if the specified date falls within July.

IsJuly([OrderDate])

IsAugust(Date)

Returns True if the specified date falls within August.

IsAugust([OrderDate])

IsSeptember(Date)

Returns True if the specified date falls within September.

IsSeptember([OrderDate])

IsOctober(Date)

Returns True if the specified date falls within October.

IsOctober([OrderDate])

IsNovember(Date)

Returns True if the specified date falls within November.

IsNovember([OrderDate])

IsDecember(Date)

Returns True if the specified date falls within December.

IsDecember([OrderDate])

IsLastMonth(Date)

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

IsLastMonth([OrderDate])

IsLastYear(Date)

Returns True if the specified date falls within the previous year.

IsLastYear([OrderDate])

IsNextMonth(Date)

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

IsNextMonth([OrderDate])

IsNextYear(Date)

Returns True if the specified date falls within the next year.

IsNextYear([OrderDate])

IsYearToDate(Date)

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

IsSameDay([Date1], [Date2])

Returns True if the specified date/time value falls within the same day.

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

IsSameDay([OrderDate], [ShippedDate])

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.

AddDays(LocalDateTimeDayAfterTomorrow(), 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.

AddDays(LocalDateTimeLastWeek(), 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.

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.

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.

AddYears(LocalDateTimeNextYear(), 5)

LocalDateTimeNow()

Returns the DateTime value that is the current moment in time.

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.

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.

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.

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.

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.

AddDays(LocalDateTimeTomorrow(), 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.

AddDays(LocalDateTimeTwoWeeksAway(), 5)

LocalDateTimeYesterday()

Returns the DateTime value with the date part that is the previous day, and the time part of 00:00:00.

AddDays(LocalDateTimeYesterday(), 5)

AddTicks(DateTime, AddTicks)

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.

AddTicks([OrderDate], 500000)

AddMilliSeconds(Time, 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.

AddMilliSeconds([StartTime], 200)

AddSeconds(Time, 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.

AddSeconds([StartTime], 120)

AddMinutes(Time, 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.

AddMinutes([StartTime], 30)

AddHours(Time, 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.

AddHours([StartTime], 5)

AddDays(Date, 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.

AddDays([OrderDate], 10)

AddMonths(Date, 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.

AddMonths([OrderDate], 3)

AddYears(Date, 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.

AddYears([OrderDate], 2)

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.

AddTimeSpan([OrderDate], [Duration])

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.

DateDiffDay([OrderDate], 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.

DateDiffMonth([OrderDate], 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.

DateDiffYear([OrderDate], Now())

DateDiffMilliSecond(StartTime, EndTime)

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.

DateDiffMilliSecond([StartTime], Now())

DateDiffMinute(StartTime, EndTime)

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.

DateDiffMinute([StartTime], Now())

DateDiffSecond(StartTime, EndTime)

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.

DateDiffSecond([StartTime], Now())

DateDiffHour(StartTime, EndTime)

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.

DateDiffHour([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.

DateDiffTick([OrderDate], Now())

GetDate(Date)

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.

GetDate([OrderDate])

GetYear(Date)

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.

GetYear([OrderDate])

GetMonth(Date)

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.

GetMonth([OrderDate])

GetDay(Date)

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.

GetDay([OrderDate])

GetDayOfYear(Date)

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.

GetDayOfYear([OrderDate])

GetDayOfWeek(Date)

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.

GetDayOfWeek([OrderDate])

GetHour(Time)

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.

GetHour([StartTime])

GetMinute(Time)

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.

GetMinute([StartTime])

GetSecond(Time)

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.

GetSecond([StartTime])

GetMilliSecond(Time)

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.

GetMilliSecond([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.

GetTimeOfDay([OrderDate])

Now()

Returns the DateTime value that is the current date and time.

AddDays(Now(), 7)

Today()

Returns a DateTime value that is the current date. The time part is set to 00:00:00.

AddDays(Today(), 3)

UtcNow()

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

AddDays(UtcNow(), 7)

DateTimeFromParts(Year, Month, Day)

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.

DateTimeFromParts([Year], [Month], [Day])

DateTimeFromParts(Year, Month, Day, Hour)

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.

DateTimeFromParts([Year], [Month], [Day], [Hour])

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

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.

DateTimeFromParts([Year], [Month], [Day], [Hour], [Minute])

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

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.

DateTimeFromParts([Year], [Month], [Day], [Hour], [Minute], [Second])

DateOnlyFromParts(Year, Month, Day)

Returns a DateOnly value constructed from the specified Year, Month, and Day.

The operands are:

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

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

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

DateOnlyFromParts([Year], [Month], [Day])

TimeOnlyFromParts(Hour, Minute)

Returns a TimeOnly value constructed from the specified hour, minute, seconds (optional), and milliseconds (optional).

TimeOnlyFromParts([Hour], [Minute])

TimeOnlyFromParts(Hour, Minute, Second)

Returns a TimeOnly value constructed from the specified hour, minute, seconds (optional), and milliseconds (optional).

TimeOnlyFromParts([Hour], [Minute], [Second])

TimeOnlyFromParts(Hour, Minute, Second, Millisecond)

Returns a TimeOnly value constructed from the specified hour, minute, seconds (optional), and milliseconds (optional).

TimeOnlyFromParts([Hour], [Minute], [Second], [Millisecond])

AfterMidday(Time)

Returns True if the specified time is after 12:00 PM.

AfterMidday([StartTime])

BeforeMidday(Time)

Returns True if the specified time is before 12:00 PM.

BeforeMidday([StartTime])

IsAfternoon(Time)

Returns True if the specified time falls between 12:00 PM and 6:00 PM.

IsAfternoon([StartTime])

IsEvening(Time)

Returns True if the specified time falls between 6:00 PM and 9:00 PM.

IsEvening([StartTime])

IsFreeTime(Time)

Returns True if the specified time falls within free time.

IsFreeTime([StartTime])

IsLastHour(Time)

Returns True if the specified time falls within the last hour.

IsLastHour([StartTime])

IsLunchTime(Time)

Returns True if the specified time falls within the lunch time.

IsLunchTime([StartTime])

IsMorning(Time)

Returns True if the specified time falls within between 6:00 AM and 12:00 PM.

IsMorning([StartTime])

IsNextHour(Time)

Returns True if the specified time falls within the next hour.

IsNextHour([StartTime])

IsNight(Time)

Returns True if the specified time falls between 9:00 PM and 9:00 AM.

IsNight([StartTime])

IsSameHour(Time)

Returns True if the specified time falls within the same hour.

IsSameHour([StartTime])

IsSameTime(Time)

Returns True if the specified time falls within the same time of day (hour and minute).

IsSameTime([StartTime])

IsThisHour(Time)

Returns True if the specified time falls within the hour.

IsThisHour([StartTime])

IsWorkTime(Time)

Returns True if the specified time falls within work time.

IsWorkTime([StartTime])

Logical Functions

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, 0)

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

InRange(Value1, Value2, Value3)

Returns True if Value1 is within the range defined by Value2 and Value3.

InRange([ProductPrice], 10.00, 50.00)

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

ToBoolean(Value)

Converts Value to an equivalent Boolean value.

ToBoolean([Value])

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

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

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(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.

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

Note

You can find a complete set of available function operators in the FunctionOperatorType enumeration description.

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)
• ==, !=
• <, >, <=,>=
• -, + (left-associative)
• *, /, % (left-associative)
• NOT
• unary -
• In
• Iif
• Trim(), Len(), Substring(), IsNull()
• [] (for set-restriction)
• ()

The default precedence can be changed by grouping elements with parentheses. For instance, the operators are performed in a default order in the first of the following two code samples. In the second code sample, the addition operation is performed first, because its associated elements are grouped with parentheses, and the multiplication operation is performed last.

Amount == 2 + 48 * 2

Amount == (2 + 48) * 2

Case Sensitivity

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

Note

A data source affects certain operators’ behavior. For instance, by default, the 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 an escape character (@ sign). In the expression below, the CriteriaOperator.Parse method interprets @Or as the field named Or, not the logical operator OR.

@Or = 'value'

Escape Characters

Use a backslash \ as an escape character for characters in expressions.

Examples:

• \[
• \\
• \'

Expression Comments

You can add a comment to your expression to explain it and make the expression more readable.

Comments are multi-line and begin with /* and end with */:

GetYear([SalesDate]) /* It is a comment. Here you can add a function description. */

Server Mode Limitations

In server mode, the Dashboard does not have simultaneous access to bound data in its entirety. This imposes the following limitations to functions:

• The Count Distinct summary function is not supported for the following database engines:

  • Microsoft Access
  • Microsoft SQL Server CE
• The Median summary function is supported for the Oracle data provider only.
• The Mode summary function is supported for the Oracle and PostgreSQL data providers only.

Example - How to Use Window Functions in Calculated Fields

This example emulates the standard Percent of Total window calculation behavior in the calculated field‘s expression.

You cannot include the window functions in a calculated field directly. To support window functions inside a calculated field expression, use the w-Function.

View Example