Crystal Reports Formulae Samples
In this blog post we will highlight various types of Crystal Reports formulae. Each example suggests a formula name, the purpose of the formula and the actual formula you need to use. Examples are based on the XTREME sample database available from the SAP Crystal Reports wesbsite. Further Crystal Reports formulae training is available at Eident Training centres in Glasgow and Edinburgh or onsite at your office.
Basic Calculations
The following example shows a calculation working with two database fields.
Formula Name:  @Total Cost 
Formula Purpose:  To calculate the basic sales value based on the unit cost of an item multiplied by the number of items ordered. 
Table Name  Orders Detail 
Field Name  Unit Price & Quantity 
Actual Formula:  {Orders Detail.Unit Price} * {Orders Detail.Quantity} 
Formula Name:  @Net Price + VAT 
Formula Purpose:  Used to include a VAT amount with the total cost of each item ordered. Based on a formula you have already created. 
Field Name  @Total Cost 
Actual Formula:  {@Total Cost} * 1.175 
String Manipulation
Used to extract a specific letter or string of letters from any database text field.
Formula Name:  @First Initial 
Formula Purpose:  To extract the first initial letter only from a field containing a persons full first name, ie: with the name “Peter” only the “P” would be extracted. Ideal for addressing on letters, forms or labels. 
Table Name  Customer 
Field Name  Contact First Name 
Actual Formula:  {Customer.Contact First Name} [1] 
Formula Name:  @Contact Name 
Formula Purpose:  To extract the first initial letter only from a field containing a persons full first name as in the example above, and join the result to another field such as a surname. The concept used in Concatenation. 
Table Name  Customer 
Field Name  Contact First Name & Contact Last Name 
Actual Formula:  {Customer.Contact First Name} [1] + “. “ + {Customer.Contact Last Name}
NB: This formula can be created on a single line or multiple lines as shown. 
Basic Date Calculations
Frequently used to calculate a period of time for instance a number of days that has passed since an event has occurred.
Formula Name:  @Processing Days 
Formula Purpose:  To calculate the number of days it took to process an order from the Order Date to the Shipment Date. 
Table Name  Orders 
Field Names  Ship Date & Orders Date 
Actual Formula:  {Orders.Ship Date}{Orders.Order Date} 
Boolean formulas
A Boolean can only provide with a True/False response. The method used is to test a condition and gives you either response as appropriate.
You can also format the field to give a Yes/No or Y/N response if preferred. Right click over the formula when it has been placed in your report and select Format Field. The Boolean tab will be displayed automatically, click on the drop down list box and select Yes/No.
Formula Name:  @Boolean 
Formula Purpose:  To display a True/False or Yes/No reponse by testing a value. For example to test the result of the above Processing Days formula to confirm if the Customers Goods have been despatched or not.
The result can then be formatted to display a Yes/No or similar response. 
Table Name  Customer 
Field/Formula Name  @Processing Days 
Actual Formula:  {@Processing Days} > 0 
IfThenElse Formulas
Used to test a field and give a response depending on the result of the field. These formulas are extremely powerful but can be used in a relatively simple way as shown in the example below.
Formula Name:  @Sales Comment 
Formula Purpose:  To display a statement against any Last Year’s Sales if the value sold was over 40000. 
Table Name  Customer 
Field Name  Last Year’s Sales 
Actual Formula:  If{Customer.Last Year’s Sales}>40000 then “Excellent” 
Editing a Formula – Example 1
Formula Name:  @Sales Comment 
Formula Purpose:  To display a statement against any Last Year’s Sales if the value sold was over 40000, then a different statement if the value is over 30000 and a final statement for all values below 30000. 
Table Name  Customer 
Field Name  Last Year’s Sales 
Actual Formula:  If{Customer.Last Year’s Sales}>40000 then “Excellent”
Else If{Customer.Last Year’s Sales}>30000 then “OK” Else “Urgent Meeting Required” 
Editing a Formula – Example 2
Formula Name:  @Sales Comment 
Formula Purpose:  To display a statement against any Last Year’s Sales if the value sold was over 40000, then a different statement if the value is over 30000 and a finally the actual sales figure if it’s less than 30000. 
Table Name  Customer 
Field Name  Last Year’s Sales 
Actual Formula:  If{Customer.Last Year’s Sales}>40000 then “Excellent”
Else If{Customer.Last Year’s Sales}>30000 then “OK” Else ToText({Customer.Last Year’s Sales}) 
Editing a Formula – Example 3
Formula Name:  @Sales Comment 
Formula Purpose:  To display a statement against any Last Year’s Sales if the value sold was over 40000, then a different statement if the value is over 30000 and a finally the actual sales figure if it’s less than 30000.
The following example will also allow you to condense the sales value by dividing the Last Year’s Sales field by 1000. It will also remove the decimal places rounding the whole number up or down as necessary and add the letter K to the result – ie: £20K

Table Name  Customer 
Field Name  Last Year’s Sales 
Actual Formula:  If{Customer.Last Year’s Sales}>40000 then “Excellent”
Else If{Customer.Last Year’s Sales}>30000 then “OK” Else ToText({Customer.Last Year’s Sales}/1000,0) + “K” 