Workshop Formula Editor

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:

  • The Fields area (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 (top center frame of the Formula Editor) includes the pre-built Crystal Reports functions and Custom functions that are available to be added to the currently edited formula.

  • The Operators area (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, CASE, AND/OR, and so on.

  • The Editing area (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, some book-marking options, an important formula syntax checking button (x+2), and the Crystal versus Basic Syntax drop-down box.

Figure 10.5. The Formula Editor within the Formula Workshop.

graphics/10fig05.jpg

graphics/bookpencil_icon.gif

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 syntax Crystal Syntax has no programming language affiliation, but is highly-evolved and easy to use for non-programmers. For the rest of this hour, the examples will be created using the more commonly used Crystal syntax.


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:

  1. 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.

  2. Select the Employees and Employee_Addresses tables to be used in the report.

  3. 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 10.6.

Figure 10.6. The Crystal Reports Design window with a sample report.

graphics/10fig06.jpg

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 Formulas

Arithmetic 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.

graphics/10fig07.jpg

graphics/bookpencil_icon.gif

With hundreds of formula functions and operators built into Crystal Reports and the new ability to expand that set with Custom functions, it's easy to become overwhelmed with all the available formula possibilities. One very helpful source for 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, a detailed description can be accessed on each of the hundreds of different Crystal Reports functions and operators. Figure 10.8 displays the Crystal Reports Help dialog box with an Aging function highlighted.

Figure 10.8. Crystal Reports functions Help.

graphics/10fig08.jpg


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:

  1. 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 10.9 displays the Crystal Reports Design window with the Field Explorer displayed.

    Figure 10.9. Sample report with Field Explorer displayed.

    graphics/10fig09.jpg

  2. 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 Name so call 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 Use Editor button that appears in the bottom of that screen. The Formula Expert will be explored later in this hour, but for now, the Formula Editor will be our primary focus. The familiar Formula Workshop (as you saw in Figures 10.2 and 10.5) will appear.

  3. Logically stepping through the CEO's request, the first database field we 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.

graphics/lightbulb_icon.gif

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, 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.

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

    Figure 10.10. Salary driven options formula creation example.

    graphics/10fig10.jpg

  2. 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 10.10, save the formula with the Save button and exit the Formula Workshop by clicking Close.

  3. Add the new formula into the report beside Salary and format it to display zero decimals and no currency symbol (hint right-click on the object and select the Format option). The preview tab of the CEO's report should resemble that shown in Figure 10.11.

    Figure 10.11. The interim version of the CEO's sample report.

    graphics/10fig11.jpg

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 Formulas

Date 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.

graphics/10fig12.jpg

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.

Table 10.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 pre-built 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 hour (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 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:

  1. 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, 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.

  1. 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. 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 curly brackets around the CurrentDate {Employee.Hire Date} section of the formula to ensure the proper order of calculation.

    graphics/bookpencil_icon.gif

    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.


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

    Figure 10.13. A Tenure Driven Options sample formula.

    graphics/10fig13.jpg

  3. 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 we have created and add them themselves. It seems like a good opportunity for another formula to sum up those two numbers.

  1. 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 will appear in the Fields frame, and you can use the addition operator.)

  2. Add this new field to the report, remove the hire date and salary fields, and re-format it to make your sample resemble that displayed in Figure 10.14.

    Figure 10.14. Sample Crystal Report with multiple formulas.

    graphics/10fig14.jpg

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 Formulas

String 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.

graphics/10fig15.jpg

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.

graphics/lightbulb_icon.gif

Although the + operator requires all of its arguments to be of the same string type when concatenating, the & operator will perform dynamic conversion to text on any non-string fields included in the operation a nice timesaving feature.


graphics/bookpencil_icon.gif

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 will 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.


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:

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

  2. Because we only want to present the first letter of the employee's first name, we will 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 function a string.

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

  4. Move the cursor in the editing area to the location of the second expected parameter for the Left function after the comma and type 1 (the number of characters to extract). This will create the entry Left ({Employee.First Name}, 1) in the Formula Editor and will instruct the Formula Engine to take the left most single character from the First Name field.

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

    Figure 10.16. String formula sample in the Formula Editor.

    graphics/10fig16.jpg

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

    Figure 10.17. Maple Leaf Bikes CEO report with String formula.

    graphics/10fig17.jpg

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 Conversion

Often, 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.

graphics/10fig18.jpg

A great deal of flexibility is provided with the numerous type conversion functions built into Crystal Reports.

Control 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 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.

graphics/10fig19.jpg

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:

  1. Create a new formula called High Option Review.

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

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

  4. Now when this condition is met for any employee, we 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.

  5. When that condition is not met, we 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 10.20.

    Figure 10.20. A sample formula with an If control structure.

    graphics/10fig20.jpg

    graphics/bookpencil_icon.gif

    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.


    graphics/lightbulb_icon.gif

    Comments can be added to formula statements in order 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 allows you to quickly add this syntax into formulas, as indicated with the double slash (//) icon.


  6. Add the new formula to the CEO's report so that it resembles the sample report displayed in Figure 10.21.

    Figure 10.21. Revised sample report with a High Option Review indicator.

    graphics/10fig21.jpg

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.

graphics/alarmclock_icon.gif

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 loop constructs that tally over 100,000 looping iterations, the formula will stop processing with an error.


Variables

Crystal 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:

  • Local Accessible only in the same formula that they are declared within.

  • 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 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.

graphics/lightbulb_icon.gif

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.


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."



Sams Teach Yourself Crystal Reports 9 in 24 Hours
Sams Teach Yourself Crystal Reports 9 in 24 Hours
ISBN: B003D7JUVW
EAN: N/A
Year: 2005
Pages: 230

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net