Using the Formula Workshop

You have already been introduced to the Record Selection and Group Selection functionality of Crystal Reports that each independently leverages the formula capabilities of the product for enhanced flexibility. As you create more advanced reports, you will come across more functional areas that will exploit the formula capabilities of Crystal Reports. Figure 4.1 displays the familiar Formula Editor within the new Formula Workshop interface.

Figure 4.1. The Formula Editor within the new Formula Workshop.

The Formula Editor can be used in the following functional areas of Crystal Report creation:

• Creation of derived fields (Formulas, SQL Expressions)

• Report Section formatting

• Report Object formatting

• Record Selection formulas

• Group Selection formulas

• Running Total conditions

• Formula-based hyperlinks (covered in Chapter 9, "Custom Formatting Techniques")

• Alert conditions (covered in Chapter 11, "Using Record Selections and Alerts for Interactive Reporting")

• Use of Report Variables (covered later in this chapter and in Chapter 12, "Using Subreports for Advanced Reports")

Although the independently accessed Formula Editors for each of these reporting areas provide powerful capabilities, a great new productivity feature introduced in Crystal Reports version 9 is the capability to access almost all the formulas held in a report in a single interface called the Formula Workshopessentially a one-stop shop for all formulas. At the time of writing, the only exceptions to the rule were Running Total and Alert Condition formulas.

The Formula Workshop consists of a toolbar, a tree that lists the types of formulas you can create or modify, and an area for defining the formula itself either through the Formula Editor or a Formula Expert.

Navigating the Formula Workshop with the Workshop Tree

Figure 4.2 shows some of the new Formula Workshop features you see by expanding the Formula Workshop Tree found in the Formula Editor.

Figure 4.2. The Formula Workshop with expanded Formula Workshop Tree and the Formula Expert displayed.

The Workshop Tree is a container for Report and Repository functions, Formula fields, SQL Expression fields, Selection formulas, and Formatting formulasall of which are explained in more detail in the following sections.

Report Custom Functions

Report Custom Functions are functions created by Crystal Report Designers that are stored within the current report file. It is important to note that custom functions are accessed from the Formula Workshop along with other types of formulas and functions. New Custom Report functions are created through the Formula Editor by accessing the right-click menu on any part of the Report Custom Function section of the Formula Workshop or by selecting Custom Function from the New menu drop-down list.

For more information on custom functions, p. 314.

Repository Custom Functions

Repository Custom Functions are functions created by Crystal Report Designers and then stored centrally within the BusinessObjects Enterprise (or Crystal Reports Server) Repository. The repository acts as a central library for these custom functions among multiple other reusable objects. Note that Repository functions are accessed from the Formula Workshop along with the other types of formulas and functions. You upload new Repository functions by creating a Report function and subsequently adding it to the Repository through the Add Repository option accessed by right-clicking any specific Custom Report Function.

For more detail on Repository functions, p. 390.

Caution

Although the Crystal Repository was introduced and made available in Crystal Reports version 9, it is now only available to Crystal Report designers who are licensed for BusinessObjects Enterprise (or Crystal Reports Server) version 10 or XI. When requesting any Repository-related function or activity, a logon prompt for BusinessObjects Enterprise (or Crystal Reports Server) is presented to the designer and must be successfully completed before the functionality is made available.

Tip

Remember that when you add a custom function to the Central Repository for other report developers to use, you must first create it locally as a Report Custom function and only then can it be added to the Central Repository. Custom functions cannot be directly added into the Central Repository. See Chapters 13 and 18 for more details on Report and Repository functions.

Formula Fields

As you learned in previous chapters, formula fields provide a means to add derived fields (that is, those not directly available in your database), such as a calculation into your Crystal Reports, as well as provide your business users (report consumers) with additional views of data. Once created, Crystal Reports treats derived formula fields in exactly the same manner as it does original database fields. The majority of this chapter is dedicated to introducing the different methods of creating formulas through two interfacesthe Formula Editor and the Formula Expert. Both of these are discussed next, and Chapter 13, "Using Formulas and Custom Functions," explores some advanced features of formula creation and use.

SQL Expression Fields

SQL Expressions provide a means to add derived fields (that is, those not directly available in your database), such as a calculation into your Crystal Reports, that are based exclusively on Structured Query Language (SQL) statements rather than standard Crystal formula syntax. As a reminder, SQL Expressions are used in cases where report-processing efficiency is critical.

Using SQL Expressions facilitates pushing data processing to the database server instead of the Crystal Reports Server, and this is usually most efficient. Like Formulas, SQL Expressions are created in the Formula Editor but provide only a subset of the functionality because of the dependency on the SQL supported by the report's attached data source. A downloadable chapter called "Using SQL Queries in Crystal Reports," provides a good introduction to SQL and is available from www.usingcrystal.com.

Selection Formulas

As discussed in Chapter 3, "Filtering, Sorting, and Summarizing Data," selection formulas come in two varieties in Crystal ReportsGroup and Record. A Record Selection formula provides a filtering mechanism on records to be included in the final report. Likewise, a Group Selection formula provides a filtering mechanism on the groups to be included in the final report. Each of these selection formulas can be accessed and edited through the Formula Workshop using the familiar Formula Editor component. The Formula Editor will be described in detail in the next major section and in extended detail with respect to selection formulas in Chapter 11.

Formatting Formulas

Formatting formulas provide flexibility in the presentation of a Crystal Report's report sections and all the report objects contained within report sections. Examples of object and section formatting options include Background Color, Suppression, ToolTip, Border Color/Style, Section Underlay, and so on. All the formatting capabilities available in the Format Editor dialog (see Figure 4.3) and the Report Section Expert (see Figure 4.4) that provide access to an x+2 icon can be setand be set conditionallythrough these Formatting Formulas.

Figure 4.3. The Format Editor dialog provides access to numerous formatting settings and additional access to the Formula Editor for conditional formatting.

Figure 4.4. The Section Expert provides access to numerous section formatting settings and additional access to the Formula Editor for conditional settings.

Coverage of the formatting functionality provided through these dialogs is covered in Chapter 6, "Fundamentals of Report Formatting," but you should note that this is accessed and set through the Formula Workshop's Formula Editor. When you select the New Formatting Formula option by either clicking the New button or right-clicking on a Section or Field element under the Formatting Functions tree, you can access all formatting functions that can be modified through a formula.