Compute Expression Syntax

When you create an expression, use property names to refer to property values. For example, if one data property is “UnitPrice” and another is “Quantity”, the expression would be:

UnitPrice * Quantity

Filtering

When you create an expression for a filter, enclose strings within single quotation marks:

LastName = 'Jones'

Reserved words

If a data property name contains any non-alphanumeric characters or starts with a digit or matches (case-insensitively) any of the following reserved words, it requires special handling, as described in the following paragraphs.

• And
• Between
• Child
• False
• In
• Is
• Like
• Not
• Null
• Or
• Parent
• True

If a data property name satisfies one of the above conditions, it must be wrapped in either square brackets or the ”`” (grave accent) quotes. For example, to use a data property named “Column#” in an expression, you would write either “[Column#]”:

Total * [Column#]

or `Column#`:

Total * `Column#`

User-Defined Values

User-defined values may be used within expressions to be compared with data property values. String values should be enclosed within single quotation marks (and each single quotation character in a string value has to be escaped by prepending it with another single quotation character). Date values should be enclosed within pound signs (#) or single quotes (’). Decimals and scientific notation are permissible for numeric values. For example:

FirstName = 'John'

Price <= 50.00

Birthdate < #1/31/82#

For data properties that contain enumeration values, cast the value to an integer data type. For example:

EnumColumn = 5

Operators

Logical operations are allowed using the Boolean AND, OR and NOT operators. You can use parentheses to group clauses and force precedence. The AND operator has precedence over other operators. For example:

(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'

When you create comparison expressions, the following operators are allowed:

• <
• >
• <=
• >=
• <>
• =
• IN
• LIKE

The following arithmetic operators are also supported in expressions:

• + (addition)
• - (subtraction)
• * (multiplication)
• / (division)
• % (modulus)

To concatenate a string, use the + character. For example:

FirstName + ' ' + Surname

Wildcard Characters

Both the * and % can be used interchangeably for wildcard characters in a LIKE comparison. If the string in a LIKE clause contains a * or %, those characters should be enclosed in brackets ([]). If a bracket is in the clause, each bracket character should be enclosed in brackets (for example [[] or []]). A wildcard is allowed at the start and end of a pattern, or at the end of a pattern, or at the start of a pattern. For example:

ItemName LIKE '*product\*'

ItemName LIKE '*product'

ItemName LIKE 'product*'

Wildcard characters are not allowed in the middle of a string. For example, ‘te*xt’ is not allowed.

Aggregates

The following aggregate types are supported:

• Sum (Sum)
• Avg (Average)
• Min (Minimum)
• Max (Maximum)
• Count (Count)
• StDev (Statistical standard deviation)
• Var (Statistical variance).

Create an aggregate expression by using one of the functions listed earlier. For example:

Avg(Price)

Avg(Orders.Price)

Sum(Price)

Note

If you use a single array to create an aggregate, there would be no group-by functionality. Instead, all rows would display the same value in the data property.

If an array has no rows, the aggregate functions will return null.

You can also convert data types using the Convert function, shown in the following section.

An aggregate can only be applied to a single data property and no other expressions can be used inside the aggregate.

Functions

The following functions are also supported:

CONVERT

Description	Converts particular expression to a specified Type.
Syntax	Convert(expression, type)
Arguments	expression — The expression to convert. type — .NET type to which the value will be converted.

Example: Convert(total, 'System.Int32').

All conversions are valid with the following exceptions: Boolean can be coerced to and from Byte, SByte, Int16, Int32, Int64, UInt16, UInt32, UInt64, String and itself only. Char can be coerced to and from Int32, UInt32, String, and itself only. DateTime can be coerced to and from String and itself only. TimeSpan can be coerced to and from String and itself only.

LEN

Description	Gets the length of a string
Syntax	LEN(expression)
Arguments	expression — The string to be evaluated.

Example: Len(PropertyName).

ISNULL

Description	Checks an expression and either returns the checked expression or a replacement value.
Syntax	ISNULL(expression, replacementvalue)
Arguments	expression — The expression to check. replacementvalue — If expression is null, replacementvalue is returned.

Example: IsNull(price, -1).

IIF

Description	Gets one of two values depending on the result of a logical expression.
Syntax	IIF(expr, truepart, falsepart)
Arguments	expr — The expression to evaluate. truepart — The value to return if the expression is true. falsepart — The value to return if the expression is false.

Example: IIF(total>1000, 'expensive', 'dear').

TRIM

Description	Removes all leading and trailing blank characters like \r, \n, \t, ' '
Syntax	TRIM(expression)
Arguments	expression — The expression to trim.

SUBSTRING

Description	Gets a sub-string of a specified length, starting at a specified point in the string.
Syntax	SUBSTRING(expression, start, length)
Arguments	expression — The source string for the substring. start — Integer that specifies where the substring starts. length — Integer that specifies the length of the substring.

Example: SUBSTRING(phone, 7, 8).