Truncate(x, #places)

Returns a number with extra decimal places removed.

For example, Truncate(123.45678, 2) returns 123.45.

Round(x, #places)

Returns a number rounded to the number of decimal places. For example, Round(123.45678, 2) returns 123.46.

Sum(field)

Get a sum for the whole report. That is, a grand total.

Sum(field, conditionfield)

Get a sum for a particular group of values.

Average(field)

Get an average for the whole report.

Average(field, conditionfield)

Get an average for a particular group of values.

Maximum(field)

Get the maximum value of the field for the whole report.

Maximum(field, conditionfield)

Get the maximum value in a particular group.

Minimum(field)

Get the minimum value of the field for the whole report.

Minimum(field, conditionfield)

Get the minimum value in a particular group.

Count(field)

Count the number of records for the whole report.

Count(field, conditionfield)

Count the number of records in a particular group.

Length(field)

Get the number of characters in a character string.

Trim(field)

Return the string field, without leading/trailing spaces.

TrimLeft(field)

Return the string field, without leading spaces.

TrimRight(field)

Return the string field, without trailing spaces.

UpperCase(field)

Return the string field converted to upper case.

LowerCase(field)

Return the string field converted to lower case.

ProperCase(field)

Return the string field converted to proper case.

For example, Title Case.

StrReverse(field)

Return the string field, with all characters reversed. That is, the last character first.

NumericText(field)

Returns true if the string is completely numeric (for example, 123.45). Returns false if any part of the string is non-numeric (for example, 124.23A).

ToNumber(field)

Converts the field from a string/currency/boolean and returns as a number.

ToText(field)

Convert the field from a number/currency/boolean to a string.

ToText(field, places)

Convert a number/currency field to a string with a set number of decimal places.

ToText(field, places, thousands)

Convert a number/currency field to a string with a set number of decimal places and a character to represent the thousands character.

ToWords(field)

Convert the numeric field to a word representation.

For example, 123 becomes one hundred twenty-three.

ReplicateString(string, number)

Returns the specified string the number of times.

For example, ReplicateString(‘xyz’, 3) returns ‘xyzxyzxyz’.

Space(number)

Returns a number of blank spaces. For example, Space(10) returns " ".

InStr(string1, string2)

Returns the position of string2, inside string1. For example, InStr(‘abcdefgh’, ‘de’) returns 4.

Mid(string, start, length)

Returns a part of a string, from start for a certain number of characters. For example, Mid(‘abcdefgh’, 4, 3) returns def.

Left(string, length)

Returns the left part of a string to a certain number of characters.

Right(string, length)

Returns the right part of a string to a certain number of characters.

Chr(number)

Returns the ASCII character for the number. For example, Chr(65) returns A.

Replace(inputString, findString, replaceString)

Replaces the part of inputString that matches findString, with replaceString. For example, Replace(‘abcdefgh’, ‘de’, ‘xy’) returns ‘abcxyfgh’.

Date(YYYY, MM, DD)

Returns a Date value for the year, month and day numbers passed in.

Year(datetime)

Returns the year part of a date or datetime value.

Month(datetime)

Returns the month part of a date or datetime value.

Day(datetime)

Returns the day part of a date or datetime value.

DateAdd(intervalType, number, datetime)

Adds the number of intervals (day, month, etc) to the datetime and returns the new date. For example, DateAdd(‘yyyy’, 3, CurrentDate) returns the current date plus three years.

DateDiff(intervalType, startdatetime, enddatetime)

Returns the difference between the two dates for the chosen interval. For example, DateDiff(‘d’, {@StartOfYear}, CurrentDate) returns the number of days between 01/01/2006 and 30/06/2006 as 180).

MakeArray(x, y, z, ….)

Returns an array containing all the items entered. For example, MakeArray(‘abc’, ‘123’, ‘xyz’) returns an array with three items in it (one for each string).

Switch(expression1, value1, expression2, value2, ……)

Evaluates each expression in turn (expressions must resolve to true or false) and returns the value associated with the first true expression. For example, Switch({Community.Gender} = ‘M’, ‘Male’, {Community.Gender} = ‘F’, ‘Female’, True, ‘Unknown’).

Note: The last expression is True, this is a catch-all for when the other expressions are not satisfied.

IIF(expression, truepart, falsepart)

Evaluates the expression (expressions must resolve to true or false). If:

• true, the truepart is returned,
• otherwise the falsepart is returned.

For example, IIF({@Amount} > 0, ‘Good’, ‘Bad’).

Previous(field)

Returns the value of the field, from the prior dataset record.

Next(field)

Returns the value of the field, from the next dataset record.

IsNull(field)

Returns true if the field value is null, otherwise false.

PageNumber

Returns the current page number of the report.

TotalPageCount

Returns the total number of pages in the report.

PageNofM

Returns, for example, Page 5 of 10.

OnFirstRecord

Returns true if the current record is the first one of the dataset.

OnLastRecord

Returns true if the current record is the last one of the dataset.

ReportTitle

Returns the title of the report as entered in File – Summary Info – Title.