Using the Expression Builder
Here you can key in values, paste fields and functions from those listed and insert operators or connectors by clicking the relevant button. The aim is to build valid expressions that will perform the required calculation. You can build complex calculations and brackets will be placed correctly around expressions.
•Operators are:
|
Symbol |
Description |
|
+ |
Addition |
|
- |
Subtraction |
|
* |
Multiplication |
|
/ |
Division |
|
% |
Modulus. This provides the remainder after dividing the first numeric expression by the second one. For example 9 % 3 = 0 (as 9 is divisible by 3, with no remainder). While 9 % 2 = 1 (as 2 goes into 9 4 times, with 1 remaining). |
|
= |
Equals |
|
> |
Greater Than |
|
< |
Less Than |
|
<> |
Not Equal To |
|
And |
Logical And. Performs logical conjunction on two Boolean expressions. |
|
Or |
Logical Or. Performs logical disjunction on two Boolean expressions. |
|
Not |
Logical Not. Performs logical negation on a Boolean expression. |
|
( ) |
Opening/Closing bracket. Used in conjunction with group statements in an expression and apply an order of precedence in which the statements are evaluated. |
• Fields listed are those that are selected for the report through the Fields tab. Select one and click 
to insert into the expression at the cursor position.
•Functions are:
|
Function |
Description |
|
CONVERT(expression,type) |
Converts a given expression to a specified .NET Framework Type. This function requires knowledge of column data types, and the .NET Framework data types, and is included primarily for advanced users. Simple conversions from numeric to string values can be done as follows: Example: CONVERT([numeric_ data_column], ‘System.String’) Data Types Include: - ‘System.String’ ‘System.Int32’ (for integer values) ‘System.Boolean’ (for true/false values) ‘System.Double’ (for decimal numbers) |
|
LEN(expression) |
Gets the length of a string Example: LEN([string_data_column]) |
|
ISNULL(expression, replacementvalue) |
Checks an expression and either returns the checked expression or a replacement value. Examples: ISNULL([string_data_column], ‘’) – replaces null values with a zero length string ISNULL([numberic_data_column], 0) – replaces null values with zero |
|
IIF(expression,truepart,falsepart) |
Gets one of two values depending on the result of a logical expression. Example: IIF ([numeric_data_column] > 0, [numeric_data_column] , 0) – if the column value is greater than zero the result will be the value in the column, if not the function will return zero. |
|
TRIM(expression) |
Removes all leading and trailing blank characters from a string value. Example: TRIM([string_data_column]) |
|
SUBSTRING(expression,start,length) |
Gets a sub-string of a specified length, starting at a specified point in the string. Example: SUBSTRING(‘ABCDEFG’,1,3) – returns ‘ABC’ |