Using the Formula Workshop

You have already been introduced to a few different areas of Crystal Reports that leverage the formula capabilities of the product for enhanced functionality. As you create more advanced reports, you will come across more of these functional areas. Figure 10.1 displays the familiar Formula Editor within the new Formula Workshop interface.

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

Examples of places where the Formula Editor will be used when creating reports include

• 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 Hour 13, "Custom Formatting Techniques")

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

• Use of Report Variables (covered later in this hour)

Although the independently accessed Formula Editors for each of these reporting areas provide powerful capabilities, a great new productivity feature in Crystal Reports 9 is the ability to access almost all the formulas held in a report in a single interface called the Formula Workshop essentially 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.

Workshop Tree

Figure 10.2 highlights some of the new Formula Workshop features by expanding the Formula Workshop Tree found in the Formula Editor.

Figure 10.2. The Formula Workshop with expanded Formula Workshop Tree.

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

Report Functions

Report Functions are Custom functions created by Crystal Report Designers that are stored within the current report file. Although Custom functions are covered in detail in Hour 17, "Using Formulas and Custom Functions to Implement Complex Business Logic," it is important to note that they 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 right-clicking on any part of the Report Function section of the Formula Workshop or by Selecting Custom Function from the New menu drop-down list.

Repository Functions

Repository functions are Custom functions created by Crystal Report Designers and then stored centrally within the Crystal Reports Repository. The repository acts as a central library for these Custom functions among multiple other reusable objects. Although Repository functions are covered in detail in Hour 18, "Working with the Report Component Repository," note that they are accessed from the Formula Workshop along with the other types of formulas and functions. New Repository functions are uploaded by creating a Report function and subsequently adding it to the Repository through the Add Repository option accessed by right-clicking Custom Report Function.

Remember that when trying to add a custom function to the central repository for other report developers to use, it must first be created locally as a Report function and only then can it be added to the central repository. Custom functions cannot be directly added into the central repository. See Hours 17 and 18 for more details on Report and Repository functions.

Formula Fields

As has been mentioned already in previous hours, 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 Report treats derived formula fields in exactly the same manner as it does original database fields. The majority of this hour is dedicated to introducing the different methods of creating formulas through two interfaces the Formula Editor and the Formula Expert. Both of these will be discussed next, and Hour 17 will explore 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 in to 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. Hour 22, "Optimizing SQL Queries in Crystal Reports," provides a good introduction to SQL.

Selection Formulas

As discussed in Hour 7, "Filtering, Sorting, and Summarizing Data," Selection formulas come in two varieties in Crystal Reports Group 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 soon and in extended detail with respect to Selection Formulas in Hour 15.

Formatting Formulas

Formatting formulas provide flexibility in the presentation of a Crystal Report's report sections and all of 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 10.3) and the Report Section Expert (see Figure 10.4) accessed by the x+2 icon can be set and be set conditionally through these Formatting Formulas. Advanced Formatting is covered in Hour 13 but it should be noted that this is accessed and set through the Formula Workshop's Formula Editor.

Figure 10.3. The Format Editor dialog.

Figure 10.4. The Section Expert.