Using the Formula Workshop
You have already been introduced to the Record Selection and
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:
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
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
For more detail on Repository functions, p. 390 .
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-
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.
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
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.
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 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