What formulas can be used in CRW (Crystal Reports)?

Answer:

Formulas overview

Arithmetic functions

Summary functions

String functions

Date range functions

Special field functions

Evaluation time functions

Other functions

Operators

Functions

Arithmetic Functions

Abs(x)

Absolute value

Abs(1.50) = 1.50

Abs(-1.50) = 1.50

Remainder(numerator, denumerator)

Remainder of a division

Remainder(12,5) = 2

Round(x)

Rounding to a integer

Round(1.499) = 1

Round(1.5000) = 2

Round(x, n)

Rounding with decimals

Round(2345.23456,4) = 2345.2346

Round(2345.23456,3) = 2345.235

Truncate

Convert rational to an integer

Truncate(1.499) = 1

Truncate(1.999) = 1

Summary Functions

Average (field)

Averege(grand total)

Average({file.Amount}) = Calculates the average of all values in the {file.Amount} field.

Average (field, condField)

Average of a group selected by character, number, or dollar value field

Average({file.Orders},{file.Customer}) =

Calculates the average order in each group of customer orders. The orders are separated into groups whenever the value in the {file.Customer} field changes.

Average (field, condField, "condition")

condField is the name of the date or Boolean field that triggers the summary field to be calculated whenever a certain condition is met, and condition is the condition ("weekly", "monthly", "change to Yes", "next is No", etc.) that needs to be met.

Average({file.Order},{file.Date}, "monthly") =

Calculates the average order in each group of orders in the {file.Orders} field (the average order for each month). The orders are separated into groups whenever the value in the {file.Date} field changes to a new month.

Average ([array])

Average of an array of values

Average([25,50,75,100]) = 250/4 = 62.50

Count (field)

Counts the number of values in the field for the entire report. It creates, in effect, a "grand total" count.

if Count({file.Orders}) >= 100 then "Congratulations on meeting your quota!" else ""

Count([1, 2, 3, 4, 5]) = 5

DistinctCount (field)

Counts the number of distinct values in the field for the entire report. Duplicate values are not counted.

if DistinctCount({file.Customer}) >= 100 then "Congratulations on meeting your quota!" else ""

DistinctCount([1, 2, 3, 2, 5, 1, 4, 3, 2]) = 5

Maximum (field)

Maximum(field) returns the highest value in the field for the entire report. It is, in effect, a "grand total" maximum.

Maximum({file.Order},{file.Date}, "monthly") =

«Identifies the largest order in each group of orders in the {file.Orders} field (the largest order for each month). The orders are separated into groups whenever the value in the {file.Date} field changes to a new month.»

Maximum([25, 50, 75,100]) = 100

Minimum (field)

Where field is a database field or a formula.

Minimum({file.Amount}) = returns the lowest value in the {file.Amount field}.

Minimum([25, 50, 75, 100]) = 25

PopulationStdDev (fld)

PopulationStdDev(fld) calculates the population stand. Dev. of the number in the field for the entire report. It calculates the grand total population standard deviation for that field.

PopulationStdDev([2,4,6,8,10]) = 2.83

PopulationStdDev({file.Quantity})

Calculates the grand total population standard deviation for all values in the field Quantity». Crystal Reports uses N when calculating population standard deviation, N-1 when calculating standard deviation.

PopulationVariance (fld)

PopulationVariance(fld) calculates the population variance of the number or currency values in the field for the entire report. It calculates the grand total population variance for that field.

Calculates the population variation for each group of laboratory test results in the Results field. The test results are separated into groups whenever the value in the Date field changes to a new day.

Crystal Reports uses N when calculating population variance, N-1 when calculating variance.

StdDev (fld)

Standard Deviation(fld) calculates the standard deviation of the number or dollar values in the field for the entire report. It calculates the grand total standard deviation for that field.

Standard deviation([2,4,6,8,10]) = 3.16

Crystal Reports uses N-1 when calculating standard deviation, N when calculating population standard deviation.

Sum (fld)

Sum(field) totals the number or dollar values in the field for the entire report. It calculates the grand total for that field.

Sum({file.Amount}) =

calculates the sum of all values in the {file.Amount field}.

Sum([5, 5, 5, 5]) = 20

Variance (fld)

Variance(fld) calculates the variance of the number or currency values in the field for the entire report. It calculates the grand total variance for that field.

Variance({field.Quantity})

Calculates the grand total variance for all values in the field Quantity.

Crystal Reports uses N-1 when calculating variance, N when calculating population variance.

