Peter Norton's Guide to Visual Basic 6
(Publisher: Macmillan Computer Publishing)
Author(s): Peter Norton
ISBN: 0672310546
Publication Date: 09/09/98

Adding Custom Formulas to a Report

Crystal Reports uses formulas and functions to do the kind of processing and data manipulation that is required for advanced database reporting. Choose Edit, Formula and use the Edit Formula dialog box to create the calculated fields in the report (see Figure 14.7).

Figure 14.7.  Both simple and complex formulas are defined using the Edit Formula dialog box.

The Edit Formula dialog box enables you to work with both formulas and functions. A formula is a set of instructions that calculate the information you can’t directly receive from the database. If a database record has two fields, Unit Price and Quantity Sold, but you need the total sales price, you would have to multiply the two fields to calculate the total sales price. This process is accomplished by using a formula that you place in the report.

This type of formula is simple because it uses the standard arithmetic operators. However, not all the calculations you need are simple formulas. At times you might require complex calculations or manipulations of the database fields. If you want to display the average monthly sales for the previous year, rounded to the nearest unit, you need a mathematical formula. The functions required to perform these activities involve a fair amount of data manipulation. Whereas some of this can be done using only Crystal Reports operators, many types of manipulations can’t be done without the use of functions.

Functions are built-in procedures or subroutines that are used to evaluate, calculate, or transform data from the database. The Edit Formula dialog box is divided into four subwindows that are used to create the calculation (refer Figure 14.7). The Fields window lists all the database fields that are available to you, based on the tables you have previously selected. The Functions window lists all the functions you can use to create a calculated field. The Operators window lists all the operators you can use. The largest window is the Formula Text window, where you build the actual calculation. You can use the different windows to select items, or if you know the correct syntax, type directly into the Formula Text window.

You can combine fields, functions, operators, and other calculations to create complex calculated fields. This enables you to create complex calculations in steps (small calculated fields) and then combine them to form the finished calculation.

Selecting the Required Records

When you select a field for the report, every row or query in the table is printed. However, in many cases you need only specific rows of data from the database. For example, you might want only New York customer data, or only invoices that fall within a particular range of dates. Crystal Reports includes four options on the Report menu used to select the data in your report; the options are listed in Table 14.2.

Even though you will be able to create professional-looking reports after completing this chapter, there are many more features included in Crystal Reports than can be covered. In the next section, you will see how to create a report by using the Crystal Reports program and then learn how to access the report from a Visual Basic application.

Creating a Report

As with anything else in the program design process, reports require both your time and analysis before you actually create them. For each report that you are creating, you should answer the following questions:

•  What is the purpose of the report?

•  What information should be included in it?

•  Does it require selection capabilities?

•  What is the layout of the report?

If you have the answers to these questions, your reports will perform well. Before starting Crystal Reports, you should sketch out what the report will look like and list the fields needed for the report. After you have listed the required fields, sketch the layout of the report. After you are satisfied with both the field list and the layout, you can take the next step, which is to start Crystal Reports. You are then placed in the main window (refer to Figure 14.1).