Exploring the Formula Workshop

In Crystal Reports, you utilize the Formula Workshop to create formula fields. The Formula Workshop acts as a central location from where you may add, edit, or delete any formulas within your report.

Before we access the Formula Workshop, let's create a sample report to utilize within this chapter. Create a blank report, selecting the Employee and Orders tables. Allow the Smart Linking to link these tables, or refer to the ER Model diagram in Appendix A.

Once you're at the Report Design area, add the following fields to the Details section:

• Last Name (from the Employee table)

• First Name (from the Employee table)

• Order Date (from the Orders table)

• Order Amount (from the Orders table)

Create a group using the Last Name field. Use the Format Editor to change the Order Date field to adjust any sections as necessary. Once you've got this report created, select Report, Formula Workshop from the menu bar. The Formula Workshop displays, as shown in Figure 10-1.

Figure 10-1: The Formula Workshop

The Workshop Tree is located on the left side of the Formula Workshop. The Workshop Tree contains a number of folders, each representing the different types of formulas that you can create in Crystal Reports.

Tip

To undock the Workshop Tree, click and hold your mouse button somewhere within the Workshop Tree's gray border. You'll find that you can drag and drop the Workshop Tree anywhere within your desktop. To dock the Workshop Tree again, drag it back to the left side of the Formula Workshop.

If you find that the Workshop Tree is not docking, right-click on the Workshop Tree title bar. In the Options menu that displays, make sure the Allow Docking option is checked.

The following sections summarize the different types of formulas that you can create within Crystal Reports. Whenever you create a formula, it is stored in the appropriate folder in the Formula Workshop for easy reference.

The Report Custom Functions Folder

Custom functions are fully functional formulas that you find yourself using again and again within your reports. For example, they might be procedures you create to evaluate, make calculations on, or transform data. Rather than creating these kinds of formulas every time within a report, you can make them custom functions so they are available for all your future report endeavors.

Custom functions are contained within the Crystal Reports' Report Component Repository. The Report Component Repository acts as a library where you may store report objects for use in multiple reports and by multiple report designers.

The Formula Fields Folder

This folder contains formulas that perform some sort of operation to the data that results in a new value. For example, a report formula might combine two text strings into one or perform a financial calculation on two numeric database fields to return some numeric result.

The SQL Expression Fields Folder

This folder contains formulas created exclusively with SQL rather than the Crystal Reports formula syntax. Often, users who are familiar with the SQL language prefer to write report queries and formulas through SQL statements rather than use Crystal Reports' "out of the box" functionality.

A full explanation of SQL is beyond the scope of this book. However, check out Chapter 15 for information about using SQL within your reports.

The Selection Formulas Folder

This folder contains formulas that limit (filter) the amount of data returned from the database. You created some selection formulas (with the Select Expert) in Chapter 7. In the next chapter, we look at expanding the functionality of selection formulas through the use of parameter fields.

The Formatting Formulas Folder

These formulas (better known as conditional formatting formulas) change the appearance of a report object (for example, changing the color of text or a database field or combining two database fields into one).