Excel Data Source filter expressions

Examples of filter expressions

This example describes syntax of Filter expression. It shows how to correctly build expression string using methods to escape values.

Column names

If a column name contains any of these special characters ~ ( ) # \ / = > < + - * % & | ^ ' " [ ], you must enclose the column name within square brackets [ ]. If a column name contains right bracket ] or backslash \, escape it with backslash (\] or \\).

Filter = "id = 10"; // no special character in column name "id"

Filter = "$id = 10"; // no special character in column name "$id"

Filter = "[#id] = 10"; // special character "#" in column name "#id"

Filter = "[[id\]] = 10"; // special characters in column name "[id]"

Literals

String values are enclosed within single quotes ' '. If the string contains single quote ', the quote must be doubled.

Filter = "Name = 'John'" // string value

Filter = "Name = 'John ''A'''" // string with single quotes "John 'A'"

Date values are enclosed within sharp characters # #.

Filter = "Date = #12/31/2008#" // date value (time is 00:00:00)

Filter = "Date = #2008-12-31#" // also this format is supported

Filter = "Date = #12/31/2008 16:44:58#" // date and time value

Alternatively you can enclose all values within single quotes ' '. It means you can use string values for numbers or date time values. In this case the current culture is used to convert the string to the specific value.

Filter = "Date = '12/31/2008 16:44:58'" // if current culture is English

Filter = "Date = '31.12.2008 16:44:58'" // if current culture is German

Filter = "PVEUHI = '1199.90'" // if current culture is English

Filter = "PVEUHI = '1199,90'" // if current culture is German

Comparison operators

Equal, not equal, less, greater operators are used to include only values that suit to a comparison expression. You can use these operators = <> < <= > >=.

Note: String comparison is culture-sensitive.

Filter = "BOXNUM = '10'" // number is equal to 10

Filter = "Date < #1/1/2008#" // date is less than 1/1/2008

Filter = "Name = '2FC100'" // string is equal to '2FC100'

Filter = "Name <> '2FC100'" // string is not equal to '2FC100'

Filter = "Name >= '2F'" // string comparison

Operator IN is used to include only values from the list. You can use the operator for all data types, such as numbers or strings.

Filter = "BOXNUM IN (10, 12, 14)" // integer values

Filter = "PVEULO IN (0.0, -10.0, -100.0)" // float values

Filter = "Name IN ('FC100', 'FC110', 'FC120')" // string values

Filter = "Date IN (#12/31/2008#, #1/1/2009#)" // date time values

Filter = "BOXNUM NOT IN (21, 22, 23)" // values not from the list

Operator LIKE is used to include only values that match a pattern with wildcards. Wildcard character is * or %, it can be at the beginning of a pattern '*value', at the end 'value*', or at both '*value*'. Wildcard in the middle of a pattern 'va*lue' is not allowed.

Filter = "Name LIKE '2F*'" // values that start with '2F'

Filter = "Name LIKE '%FC%'" // values that contain 'FC'

Filter = "Name NOT LIKE '2*'" // values that don't start with '2'

If a pattern in a LIKE clause contains any of these special characters * % [ ], those characters must be escaped in brackets [ ] like this [*], [%], [[] or []].

Filter = "Name LIKE '[*]*'" // values that starts with '*'

Filter = "Name LIKE '[[]*'" // values that starts with '['

Boolean operators

Boolean operators AND, OR and NOT are used to concatenate expressions. Operator NOT has precedence over AND operator and it has precedence over OR operator.

Operator AND has precedence over OR operator, parenthesis are needed

Filter = "HWYNUM = '02' AND (BOXNUM = '20' OR BOXNUM = '24')";

Following examples do the same

Filter = "HWYNUM <> '02' AND PNTBOXTY = 'EC'";

Filter = "HWYNUM = '02' AND NOT PNTBOXTY = 'EC'";

Filter = "NOT (ALGIDDAC = 'PIDNORM' OR ALGIDDAC = 'DAS')";

Filter = "ALGIDDAC NOT IN ('PIDNORM', 'DAS')";

Arithmetic and string operators

Arithmetic operators are addition +, subtraction -, multiplication *, division / and modulus %.

Filter = "PVEUHI - PVEULO < 10"; // PV range < 10.0

Filter = "PVEUHI % 100 = 0"; // PV range evenly divisible by 100

Aggregate Functions

These aggregate functions are supported: SUM, COUNT, MIN, MAX, AVG (average), STDEV (statistical standard deviation) and VAR (statistical variance).

This example shows aggregate function performed on a single table.

Select entities with above-average PV range

Filter = "PVEUHI - PVEULO > AVG(PVEUHI - PVEULO)";

Select entities which have more than 1 input

Filter = "COUNT(NMBRINPT) > 1";

Functions

There are also the following functions that can be applied to a data column.

CONVERT – converts particular expression to a specified .NET Framework type

Syntax: CONVERT(expression, type)

Example: CONVERT(NUMINPTS, 'System.Int32')

LEN – gets the length of a string

Syntax: LEN(expression)

Example: LEN(TAG) > 0

ISNULL – checks an expression and either returns the checked expression or a replacement value

Syntax: ISNULL(expression, replacementvalue)

Example: ISNULL(PVEUHI, 0)

IIF – gets one of two values depending on the result of a logical expression

Syntax: IIF(expr, truepart, falsepart)

Example: IIF(PVEUHI>1000, 'D0', 'D1')

TRIM – removes all leading and trailing blank characters like \r, \n, \t, ‚ ‘

Syntax: TRIM(expression)

Example: TRIM(DESC) = 'ONLINE'

SUBSTRING – gets a sub-string of a specified length, starting at a specified point in the string

Syntax: SUBSTRING(expression, start, length)

Example: SUBSTRING(NAME, 1, 2)

Tips & Tricks

Common Failures

The column in the Excel sheet cannot automatically infer its type. When columns contain a mix of data such as a numeric and string, Excel may not be able to establish the column type as a Text type. Functions that work with string values may fail, which requires you to select the Excel column and set its format type to Text.
When working with number types such as data converted from a Honeywell Hiway EB file, data values such as HWYNUM, BOXNUM, etc. may need to be enclosed within single quote characters as in HWYNUM = '06' and BOXNUM = '22' when used in a Filter expression.