N.B. Alle summary functies kunnen gebruikt worden zoals met Array is aangegeven: in combinatie met een (fld), (fld, condFld), (fld, condFld, "condition") en ([array]). Bij alle functies kan fld een nummer, valuta of formule bevatten, tenzij anders aangegeven.

String Functions

Length(x)

x = text string, or a field containing a text string.

Length returns the number of characters in a text string that you enter into the formula, or in a text string stored as a value in a data field.

Length("Account") = 7

Length('Text_String') = 11

LowerCase(x)

UpperCase(x)

x = text value in a data field or a text string.

LowerCase prints the text string or text value in the data field in all lower case letters.

Where x is a string and n is an integer. Prints string x, n number of times.

If {file.Sales} << {file.Quota} then

ReplicateString("*",10) else ""

Prints the string "*" ten times as a flag.

ToNumber(x)

Where x is a number stored as a text string. ToNumber allows you to convert a number stored as text to a number on which you can perform arithmetic.

ToNumber("123.45") = 123.45

ToText

Where x is a number that you wish to convert to text. ToText converts a number to a text string

ToText(123.45) = "123.45"

ToText (number of places)

Where x is a number you want to convert to text. ToText(x, # places) converts a number to a text string and allows you to specify the number of decimal places in the number when it is written as text.

ToText(12345.6749,2) = "12345.67"

This function does not truncate the number written as text, but rounds it.

ToWords (x) of (x, number of places)

Where x is a number that you wish to convert to words (1 = one, 68 = sixty-eight, etc.

The function treats the number as a whole, rather than as a series of individual numbers. That is, 123 is treated as the number one hundred twenty-three rather than the individual digits one, two, and three.

Negative numbers begin with the word "negative."

ToWords(12345) = twelve thousand three hundred forty-five and xx/100

ToWords(-12345) = negative twelve thousand three hundred forty-five and xx/100

ToWords(12345.4999,0) = twelve thousand three hundred forty-five

This function does not truncate the number written as text, but rounds it.

TrimLeft(x)

TrimRight(x)

TrimLeft removes all spaces to the left of a string or data field (x).

TrimLeft(" _____Al/4520/B12") = "A1/4520/B12"

Date Range Functions

Date (YYYY, MM, DD)

Date returns a date from the year, month, and day numbers entered. The year must be four digits. The month must be a number from 1 to 12. The day must be a number from 1 to 31.

Date(1990,10,1) = Oct 01 90

Date(1986, 9,11) = Sep 11 86

If PageNumber = 1 then PrintDate

else Date(0,0,0)

Day(x)

Day extracts the day from a date and returns a number.

Day({file.Last invoice on}) = 10 where the last invoice date was the 10th day of the month.

DayOfWeek

Extracts the day component of a date, determines the day of the week the date falls on, and converts the day of the week to a number (1 to 7) where Sunday is the first day of the week.

(DayOfWeek(Date(1990,10,1) = 2 where October 1, 1990 is a Monday.

DTSToDate (DateTimeString)

Where DateTimeString is any Microsoft Access (version 1.0 or higher) Date/Time string field. Evaluates the field specified and returns only the date.

DTSToDate({ORDERS.OrderDate})returns only the date from a Date/Time string field. The Date/Time field from your Access table must be a string field. It must include both a date and time.

DTSToTimeString (DateTimeString)

Evaluates the field specified and returns only the time in military format.

DTSToTimeString({ORDERS.OrderDate})

DTSToSeconds (DateTimeString)

Same as above but returns time in seconds

DTSToSeconds({ORDERS.OrderDate})

Month

Month extracts the month component of a date and converts it to a number.

Month({file.Last invoice on}) = 10

Now

Today

Now prints the current time on a report. The time is taken from your computer's internal clock.

no arguments are required

Year(x)

Use this function any time you need to use a year, converted to a number, in calculations or comparisons.

Year({file.Last invoice on}) = 1989

Date Range Functions

Aged...Days

Aged as of today the number of days specified

Aged0To30Days

Aged as of today 30 days.

Aged31To60Days

Aged as of today 31 to 60 days.

Aged61To90Days

Aged as of today 61 to 90 days.

Over90Days

Aged as of today over 90 days.

AllDatesFromToday

From the first date entered to today.

AllDatesFromTomorrow

From today to any future date

AllDatesToYesterday

From the first date entered to yesterday.

Calendar1stHalf/2ndHalf

All included dates in the first or second half of the calendar year

Calendar1stHalf

All included dates in the first half of the calendar year.

Calendar2ndHalf

All included dates in the second half of the calendar year.

Calendar1stQtr...4thQtr

All included dates in the 1st to 4th quarters of the fiscal year

Calendar1stQtr (ook: ~2nd, 3rd & 4thQtr)

All included dates in the 1st quarter of the fiscal year.

Last4WeeksToSun

The four weeks previous to last Sunday

Last7Days

From seven days ago to today

LastFullMonth

From the first to last day of the previous month

LastFullWeek

From Sunday to Saturday of the last full week

LastYearMTD

All dates in the current month last year, up to the current date last year

LastYearYTD

All dates in the last year, up to the current date last year.

MonthToDate

From the first day of the month to today.

Next...Days

Dates in the period specified starting from today.

WeekToDateFromSun

From last Sunday to Today.

YearToDate

From the first day of the calendar year to today.

Special Fields Functions

Group number, Print Date, PageNumber en RecordNumber.

Evaluation Time Functions

BeforeReadingRecords

Specifies that the formula is to be evaluated before the database records are read.

N.B. If you try to include a database field or group in this formula, you get an error message.

BeforeReadingRecords;

ToNumber ("12345") Forces the formula (which contains no database fields or groups), to be evaluated at the time it is normally evaluate (before reading records).

WhileReadingRecords

Forces the program to evaluate the formula while it is reading database record data.

N.B. zie boven.

WhileReadingRecords;

ToNumber({items.Qty}) Forces the formula (which contains a database field) to be evaluated at its normal time (while reading records).

WhilePrintingRecords

Forces the program to evaluate the formula while it is printing database record data.

N.B. zie boven.

WhilePrintingRecords;

3* Sum ({detail.Qty1}, detail.Qty2})

Forces the formula (which contains a group) to be evaluated at its normal time (while printing records)

N.B. Formulas are normally evaluated at the following times:

If no database or group field is included in the formula, the formula is evaluated before the program reads database records.

If a database is included in the formula, the formula is evaluated while the program reads database records.

If a group field, page # field, subtotal, etc. is included in the formula, the formula is evaluated after database records are read and while the data from the records is being printed in the report.

Other Functions

IsNull(fld)

fld= elk veld

Evaluates the field specified in the current record and returns TRUE if the field contains a null value.

IsNull({file.AMT}) =

200.00 False

(null) True

StartsWithOperator(Symbol/Word)

{fieldname} startswith "abc"

«This operator tests to see if the contents of {fieldname} start with a character string that you specify "abc". If the contents of the field do start with "abc", then the formula returns the value True. If the field starts with anything else, the formula returns False.».

This operator tests to see if the contents of {fieldname} matches a pattern that you specify in a character string "c?n*". If the contents of the field do fit the pattern "c?n*", then the formula returns the value True. If the field starts with anything else, the formula returns False.

{customer.firstname} like "D?n"

TRUE

where {customer.firstname} = Dan

FALSE

where {customer.firstname} = Doug

You can use the wildcards

x like y

Next(fld)

fld is any field or formula field in the report. Next(fld) returns the value of the specified field for the next record.

Next({file.AMT}) =

200.00 0

null 100.00

100.00

NextIsNull(fld)

where fld is any field or formula field in the report. Evaluates the field specified in the next record and returns a TRUE if the field contains a null value.

NextIsNull({file.AMT}) =

200.00 True

(null) False

100.00 True*

* geen volgend record

NumericText(x)

Where x is a text field. NumericText tests to see if the content of a text field is a number. If the entire content of the field is a number - or if the characters extracted via the subscript operators are entirely a number - the expression returns a YES value. If any part of the content of the field - or of the characters extracted - are not a number, the expression returns the value NO.

NumericText({file.Reference}) = NO

where {file.Reference} = ABCDEFG"

NumericText({file.IDNUM}) = YES

where {file.IDNUM} = "12345"

Previous(fld)

Where fld is any field or formula field in the report.

Previous({file.AMT}) =

200.00 0

400.00 200.00

100.00

PreviousIsNull(fld)

Evaluates the field specified in the previous record and returns a TRUE if the field contains a null value.

PreviousIsNull({file.AMT}) =

200.00 True*

(null) False

100.00 True

* geen vorig record

NumberToCode39(number)

Converts numbers in number fields to the format needed to create bar codes using Azalea bar code fonts. It converts the numbers to the characters supported by the font and formats the resulting value with leading and trailing asterisks.

NumberToCode39(1234567) = *1234567*

NumberToCode39(1234567.89) = *1234568*

Only integers, others will get rounded

number is a field containing numbers.

StringToCode39(string)

zie boven. string is a string field containing numbers

StringToCode39(1234567_89) = *1234567_89*

Picture (string, picture)

Picture (string, picture) prints a string or values in a string field in a predetermined format.

Picture must be enclosed in quotes.

Picture ({phone.PHONE},"(xxx) xxx-xxxx" ) -> prints the values in the PHONE field in the format (805) 555-5555, (313) 555-2555, etc.

LooksLike (string, mask)

String is a string, string field, or string formula, and mask is a mask (characters and wildcards) against which you want the program to compare field values. It compares a string to a mask that contains one or more wild cards. The function returns True if the string matches the mask, False if it doesn't.

LooksLike ("Snyder","Sn?der") = TRUE

LooksLike ("Schneider","S?der") = FALSE

LooksLike ("Schneider", "S?????der") = TRUE

Can use wildcards.

Soundex (string)

String is one of two or more strings that sound alike. Soundex (string) evaluates string and returns a four character value that symbolizes the way string sounds.

This function can also be used to find customer names that have been misspelled.

If Soundex{file.LNAME} = Soundex ("Snider") then {file.LNAME}

else ""

runs a Soundex on each value in the LNAME field and prints only those values that have a Soundex value equal to the Soundex value of the name Snider. This formula returns the names Sneider, Schneider, Snyder, and Snider if they are all in the database. See help for more.

Equal (=), Not Equal (ą of <>), Less Than (<), Greater Than (>), Greater Than Or Equal (>=), Less Than Or Equal (<=)

String Operators

Concatenate

connect string x to string y (text strings)

"Bread" + " and " + "butter" = "Bread and butter"

Subscript (string)

Extract the y to z range of elements from string x. The subscript ranges are 1 origin, they start at 1 not 0*.

{file.Postal} [6] = "V"

{file.Postal} = T5A 9V2 (the space between A and 9 counts as an element)

In String

Test for the presence of string x in string y. syntax: x in y**.

"Elm" in {file.Address} = TRUE

{file.Address} is "1335 Elm Street".

* Don't confuse Subscript with In String. While Subscript tests a target string for the presence of an element and extracts the element (if found) from the string. InStringsimply tests the target string for the presence of the element.

** "In" can also be used to test for the presence of text in a text range, i.e., "V5B" in "V0A" to "V9Z". Such a range can be created using the Make Range operator.

Range Operators

Make Range

Create the range x to y.

Syntax: x to y. Ook bruikbaar voor data of text. Bij text krijg je een TRUE of FALSE ipv een range.

100.00 to 250.00

A range of consecutive numeric values from 100.00 to 250.00, including the end values.

In Range

Tests a range of values (y) to see if a value (x) falls within the range specified. Syntax: x in y.

10 in (5 to 15) = TRUE

Boolean Operators

And (&&;False and False = False, False and True = False)

Or ( ||; False or False = False, False or True = True)

Not(x) (!; not (True) = False not (False) = True)

Array Operators

Make Array ([ ])

build an array containing: x, y, z,...n

[100,200,300,400]

Subscript (array): x[y]

extract the y element of an array x

[100, 233, 466, 998] [3] = 466

In Array (in)

x in [y], is x in the array y.

{file.State} in ["CA", "HI", "AK"]

Is the value of the {file.State} field in the array of state abbreviations listed in the brackets?

Miscellaneous Operators

Assignment (:= bijv. x := n)

Assigns the value n to the variable x

Amount:= 0

Parentheses ( ) bijv. (x + y) * z

Perform the calculations inside the parentheses first.

(8 + 6) * 3 - 6 / 2 = 39

(8 + 6) * (3 - 6 / 2) = 0

If-Then-Else (if x then y else z)

If x is true then do y. If x is not true (else), do z. ^{1)}

If {file.Zip} <= "49999" then

"Blue Label" else "Ground"

Statement Separator -> ;

1+1;"abc"

1+1 and "abc" are two different formula statements in a multiple statement formula. The semicolon between the statements specifies where one statement ends and the next one begins. Without the semicolon the statements would be treated together as an individual statement

^{
1) The if part of the expression can include text, numbers, Boolean expressions (Cust#<"10000"), and formulas ({@Formula}), where @Formula is Boolean. The then and the else parts, however, must both be of the same type: (then text, else text; then number, else number). Mixing text and number actions will result in an error message.}

Disclaimer Despite the continued efforts of Exact to ensure that the information in this document is as complete and up-to-date as possible, Exact can not be held accountable for the correctness and/or completeness and/or specific applicability of the published and/or requested information in this document. Exact shall not be liable for any direct, indirect, incidental, special or consequential damages, lost profits or for business interruption arising out of the use of this document. The extraction and use of information from this document remains at all times completely within the user's own risk.