Useful Crystal Reports functions
- Former user (Deleted)
- Richard Conroy (Deactivated)
- Former user (Deleted)
The following are functions and formulas used in Crystal Reports.
Some snippets that may be useful to your organisation are:
- ToText({Community.ID}, 0, '') – Returns the ID without any decimals or thousands separator.
- Count({vStudents.ID}, {vStudents.YearLevel}) – Returns the count of students per year level.
- Chr(252) – When used with WingDings font, returns the tick
. - Chr(251) – When used with WingDings font, returns the cross
. - {vStudents.Surname} + ' ' + {vStudents.Preferred} + ' ' + ToText({vStudents.ID}, 0, '') - Returns a single string containing the surname, preferred name and ID in the correct order, which can be used for grouping instead of creating a separate group for each field.
Note: These snippets use example table and view names between the braces { }. You should use values appropriate to the specific report.
Changing the font colour with using a formula:
To change the font colour using a formula:
- Navigate to the Font tab, Format field.
- Type, for example:
If {@Amount} > 0 then
Black
Else
Red
Your font colour is changed accordingly.
Other useful Crystal Reports functions and formulas
Function | Description |
|---|---|
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. |
ToText({FileSemesters.FileYear},"#") | Convert number to text (remove all formatting e.g. 2,011.00 becomes 2011 |
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'). |
IIF(expression, truepart, falsepart) | Evaluates the expression (expressions must resolve to true or false). If:
|
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. |