Here you will define expressions that perform the required calculation. Create or modify an expression by:
•typing into the main input area; entries are suggested as you type and are refined as you continue typing so you can select the required entry by clicking on it or pressing Enter.
•clicking on a button to incorporate an operator, as follows:
|
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. |
You can also click a:
•Field to see any notes relating to a field you have selected for inclusion in the report. You cannot select these fields here but must select them as part of your typed entry.
•Function to see the required syntax for that function so that you can key it in correctly. The available functions are:
|
Function |
Description |
|
RTRIM (value) |
Removes any trailing whitespace from text e.g. RTRIM([ATDesc]) |
|
LTRIM (value) |
Removes any leading whitespace from text e.g. LTRIM([ATDesc]) |
|
SUBSTRING (Value, Start, Length) |
Returns the requested section of text e.g. SUBSTRING(‘MyString’,1,2) |
|
DATEPART (Part, Date) |
Returns a number representing the specified part of the date. See date formats below. e.g. DATEPART(M,[TSDate]) |
|
LEN(Value) |
Returns the number of characters within the supplied text e.g. LEN(‘MyString’) |
|
ISNULL (Value, With) |
If the value is null it is replaced e.g. ISNULL(NULL,’X’) |
|
IIF (Expression, True, False) |
If the expression is true replace with the “True” value else replace with the “False” value e.g. IIF([COCode]=’CST001’,’A’,’B’) |
|
CONVERT (Type, Input [, Style]) |
Converts one data type to another. See Data types below. See Styles below. e.g. CONVERT(MONEY,123456) e.g. CONVERT(DATETIME,’DD/MM/YYYY’,103) |
|
CASE WHEN condition 1 THEN result 1 WHEN condition 2 THEN result 2 ELSE result 3 END |
Evaluates a number of conditions and returns the appropriate result |
|
IN (item1, item2, …) |
Is the value one of the items listed e.g. IIF([COCode] IN ('CST001', 'CST002'), 'YES', 'NO') |
|
EXISTS |
|
|
LIKE(String) |
Does the value match the supplied string % Any number of characters _ Any single character [] Any single character within the range or set e.g. [a-f] or [abcdef] [^] Any single character not within the range or set e.g. [^a-f] or [^abcdef] e.g. IIF([COCode] LIKE ('CST%'), 'YES', 'NO') |
|
NOT |
Reverses the value of a Boolean expression e.g. IIF([COCode] NOT LIKE ('CST%'), 'YES', 'NO') |
|
OR |
Returns True if any of the compared conditions are True e.g. IIF([COCode]='CST001' OR [COCode]='CST002', 'YES', 'NO') |
|
AND |
Returns True if all the compared conditions are True e.g. IIF([COCode]='CST001' AND [TSIsInvoiced]=TRUE, 'YES', 'NO') |
|
BETWEEN value1 AND value2 |
returns True if the test value is greater than or equal to value1 and less than or equal to value2 e.g. IIF([TSYear] BETWEEN 2015 AND 2020,'YES', 'NO') |
|
DISTINCT |
|
|
GETDATE () |
Returns the system date e.g. GETDATE() |
|
TRUE |
Used to check the status of a Boolean field e.g. IIF([TSChargeable]=TRUE,’YES’,’NO’) |
|
FALSE |
Used to check the status of a Boolean field e.g. IIF([TSChargeable]=FALSE,’YES’,’NO’) |
|
CONCAT ( string_value1, string_value2, string_valueN ] )
|
Concatenate fields together to create a single string e.g. CONCAT([Start Date], ‘:’, [Project Name], ‘:’ [Total]) for the result 10/01/2020:My Project:1234 |
For the CONVERT function, data types are:
|
Type |
Description |
|
BIGINT |
-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) |
|
INT |
-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) |
|
SMALLINT |
-2^15 (-32,768) to 2^15-1 (32,767) |
|
TINYINT |
0 to 255 |
|
BIT |
An integer data type that can take a value of 1, 0, or NULL. |
|
DECIMAL |
Numeric data types that have fixed precision and scale. Decimal and numeric are synonyms and can be used interchangeably. |
|
NUMERIC |
Numeric data types that have fixed precision and scale. Decimal and numeric are synonyms and can be used interchangeably. |
|
MONEY |
Data types that represent monetary or currency values. |
|
FLOAT |
Approximate-number data types for use with floating point numeric data |
|
REAL |
|
|
DATETIME |
Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock. |
|
SMALLDATETIME |
Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds. |
|
CHAR |
Fixed-size string data |
|
VARCHAR |
Variable-size string data |
|
TEXT |
Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647) |
|
NCHAR |
Fixed-size string data |
|
NVARCHAR |
Variable-size string data. |
|
NTEXT |
Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823) bytes |
|
BINARY |
binary [(n)] Fixed-length binary data with a length of n bytes |
and styles are:
|
Style |
Standard |
Description |
|
1 |
US |
mm/dd/yy |
|
101 |
US |
mm/dd/yyyy |
|
2 |
ANSI |
yy.mm.dd |
|
102 |
ANSI |
yyyy.mm.dd |
|
3 |
British / French |
dd/mm/yy |
|
103 |
British / French |
dd/mm/yyyy |
|
4 |
German |
dd.mm.yy |
|
104 |
German |
dd.mm.yyyy |
|
5 |
Italian |
dd-mm-yy |
|
105 |
Italian |
dd-mm-yyyy |
|
6 |
|
dd mon yy |
|
106 |
|
dd mon yyyy |
|
7 |
|
Mon dd, yy |
|
107 |
|
Mon dd, yyyy |
|
8, 24 or 108 |
|
hh:mi:ss |
|
9 or 109 |
|
mon dd yyyy hh:mi:ss:mmmAM (or PM) |
|
10 |
US |
mm-dd-yy |
|
110 |
US |
mm-dd-yyyy |
|
11 |
Japan |
yy/mm/dd |
|
111 |
Japan |
yyyy/mm/dd |
|
12 |
ISO |
yymmdd |
|
112 |
ISO |
yyyymmdd |
|
13 or 113 |
Europe Default + Milliseconds |
dd mon yyyy hh:mi:ss:mmm (24h) |
|
14 or 114 |
|
hh:mi:ss:mmm (24h) |
For the DATEPART function, data format strings are:
|
Format |
Abbreviation |
|
DAYOFYEAR |
DY, Y |
|
DAY |
DD, D |
|
WEEK |
WK, WW |
|
WEEKDAY |
DW |
|
MONTH |
MM, M |
|
QUARTER |
QQ, Q |
|
YEAR |
YY, YYYY |
|
HOUR |
HH |
|
MINUTE |
MI, N |
|
SECOND |
SS, S |
|
MILLISECOND |
MS |
|
MICROSECOND |
MCS |
|
NANOSECOND |
NS |
|
TZOFFSET |
TZ |
|
ISO_WEEK |
ISOWK, ISOWW |
Once you complete the expression, click Apply to incorporate the calculated field into the report. A warning appears if the expression is incomplete or otherwise invalid.