Advanced calendar roll-ups support complex filtering, which allows for SQL-like information queries. They can be comprised of any number of fields and values. For example, you can build a filter that looks for records that are not started, due within seven days, and have a high priority.
When defining complex filters, concatenation is allowed using
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'
The following operators are allowed when creating comparison complex filters:
· <
· >
· <=
· >=
· <>
· =
· IN
· LIKE
The following arithmetic operators are also supported in complex filters:
· + (addition)
· - (subtraction)
· * (multiplication)
· / (division)
· % (modulus)
Use the + character to concatenate a string. Whether string comparisons are case-sensitive or not is determined by the value of the DataSet class's CaseSensitive property. However, you can override that value with the DataTable class's CaseSensitive property.
Both the * and % characters can be used interchangeably as wildcards in a LIKE comparison. If the string in a LIKE clause contains a * or %, those characters should be escaped in brackets ([]). If a bracket is in the clause, the bracket characters should be escaped in brackets (for example [[] or []]). A wildcard is allowed at the beginning of a pattern, at the end of a pattern, or both. For example:
· ItemName LIKE '*product*'
· ItemName LIKE '*product'
· ItemName LIKE 'product*'
Wildcards are not allowed in the middle of a string. For example, 'te*xt' is not allowed.
The following aggregate types are supported:
· Sum (Sum)
· Avg (Average)
· Min (Minimum)
· Max (Maximum)
· Count (Count)
· StDev (Statistical standard deviation)
· Var (Statistical variance).
When creating a complex filter, use the Column Name to refer to columns. For example, if the Column Name for one column is "UnitPrice" and another is "Quantity", the complex filter would be: UnitPrice * Quantity
Also, enclose strings with single quotes: LastName = 'Jones'
The following characters are special characters and must be escaped (wrapped in brackets) if they are used in a column name, as explained below.
|
\n (newline) |
+ |
|
\t (tab) |
- |
|
\r (carriage return) |
* |
|
~ |
% |
|
( |
& |
|
) |
| |
|
# |
^ |
|
\ |
' |
|
/ |
" |
|
= |
[ |
|
> |
] |
|
< |
|
If a column name contains one of the above characters, the
name must be wrapped in brackets. For example, to use a column named Column# in
a complex filter, you would reference the column as shown here:
Total *
[Column#]
Because brackets are special characters, you must use a slash
(\) to escape the bracket if it is part of a column name. For example, a column
named Column[] would be written:
Total * [Column[\]] (Only the second
bracket must be escaped.)
The same holds true if a column name contains a space. For
example, to use a column named First Name in a complex filter, you would type
the column name as shown here:
[First Name]='John'
User-defined values may be used within complex filters and compared against column values. String values should be enclosed within single quotes. Date values should be enclosed within pound signs (#). Decimals and scientific notations are permissible for numeric values. For example:
· FirstName = 'John'
· Price <= 50.00
· Birthdate < #1/31/82#
For columns that contain enumeration values, cast the value
to an integer data type. For example:
EnumColumn = 5
If a column does not contain data, the use of a column='' is not permissible and will not return the results that you are looking for. Since the column row is empty, it will not respond with a String result, but rather a NULL response. Because of this, you need to use the "IS NULL" or "IS NOT NULL" request to see if a column contains or does not contain any data. For example:
· [First Name] IS NULL
· [First Name] IS NOT NULL