Using the Workshop Formula Editor

2017-11-03 09:05:06

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:

The Fields area (at the top-left frame of the Formula Editor) includes all the available report, formula, summarization, and database fields that can be added to the current formula.

The Functions area (at the top-center frame of the Formula Editor) includes the prebuilt Crystal Reports functions and custom functions that are available to be added to the currently edited formula.

The Operators area (at the top-right frame of the Formula Editor) includes a number of operators that can be used in the currently edited formula. Examples of operators include +, *, IF/THEN/ELSE, SELECT CASE, AND/OR, and so on.

The Editing area (the large bottom frame of the Formula Editor) is the free-form text-editing area where formulas are formed through either direct typing or double-clicking selections from the other three Formula Editor frames.

The toolbar area contains a number of Formula Editor options including toggles on the different frames, a new toggle on the Formula Editor or Expert, some bookmarking options, a formula syntax checking button (x+2), and, importantly, the Crystal versus Basic Syntax drop-down box.

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:

Create a new report based on the Xtreme Sample Database ODBC Connection using either the Standard Report Wizard or through the main Report Design menus.

Select the Employees and Employee_Addresses tables to be used in the report. They should be automatically smart-linked on their indexed (noted by the Red Icon in the linking dialog) Employee ID fields.

Add the Employee ID, Salary, and Hire Date fields into the detail section of the report.

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:

If the Field Explorer is not already open in your Crystal Reports Design window, open that now by either clicking on the Field Explorer icon or by toggling to the Field Explorer option under the View menu. Figure 4.9 displays the Crystal Reports Design window with the Field Explorer displayed.

Figure 4.9. Maple Leaf Bikes CEO report with Field Explorer displayed.

Create a new Formula by clicking on the Formula Fields field and either accessing the New option on the right-click menu or clicking the New button in the Field Explorer toolbar. You will be prompted for a Formula Namecall this formula Salary Driven Options and select the Use Editor button to create the formula. If you accidentally click the Use Expert button, have no fear; simply click the Formula Editor/Expert toggle button in the Formula Workshop toolbar. The Formula Expert is explored later in this chapter, but for now, the Formula Editor is your primary focus. The familiar Formula Workshop (as you saw in Figures 4.2 and 4.5) appears.

Logically stepping through the CEO's request, the first database field you need to access to determine the Salary Driven Component of stock option allocation is Salary, so find the Salary field in the Fields frame and double-click on it.

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.

To accomplish this task, you need to type in the numeric constants regardless, so type the following into the Formula Editor so that it resembles Figure 4.10: / 10000 * 100.

Figure 4.10. Salary-driven options formula creation example.

Perform error-checking on your report by clicking the x+2 icon. After you confirm that no errors are found and your formula is identical to that in Figure 4.10, save the formula with the Save button and exit the Formula Workshop by clicking Close.

Add the new formula into the report beside Salary and try to format it to display zero decimals and no currency symbol (hintright-click on the object and select the Format option or use the shortcut buttons from the Formatting toolbar). At this point, also remove the original Salary and Hire Date fields from the report by deleting them. Note that the Salary Driven Options field can exist without its underlying support fields (Salary) existing on the report. The Preview tab of the CEO's report should now resemble that shown in Figure 4.11.

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.

Table 4.1. Common Date Operators, Their Functions, and Examples
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.

Conversion functions are found under the Date and Time section in the Functions frame of the Formula Editor.

Range functions are found in the Date Ranges section of the same Functions frame and provide a number of built-in date ranges that can be automatically created in Crystal Reports and used in comparisons. Range examples include Aged61To90Days, Next30Days, or AllDatesFromTomorrow. These ranges can be used with the control structures introduced later in this chapter (for example, IF statements) to determine if dates fall within certain predefined ranges.

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:

Create a New Formula in the Field Explorer called Tenure Driven Options.

Because the CEO wants to provide 100 stock options for each year (365 days) of tenure, you 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 employee tenure measured in days 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.

