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.
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.
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”