What are formulas?

In many cases, the data you want to appear on your report already exists in fields in database tables. To prepare an order list, for example, you simply need to place the appropriate fields on your report.

By placing these fields...

You get this kind of report.

Sometimes, however, you need to put data on your report that does not exist in any of the data fields. In such cases, you need to create a formula. For example, to calculate the number of days it takes to process each order, you need a formula that determines the number of days between the order date and the ship date.

Seagate Crystal Reports makes it easy for you to create such a formula.

Click the INSERT FIELDS button on the standard toolbar. The Insert Fields dialog box appears.

Click the Formula Tab to activate it.

3 Click the New button.

4 Type the name you want to identify the formula.

#When you click OK in the Formula Name dialog box, the Formula Editor appears. Use the Formula Editor to create, test, and modify your formula.

For more information on the Formula Editor, see How formulas are created - An introduction to the Formula Editor, Page 327.

$To build the processing time formula, enter the Ship Date field, the Subtraction operator, and the Order Date field.

7 Double-click the Ship Date field in the Fields list.

8 Double-click the subtraction operator in the Operators list.

9 Double-click the Order Date field from the Fields list.

The formula components are entered as you choose them.

NOTE: When you double-click an item in the Fields, Functions, or Operators list box, it is placed in the Formula text box complete

with the brackets, punctuation, and other syntax items the Formula Editor needs to process them correctly (see Formula

syntax, Page 334). If you enter your formula manually, using the

keyboard, you have to make certain you enter those syntax items yourself. It is generally safer and quicker to build a formula by choosing list box items.

NOTE: The list of values in the Browse Field Data dialog box represents only a subset of the available records.

When you are finished, click the Check button. The program checks the formula syntax.

If the syntax is correct, click the Accept button. The Formula Editor closes and you are returned to the Insert Fields dialog box.

Click the Insert button to place the formula field object in your report.

13 When the object frame appears, place it where you want the formula to appear in your report.

This is just one of many of ways you can use formulas to create powerful reports.

Some of the other typical uses for formulas are:

● creating calculated fields to add to your report,

{orders detail.UNIT PRICE}*.85

«Calculates a price discounted 15%.»

● formatting text on a report,

UpperCase ({customer.CUSTOMER NAME})

«Changes all the values in the Customer Name field to uppercase. See How to format text with formulas, Page 349.»

● pulling out a portion, or portions, of a text string, and

{customer.CUSTOMER NAME} [1]

«Extracts the first letter of the customer name. See How to group based on first letter of company name, Page 301, and search for Subscript in online Help.»

● pulling out a portion of a date.

Month ({orders.ORDER DATE})

«Determines what month an order was placed.»

These examples just scratch the surface. If you have a need for specialized data manipulation, chances are you can do it with a formula.

Related Topics

Advanced Formulas, Page 345

How formulas are created -

An introduction to the Formula Editor

Creating a formula in Seagate Crystal Reports is much like creating one in your favorite spreadsheet application. You can use:

● fields:

({customer.CUSTOMER LAST NAME}, {customer.LAST YEAR'S SALES}, etc.),

● numbers:

(1, 2, 3.1416),

● text:

(“Quantity”, “,”, “:”, ’your text’, etc.),

●operators:

(+, -, etc.),

●functions:

(TrimRight (x), Length (x), etc.),

●group field values:

(Average (fld, condFld), Sum (fld, condFld, “condition”), etc.), or

●other formulas:

(@GrossProfit, @QUOTA, etc.).

You combine these components into working formulas using the Formula Editor. The Formula Editor allows you to type the components into the formula directly, or, in the case of fields, functions, operators, group field values, and other formulas, to select them from lists of those items that are available.

The Formula Editor requires you to enter the various components according to a specific set of rules called syntax, such rules as:

●text enclosed in quotation marks,

●arguments enclosed in parentheses (where applicable),

●referenced formulas identified with a leading @ sign,

●and so on.

The Formula Editor checks the syntax and helps you debug (fix) problems before you enter the formula into the report.

Fields already used in the report.

Formulas used/defined in the report.

Parameter fields used or defined in the report.

All active database fields.

HOW TO INSERT FIELDS IN YOUR FORMULA

You can insert fields in your formulas in two ways:

1. by placing the I-beam cursor where you want the field to appear in the Formula text box of the Formula Editor, setting the insertion point and then double-clicking the desired field in the Fields list box, or

2.by placing the I-beam cursor where you want the field to appear in the Formula text box and then typing it in manually.

NOTE: Be sure to review the correct syntax for using fields in your formulas. If you do not include the file name, leave out the separating period, or fail to surround the field in braces, the program will generate a Formula Compiler Error message detailing your error. See Formula syntax, Page 334, and search for

Error Messages and Formula Compiler Warnings in Seagate Crystal Reports online Help.

Functions box The Functions box lists the dozens of functions that are included with Seagate Crystal Reports. These functions are prebuilt procedures that return values. They perform such calculations as average, sum, count, etc. All functions available are listed in this box with their arguments and arranged by their use.

Function name.

Arguments you must supply.

HOW TO INSERT FUNCTIONS IN YOUR FORMULA

You can insert functions in your formulas in the same manner as fields:

●via the Functions box, or

●by typing them in manually.

See HOW TO INSERT FIELDS IN YOUR FORMULA, Page 328.

NOTE: Be sure to review the correct syntax for using functions in your formula. If a function has required arguments, all arguments must also be entered. If any required arguments are not entered, the program will generate a Formula Compiler Error message detailing the error. See Formula syntax, Page 334, and search for

Error Messages and Formula Compiler Warnings in Seagate Crystal Reports online Help.