To accomplish this, add the Current Date function (CurrentDate) to the formula by accessing it under the Date and Time section of the Functions frame in the Formula Editor. You could alternatively add this by typing Cu in the editor box, clicking on Ctrl+Spacebar, and selecting the CurrentDate function from the list. Add the - operator (found under the Arithmetic section in the Operators frame) after that, and then add the database field Hire Date to the formula by double-clicking on it. Finally, add the / 365 and * 100 formula pieces by typing them in and, more importantly, wrap two round brackets around the CurrentDateEmployee.Hire Date} section of the formulato ensure the proper order of calculation.

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.

Ensure that your formula resembles what is displayed in Figure 4.13 and save it before closing the Formula Workshop.

Figure 4.13. A Tenure-Driven Options sample formula highlighting some date formulas.

Place the new formula on the report beside the Salary Driven Options field and format it to have no decimal places and no currency symbol.

It has likely struck you that most CEOs would not appreciate having to take the two options numbers you have created and add them themselves. It seems like a good opportunity for another formula to sum up those two numbers.

Create a new formula called Total Options and make that formula be the sum of the two previously created formulas. (Hint: The previously created formulas appear in the Fields frame under the Report Fields Tree node, and you can use the addition operator.)

Add this new field to the report, remove the hire date and salary fields, and reformat it to make your sample resemble that displayed in Figure 4.14.

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.

Create a new formula in the sample report called Employee Name.

Because you only want to present the first letter of the employee's first name, you need to use the Left function under the Strings section of the Functions frame. Add this to your formula and note that the cursor is automatically placed in the expected location for the first parameter to this functiona string.

Without moving the cursor in the Editing area, find the First Name field of the Employee table and double-click it (you will likely need to expand the Xtreme Sample Database section because this field is not currently added to the report). This adds it as the first argument to the Left function.

Move the cursor in the Editing area to the location of the second expected parameter for the Left functionafter the commaand type 1 (the number of characters to extract). This creates the entry Left ({Employee.First Name}, 1) in the Formula Editor and instructs the Formula Engine to take the leftmost single character from the First Name field.

To concatenate this with the Last Name in a nice-looking manner, type + ". " + into the Editing area and then double-click on the Last Name field of the Employee table. Your new formula should resemble Figure 4.16.

Figure 4.16. String formula sample in the Formula Editor.

Replace the Employee ID field in the CEO's sample report with the new Employee Name formula you just created and re-arrange your report to resemble Figure 4.17.

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

CStr() or ToText(): These identical functions convert Numbers, Currency, Date, Time, and DateTime values to text strings.

CDbl() or ToNumber(): These identical functions convert Currency, text string, or Boolean values to a Number. Often used in combination with IsNumeric() or NumericText() to validate input arguments.

CDate(), CDateTime(), or CTime(): These functions convert their given arguments (numeric, string, and specific fixed formats) to a respective Date, DateTime, or Time value.

A couple of more interesting type conversion options include the following:

Roman(): This function converts a number ranging from 0 to 3999 into its Roman numeral equivalent (for example, Roman(2004) = MMIV).

ToWords(): This function converts a number or currency value to a string representation of that number (for example, ToWords($134.15, 2) = one hundred thirty four and 15/100). This is a nice function for facilitating the delivery of checks.

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:

Create a new formula called High Option Review.

Add the If/Then/Else control structure to the formula.

Add the condition that the Total Options Formula (the @Total Options field) is greater than 1750 between the If and Then components so that the beginning of the formula text is IF {@Total Options} > 1750 THEN.

Now when this condition is met for any employee, you need to highlight that record for the CEO's special review. To do this, add text similar to "** Review **" (with the double quotes surrounding the text) to the area after the Then part of the If statement construct.

When that condition is not met, you can simply print a space or dash. Do this by adding "-" (including the double quotes) after the Else part of the If statement so that your new formula resembles that shown in Figure 4.20.

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.

Add the new formula to the CEO's report so that it resembles the sample report shown in Figure 4.21.

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.

Table 4.2. Commonly Used Control Structures
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:

Local: Accessible only in the same formula within which they are declared.

Global: Accessible from all formulas in the main report, but not accessible from subreports.

Shared: Accessible from all formulas in both the main report and all subreports.

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.

Категории