The Formula Editor, as shown in Figure 10.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 10.5. The Formula Editor within the Formula Workshop.
The available elements in each of the top three areas of the Formula Editor will vary depending on what type of formula you are creating. For example, when creating a Formatting formula, the Functions frame will present 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 (sound familiar?), 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 per $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 will walk through a solution for this problem:
At this point, the design frame (from the Design tab) for the report should resemble Figure 10.6. Figure 10.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 will be flushed out through the next few sections as different Formula functions are systematically introduced. Arithmetic FormulasArithmetic formulas are those derived from existing numeric fields (or fields converted into numbers type 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 and/or numeric-oriented functions. Figure 10.7 displays the Formula Editor re-sized to highlight some common Arithmetic functions and operators. Figure 10.7. The Formula Editor highlighting some Arithmetic functions and operators.
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 in to the Formula Editing frame. Using either method, a formula will begin to be constructed in the Formula Editing Area/Frame. An alternative means to creating formulas for experienced users is to directly type the formula in to the Formula Editing Area and periodically check the formula's syntax with the x+2 toolbar button, which provides error-checking functionality. Revisiting the Maple Leaf Bikes reporting scenario, the CEO has designated two criteria for stock option allocation to the Xtreme Sports employees Tenure 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 will contribute to 100 stock options. The following steps, continued from the last section, will move toward a reporting solution for the CEO and provide exposure to the Formula Creation process in the Formula Editor:
Because the CEO wants to provide 100 stock options for each $10,000 in existing salary, we will logically need to divide each employee's current salary by $10,000 and then multiply by 100. To do so, we 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.
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 will need to be taken care of with some date calculations. Date and Time FormulasDate and Time formulas are those derived from existing date or time fields (or fields converted into dates see later for type conversion information). 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 and/or date-oriented Functions. Figure 10.12 displays the Formula Editor re-sized to highlight some common date functions. Figure 10.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 in to the Formula Editing frame. Using either method, a formula will begin to be constructed in the Formula Editing Area/Frame. Some operators that are commonly used with dates include + and -. Those are displayed in Table 10.1 with some quick examples and their effect. These operators work equally well on time fields and date fields.
Common functions that are used with dates include the use of the pre-built 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 each 365 days of tenure will contribute to 100 stock options. The following steps will move toward a final reporting solution for the CEO and provide exposure to date focused formula creation in the Formula Editor:
Because the CEO wants to provide 100 stock options for each year (365 days) of tenure, we will logically need to determine each employee's tenure in days by finding the difference (with the - operator) between the current date (with a built-in Crystal Reports function) and the hire date (with a provided database field). This tenure in days measure will then need to be divided by 365 to find the tenure in years before being multiplied by 100 to determine the number of tenure-driven options.
It has likely struck you that most CEOs would not appreciate having to take the two options numbers we have created and add them themselves. It seems like a good opportunity for another formula to sum up those two numbers.
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 hours will introduce some additional capabilities provided in Crystal Reports and the Formula Editor that will increase the presentation quality of this report. String FormulasString formulas are created from existing string fields (or fields converted into strings see later for type conversion information). 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 10.15 displays the Formula Editor resized to highlight some common string functions. Figure 10.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.
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. The following instructions will use the string capabilities of the Formula Editor and enhance the report in the described manner:
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. Type ConversionOften, data is not accessible in the format that is required for a particular operation. The most 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 10.18 displays the Formula Editor with the Type Conversion section expanded. Figure 10.18. The Formula Editor with Type Conversion functions.A great deal of flexibility is provided with the numerous type conversion functions built into Crystal Reports. Control StructuresThe Formula Editor provides additional power in formula creation through a set of control structures made available in the Operators and Functions Frames. Figure 10.19 displays the involved sections of those respective frames that include the provided control structures. Figure 10.19. The Formula Editor with 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, lets assume that the CEO has provided a new requirement specifying that employees with a recommended stock allocation of greater than 1500 stock options need to be highlighted for his personal review. Of course, with Crystal Reports, there are multiple methods of providing this highlighting, but using the If/Then/Else control structure, the following steps can accomplish this:
The conditional logic inherent in the If/Then/Else and Select/Case statements provide clear flexibility in formula creation. Another valuable formula capability that programmers will 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.
VariablesCrystal Reports has included yet another programming construct variables in the Formula Editor to provide even further flexibility in formula creation. Variables provide a powerful means to store and retrieve information throughout the processing life of any report essentially 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 are accessible from the Operators frame in the Formula Editor. To use variables in your report formulas, they must be declared first and this applies to every formula that accesses any given variable not just the first processed formula.
Although a detailed discussion on variables is beyond the scope of this book, it is worth noting the power that they provide to maintain some persistent information outside the regular processing path of the report. A practical hands-on use of variables is explored in Hour 16, "Using Sub-Reports for Increased Flexibility." |