The Formula Editor, shown in Figure 4.5, is a common tool used across all the different types of formulas accessible through the Formula Workshop. The Formula Editor is composed of five distinct areas:
Figure 4.5. The Formula Editor provides a one-stop shop for formula development.
NOTE
Crystal Reports provides two different formula languages for use in creating formulas. Basic syntax is very similar to the Visual Basic programming structure and provides a natural fit for report designers with a Visual Basic programming background. The other more commonly used syntaxCrystal syntaxhas no programming language affiliation, but is highly-evolved and easy to use for nonprogrammers. For the rest of this chapter, the examples are created using the more commonly used Crystal syntax.
The available elements in each of the top three areas of the Formula Editor vary depending on what type of formula you are creating. For example, when you create a Formatting formula, the Functions frame presents a Formatting section not available while editing or creating other types of formulas. Another familiar example is the limited set of fields, functions, and operators presented when creating SQL Expressions. This is, of course, dependent on the supported SQL for the current report's data source.
To facilitate your understanding of the Formula Editor, the following hypothetical business problem provides a hands-on experience with creating formulas within reports. The CEO of Maple Leaf Bikes is planning an initial public offering (IPO) of his stock to the marketplace.
Having recently acquired another company called Xtreme Cycles, he wants to fairly share the success of the overall company with these new employees. As such, he wants to allocate stock options to them based on tenure with Xtreme Cycles (a metric of loyalty) and their current salary (a metric of expected contribution). Therefore, the CEO has determined that a fair allocation would be 100 shares for each year of tenure and 100 shares for each $10,000 in salary, and he wants a report outlining these allocations so that he can present this proposal at the next board of directors meeting. The following steps demonstrate a solution for this problem:
At this point, the design frame (from the Design tab) for the report should resemble Figure 4.6.
Figure 4.6. The Crystal Reports Design window with a sample report.
The basic building blocks to the requested report have now been added to the sample report, but there is clearly work to be done to capture the CEO's intent. This report is flushed out through the next few sections as different formula functions are systematically introduced.
Arithmetic Formulas
Arithmetic formulas are those derived from existing numeric fields (or fields converted into numberstype conversion information is discussed later in this chapter). These formulas can be simple multiplication or addition operations, or they can be as complex as standard deviations, sums, or correlations. Arithmetic formulas are created within the Formula Editor by selecting any combination of numeric fields, numeric operators, or numeric-oriented functions. Figure 4.7 displays the Formula Editor resized to highlight some common arithmetic functions and operators.
Figure 4.7. The Formula Editor highlighting some arithmetic functions and operators.
With hundreds of formula functions and operators built into Crystal Reports and the new capability to expand that set with custom functions, it's easy to become overwhelmed with all the available formula possibilities. One very helpful source of information on the many built-in formulas in Crystal Reports is the provided help files accessed through the F1 key. By clicking on the Index tab of the Crystal Reports Help Screen and searching on functions or operators, you can access a detailed description of each of the hundreds of different Crystal Reports functions and operators. Figure 4.8 displays the Crystal Reports Help dialog with an Aging function highlighted.
Figure 4.8. Crystal Reports functions Helpa great reference for understanding the syntax of formula functions.
To create an Arithmetic formula (as any other kind of formula) within the Formula Editor, either double-click on the appropriate elements from each of the Fields, Functions, and Operators frames or select them by single-clicking and dragging and dropping them into the Formula Editing frame. Using either method, a formula begins to be constructed in the Formula Editing Area/Frame. Alternatively, experienced users can create formulas by typing the formula directly into the Formula Editing Area and periodically checking the formula's syntax with the x+2 toolbar button, which provides error-checking functionality.
TIP
For users who prefer to work in the Formula Editor and type in their formulas by hand, Crystal Reports provides an Auto-Complete capability accessed by using the Ctrl+Spacebar key combination. A list of formula functions that could complete the most recently typed characters is made available for instant selection.
Revisiting the Maple Leaf Bikes reporting scenario, the CEO has designated two criteria for stock option allocation to the Xtreme Sports employeesTenure and Salary. The Salary component is based on a derivation from a numeric field (salary) and lends itself to the creation of an Arithmetic formula based on the requirements that each $10,000 of salary contributes to 100 stock options. The following steps, continued from the last section, move toward a reporting solution for the CEO and provide exposure to the Formula Creation process in the Formula Editor:
Figure 4.9. Maple Leaf Bikes CEO report with Field Explorer displayed.
TIP
More than just providing access to those fields already selected for viewing in the report, the Formula Editor Fields frame provides access to all available database fields for those tables selected as report data sources. Additionally, existing formulas, sums, running totals, and so on can be accessed here, which can be included in other formulas.
Because the CEO wants to provide 100 stock options for each $10,000 in existing salary, you logically need to divide each employee's current salary by $10,000 and then multiply by 100. To do so, you could either access the Arithmetic operators (/ for division and * for multiplication) in the Operators Frame and double-click on those or simply type them in.Figure 4.10. Salary-driven options formula creation example.
Figure 4.11. The interim version of the Maple Leaf Bikes CEO sample report.
The current version of the report takes the content of the report to about half complete. The tenure-driven component of the CEO's request needs to be taken care of with some date calculations.
Date and Time Formulas
Date and Time formulas are those derived from existing date or time fields (or fields converted into dates). These types of formulas can be as simple as extracting a month name from a date field or as complex as determining shipping times in business days (difference between two dates not including weekends and holidays). Date and Time formulas are created within the Formula Editor by selecting any combination of date and time fields, Date operators, or date-oriented functions. Figure 4.12 displays the Formula Editor resized to highlight some common date functions.
Figure 4.12. The Formula Editor highlighting some Date and Time functions.
To create a Date/Time formula (as with Arithmetic formulas) within the Formula Editor, either double-click on the appropriate elements from each of the fields, functions, and operators frames or select them with a single-click and drag and drop them into the Formula Editing frame. Using either method, a formula begins to be constructed in the Formula Editing Area/Frame.
Some operators that are commonly used with dates include + and -. Those are displayed in Table 4.1 with some quick examples and their effect. These operators work equally well on time fields and date fields.
Common Date Operator or Function |
Formula Usage Example |
Effect |
---|---|---|
+ operator |
{Employee.Hire Date}+ 365 |
Returns the one year anniversary date of the given employee in a date format. |
- operator |
{Orders.Ship Date} {Orders.Order Date} |
Returns a numeric field representing the days taken to ship after receiving an order. |
- operator |
{Orders.Warranty Expiration Date} - 365 |
Returns a date representing the purchase date of the given item. |
Common functions that are used with dates include the use of the prebuilt date ranges and date type conversion formulas in Crystal Reports.
Revisiting the Maple Leaf Bikes reporting scenario, the Tenure component of option allocation still needs to be created in the report. It is based on a derivation from two date fields (hire date and the current date) and lends itself to the creation of a date formula based on the requirements that every 365 days of tenure will contribute to 100 stock options.
The following steps move toward a final reporting solution for the CEO and provide exposure to date-focused formula creation in the Formula Editor:
NOTE
The Crystal Reports Formula Editor respects the standard mathematical order of operations. In order this would be brackets, exponents, division and multiplication, and, finally, addition and subtraction.
Figure 4.13. A Tenure-Driven Options sample formula highlighting some date formulas.
Figure 4.14. Maple Leaf Bikes CEO report with options formulas.
The CEO of Maple Leaf Bikes should be quite happy with the turnaround time on this report. Having created the results so quickly, it might be a good move in career management to spend a little time on the presentation and readability of this report. The next sections and chapters introduce some additional capabilities provided in Crystal Reports and the Formula Editor that increase the presentation quality of this report.
String Formulas
String formulas are created from existing string fields (or fields converted into stringstype conversion is covered later in the chapter in the section "Using Type Conversion in Formulas"). These formulas can be as simple as concatenating two string fields or as complex as extracting some specific piece of information from a string field. String formulas are created within the Formula Editor by selecting any combination of string fields, string operators, or string-oriented functions. Figure 4.15 displays the Formula Editor resized to highlight some common string functions.
Figure 4.15. The Formula Editor with string-oriented functions expanded.
The most commonly created string-based formulas involve the concatenation of multiple existing fields from a data source. This is accomplished through the Formula Editor with either the formal Concatenate function from within the Strings section of the Operators frame or by using the much easier + and & concatenate operators. These last two operators enable the dynamic linking of one or more string fields into one large string field.
TIP
Although the + operator requires all of its arguments to be of the same string type when concatenating, the & operator performs dynamic conversion to text on any non-string fields included in the operationa nice timesaving feature.
Revisiting the Maple Leaf Bikes reporting scenario and focusing on increasing the readability of the report, Employee ID can be replaced with Employee First Initial and Last Name. To use the string capabilities of the Formula Editor and enhance the report, follow these steps:
NOTE
When creating a string formula that is meant to join two existing strings (for example, First Name and Last Name, or Address 1 and Address 2), the concatenation features of Crystal Reports dynamically resize the resultant formula to exclude any redundant spaces between the end of the first joined field and the beginning of the next. This is an important presentation feature that prevents the requirement to trim all fields before joining them together.
Figure 4.16. String formula sample in the Formula Editor.
Figure 4.17. Maple Leaf Bikes CEO report with String formula.
Having covered the primary data types used in strings, it is useful for operating in the real world to know how to move between those data types. The next section discusses data type conversion.
TIP
Comments can be added to formula statements to better document the formula. To insert comments, use the double forward slash (//) at the beginning of a line of code to comment out the entire line. Thus, any text on this commented line would not be processed as part of the formula. There is also a toolbar command within the Formula Editor that enables you to add this syntax into formulas quickly, as indicated with the double slash (//) icon. If you're using the Basic Syntax, the apostrophe (or rem) commands can be used for commenting.
Using Type Conversion in Formulas
Often, data is not accessible in the format that is required for a particular operation. A common example is when numeric fields are stored in a database as string fields and they are required in an Arithmetic formula. For any number of additional reasons, it often happens that data needs to be converted to and from different data types. The Formula Editor provides numerous built-in functions that facilitate this conversion process. These functions are accessible from the Type Conversion section under the Functions frame of the Formula Editor. Figure 4.18 displays the Formula Editor with the Type Conversion section expanded.
Figure 4.18. The Formula Editor provides you with many different Type Conversion functions.
A great deal of flexibility is provided with the numerous type conversion functions built into Crystal Reports and these should enable all required conversions. Additionally, Crystal Reports provides some automatic conversions in the following cases: Number to Currency, Date to DateTime, and basic type to a Range Value of the same underlying basic type. Some of the most commonly used Type Conversion functions are
A couple of more interesting type conversion options include the following:
NOTE
Barcode conversion functions are also available through a third party, Azalea, at: http://www.azalea.com/CrystalReports/index.html. These enable you to convert numbers to standard barcodes that can be embedded on your Crystal Reports.
Control StructuresConditional and Looping Structures
The Formula Editor provides additional power in formula creation through a set of control structures made available in the Operators and Functions Frames. Figure 4.19 displays the involved sections of those respective frames that include the provided control structures.
Figure 4.19. The Formula Editor provides several Control Structure functions and operators.
One of the most useful control structures is the If/Then/Else construct. This structure enables the inclusion of conditional logic in Crystal Reports formulas. The If/Then/Else works particularly well when a condition leads to either one of two settings. Although this construct can handle multiple potential settings through nested If statements, creating this type of complicated formula can be avoided with the Select Case operator that allows for multiple settings and multiple potential results.
Revisiting the Maple Leaf Bikes example, assume that the CEO has provided a new requirement specifying that employees with a recommended stock allocation of greater than 1750 stock options need to be highlighted for his personal review. Of course, with Crystal Reports, there are multiple methods of providing this highlighting; to use the If/Then/Else control structure, follow these steps:
Figure 4.20. A sample formula with an If control structure.
NOTE
Carriage returns (via the Enter key) can be inserted into the construction area of the formula, such as between lines and logical breaking points, to make formulas more readable. If you're using the Basic Syntax, you can extend single code lines over multiple lines for readability by using the underscore character (_) preceded by a space.
Figure 4.21. The revised sample report includes a High Option Review indicator.
The conditional logic inherent in the If/Then/Else and Select/Case statements provides clear flexibility in formula creation. Another valuable formula capability that programmers appreciate immediately is the looping functionality. The Formula Editor provides three different looping constructs (For/Step/Do, Do/While, and While/Do), and each of these enable the evaluation of formula logic multiple times for each evaluation of the formula. Table 4.2 describes the most common types of control structures and their usage.
Control Structure |
Description |
Usage |
---|---|---|
If/Then/Else and IIF() |
Conditional structures that select an execution path based on the evaluated conditions. |
This construct is best used when evaluating conditions with a minimal set of potential execution options. |
Select Case |
Conditional structure that selects an execution path based on the evaluation conditions. |
This construct is best used in place of the if/then/else construct when evaluating conditions with multiple potential execution paths. |
Switch |
Another conditional structure that selects a value from a set of expression/value pairs where the expression evaluates to true. |
This is especially effective when creating report selection filters because it allows for the pushing of the results down to the database for faster report executionthis is not possible with the other conditional constructs. It is also useful for compact conditional formula creation. |
For/Step/Do Loops |
For loops enable you to evaluate a sequence of statements a fixed numbers of times. An Exit statement can end this looping prematurely. |
This construct is best used when you know the number of times that the expressions need to be evaluated in advance or the loops are dependent on a variable in the report. For I = X to Y Step Z Do (statements) |
Do/While Loops |
Do/While loops execute until the While condition is no longer met. They always execute at least once. The Exit While statement can end this looping prematurely. |
While loops can be used to execute a fixed block of statements an indefinite number of times. Do statements While condition |
While/Do Loops |
While/Do loops execute until the While condition is no longer met. It is possible that not a single iteration takes place if the condition is immediately false. The Exit While statement can end this looping prematurely. |
While loop can be used to execute a fixed block of statements an indefinite amount of time. While condition Do (statements) |
CAUTION
The Crystal Reports engine has a built-in safety mechanism that displays an error message and stops processing any formula if it includes more than 100,000 loop iterations. This is important to consider when including any of the loop constructs in a formula. It is also important to note that this built-in governor works on a per formula basis and not per loop. This means that if any one formula contains any number of loops that tally more than 100,000 looping iterations, the formula stops processing with an error. Another control structure function called Option Loop can be used for limiting iterations to a number different than 100,000.
Variables
Crystal Reports has included yet another programming construct, variables, in the Formula Editor to provide even further flexibility in formula creation. Variables give you a powerful means to store and retrieve information throughout the processing life of any reportessentially providing a temporary storage space for valuable information. Examples of information that might be useful to store and retrieve later are previous detail section information, previous group section information, or a one-time calculation that needs to be incorporated into many subsequent report formulas.
Several different types of variables can be declared (for example, String, Number, Date, Time, Boolean, and so on) and three different scopes for each of these variables are as follows:
Both the Variable Declaration and Scope operators listings are accessible from the Operators frame in the Formula Editor. To use variables in your report formulas, they must be declared firstand this applies to every formula that accesses any given variablenot just the first processed formula.
TIP
Another important function to remember when using multiple variables in multiple formulas with calculation dependencies is the EvaluateAfter() function. This formula function can force certain formulas (and their variable logic) to be processed after another formula (and its variable logic). This can be very useful when the order of formula calculation is important because of variable and formula dependencies. A good discussion of when things are evaluated in Crystal Report's multi-pass engine is provided in Chapter 12.
It is worth noting that variables can provide significant power in report creation in their capability to maintain persistent information outside the regular processing path of the report. A practical hands-on use of variables is explored in Chapter 12.