<!wp_title:7. 1 Introducing Formulas
<!wp_category:Crystal Reports.NET 2003>
<!wp_category: Chapter 7 - Using the Formula Editor
Formulas enable you to customize report output by analyzing the data in the report as it is being printed. There are two circumstances when you need to use formulas. The first is performing calculations based on the raw data. For example, when printing the monthly sales for a product you can calculate the percent of total sales. Storing this in the database isn’t practical because it is more efficient to calculate that value on an as needed basis rather than waste space storing the number in the database. The second circumstance that formulas are needed is dynamically modifying the formatting of fields or sections on the report. For example, you can simulate a “green bar” report by using a formula that alternates the background color of every other row. The flexibility and power of formulas make them one of the most useful features of Crystal Reports.
This chapter is the first of three that show you how to use formulas to make reports vibrant. It shows you when and how to use the Formula Editor. The next chapter is a syntax primer for both Basic syntax and Crystal syntax. These are the two languages that Crystal Reports uses. Chapter 9 is a reference for the common functions that are built into Crystal Reports.
Throughout this chapter, the examples are written using Basic syntax. Although this language isn’t covered in this chapter, it is very much like VB .NET and you shouldn’t have any problem understanding the examples here. In fact, the two languages are so similar that you might be tempted to skip over the next two chapters if you already know how to program in VB .NET. I don’t recommend this because Basic syntax has been designed to make reporting easier, and it adds a lot of new functionality not found in VB .NET. Learning about this new functionality can show you how to simplify some reporting specific tasks that would be more difficult that in VB .NET.
<!wp_title:7. 2 The Formula Editor>
<!wp_category:Crystal Reports.NET 2003>
<!wp_category: Chapter 7 - Using the Formula Editor
Writing Formulas with the Formula Editor
The Formula Editor shown in Figure 7-1 is where you create and edit formulas. It consists of four separate windows. The three windows along the top of the dialog box present the entire set of tools to write a formula. Within these three windows is every available field, syntax construct, and built-in formula. The bottom window is the formula window where you write and edit the formula.
<!wp_pic:
Figure 7-1. The Formula Editor window.
The top three windows assist you with writing formulas. Rather than having to use other tools to find out the names of the database fields and function names, they are listed in one of the windows at the top. When you double-click any of the items, that item appears in the code window at the current cursor position. You can also drag and drop the item into the code window. Effectively, you could write most of you formulas without doing any typing. Just double click the appropriate functions and insert the report fields and let the formula be built for you. Personally, as a programmer I don’t find this very practical. Scrolling through a hierarchy of syntax trees isn’t nearly as efficient as just typing it in. However, it is very useful to have these trees available when you can’t remember something because they are almost like having a mini-help file available. If there is a built-in function that you haven’t used in a while, you can browse through the tree structure to look for it. You can also look at it just to find out all the parameters a function requires.
In the top right corner of this dialog box is the drop-down list for whether you want to use Crystal syntax or Basic syntax. The individual windows are discussed in more detail in the Chapter 5, but the following are brief descriptions.
The leftmost window is the Field Tree. It shows all the fields on the report. This consists of formulas, group fields, and data fields. Below that it shows every field in the current data source (whether they are in the report or not). You can use any report field in your formula as well as any database field. The database field doesn’t need to be displayed on the report for it to be used in a formula.
The Function Tree is the middle window. It shows all the functions available. At first, this tree is a nice crutch to lean on as you learn the Basic syntax language. If you are a VB.NET programmer, you will find Basic syntax to be so similar that you will quickly learn the language and not rely on the Function Tree.
The Operator Tree shows the different operators grouped by category. Some of these categories are Arithmetic, Boolean, Comparisons, etc. It is similar to the Function Tree in that it is as a nice crutch when you are new to formulas, but you will quickly outgrow it.
The Formula window is where you write the formulas. The font is color-coded so that reserved words use a blue font. Comments are in green. Variables and value constants are in black.
As you write formulas, you need to save and check the syntax of the formula. The left most portion of the toolbar has buttons that provide this functionality. Figure 7-2 shows that portion of the toolbar.
<!wp_pic:
Figure 7-2. Formula buttons.
The buttons shown in Figure 7-2 are described here:
A blank sheet, the f first button, creates a new formula. It first saves the current one and checks its syntax before creating a new formula.
A disk, the second button, saves the formula and checks the syntax. You can continue working on the existing formula.
The third button, a disk with an “x”, checks the syntax, saves the formula and closes the Formula Editor. It returns you back to the report designer. You will probably use this one most of the time.
The fourth button, the formula icon with a checkmark, checks the syntax of the formula. It gives you a message box telling you whether the formula is okay and then lets you go back to working on the formula. Use this to verify the syntax with a function you aren’t familiar with.
The dropdown list shows all the available formulas. Use this when you are done working on the current formula and you want to edit another formula. This can only be used with existing formulas, not creating new ones.
Whenever you save a formula, the syntax is always verified. This insures that if there is something wrong with the formula, it will be corrected right away. When a syntax error is found, a message box appears informing you of the problem. Unfortunately, the error messages that appear are usually not very helpful. You frequently have to decipher this yourself. If you find that there is a syntax error in your formula, you are not required to fix it right away. You can save it as is, and then come back later to fix the errors. Just don’t forget to fix it or else your report won’t run properly.
<!wp_title:7. 3 Using Formulas for Calculations>
<!wp_category:Crystal Reports.NET 2003>
<!wp_category: Chapter 7 - Using the Formula Editor
Using Formulas for Calculations
A report gets the majority of its data from a data source. This could be a table in a database, an XML data feed or a proprietary data source. Data usually consists of raw data that doesn’t have extraneous information that can be derived by other means (e.g. calculations). It is more efficient to perform calculations on an as-needed basis than to save the results within the database. A formula can be calculated and displayed directly on the report or it can be used by other formulas.
Crystal Reports lets you add and edit formulas via the Field Explorer window in the report designer. Within the Field Explorer window, right click on any of the formula fields and select Edit or New to open the Formula Editor. You can also right click on the tree node Formula Fields. The Field Explorer window is shown in Figure 7-3.
<!wp_pic:
Figure 7-3. The field explorer window.
Once you’ve added a formula to the Field Explorer, you can display it on a report by dragging and dropping it to the report. You can tell the difference between database fields versus formulas because the formula fields are prefixed with a @. If you want to edit a formula field on the report, right click on it within the report layout and select Edit.
<!wp_title:7. 4 Dynamic Formatting>
<!wp_category:Crystal Reports.NET 2003>
<!wp_category: Chapter 7 - Using the Formula Editor
Dynamic Formatting with Formulas
Reports by their very nature are static. Although the printed data changes and the running totals are different every time, the report format stays the same. For example, if the first field in a column has a font of Arial and is black, every field in that column is also going to have a font of Arial and be black. After all, if every field in the same column had a different font, it would be very hard to read and people would question the abilities of the report designer. But wouldn’t it be nice to use visual cues to highlight important data? For example, you could change the color of an inventory quantity to red when it is below the minimum. The reader immediately knows that the item needs to be reordered. There might even be a special note to the side of the report stating whom to notify. In this circumstance, making a report dynamic increases its usefulness to the reader without adding clutter.
Crystal Reports gives you the ability to use formulas that dynamically modify the visual properties of fields and sections on a report. For example, you can use a formula that returns either True or False with the Suppress property of a section to either show or hide a section. You could also use a formula that returns a string and this can be used to add a special message at the end of each row.
<!wp_title:7. 5 Adding Formulas>
<!wp_category:Crystal Reports.NET 2003>
<!wp_category: Chapter 7 - Using the Formula Editor
Adding Formulas
When modifying a property in the designer, the changes you make to that property stay the same as the report runs. Most properties, but not all, can have a formula attached to them so that their value can be modified based upon other fields in the report. Clicking on the formula button next to a property lets you add a formula to do this. The formula button is shown in Figure 7-4. It has a blue “X-2” and there is a horizontal pencil underneath it. Once you add a formula to a property the “X-2” turns red and the pencil is at an angle. This is shown in Figure 7-5. Only properties with a formula button next to them can use a formula to make the values dynamic.
<!wp_pic:
Figure 7-4. A button with no formula associated with it.
<!wp_pic:
Figure 7-5. A button with a formula associated with it.
When assigning a formula to a property, you have to determine the data type that the property uses. This can be anything from Boolean, string, number or Crystal pre-defined constants. The formula must return the proper data type to the property. If the formula returns the wrong data type, the Formula Editor returns an error when you try to save it.
As an example of using the proper data type, Figure 7-6 shows the Section Editor dialog box. All the properties displayed here use checkboxes. These properties are either on or off. Formulas that are associated with these properties have to return True or False. As expected, returning True is same as a checked box, and returning False is the same as an unchecked box.
<!wp_pic:
Figure 7-6. The section expert window.
As a more varied example, Figure 7-7 shows the Border tab of the Format Editor dialog box. This dialog box uses checkboxes, line styles and colors.
<!wp_pic:
Figure 7-7. The Format Editor's border tab.
The values for the line style and color are predefined constants within Basic syntax. It can have values such as DashedLine, NoLine, etc. The color property can have values such as Aqua, Yellow, etc.
When you open the Formula Editor, the predefined constants for the current property are listed in the Function Tree. This list is dynamic and won’t show predefined constants that don’t apply to the current property. For example, if you are modifying a line style property, the Function Tree will show the different line styles, but won’t list any colors. This is illustrated in Figure 7-8. If you are modifying a color property, then it will show the available colors, but no line styles. This is illustrated in Figure 7-9.
<!wp_pic:
Figure 7-8. The Function Tree for line styles.
<!wp_pic:
Figure 7-9. The Function Tree for colors.
<!wp_title:7. 6 Printing Checkboxes>
<!wp_category:Crystal Reports.NET 2003>
<!wp_category: Chapter 7 - Using the Formula Editor
Printing Checkboxes
There are many ways to display a Boolean constant. You can display the words Yes/No or True/False. Right-click on the field and select Format. The dialog box displays a drop-down box that lets you select how you want the data to be displayed (Yes/No, True/False, etc.).
You can also display Boolean values as a checkbox, but this requires a little more creativity. Crystal Reports doesn’t have a built-in method for displaying checkboxes. You have to use the control characters in the Wingdings font. There are a variety of interesting characters in the Wingdings font. But five of them can be use for displaying checkboxes. Figure 7-10 shows some of the Wingdings characters available.
<!wp_pic:
Figure 7-10. Wingdings control characters.
Find the character you want to display and click on it. At the bottom you will see the Character Code value that you need to print. Write down this number. The character that you may not find right away is the empty checkbox. If you scroll up you’ll see that it is code 168.
To display a checkbox on the report, create a new formula field and insert the following formula.
If {Table.Field} = True Then
'Display checked box
Formula = Chr(254)
Else
'Display empty box
Formula = Chr(168)
End If
This formula tests a field to see if it is True. If so, it returns the control character 254 (a checked box). If it is false, it returns the control character 168 (an empty box). Save the formula and close the Formula Expert dialog box.
Drag this formula from the Field Explorer onto the report. Finally, change the font to be Wingdings by right-clicking on the field and selecting Format. Click on the Font tab and use the dropdown box to select the Wingdings font. Save the report and run it to see the checkbox displayed.
<!wp_title:7. 7 DefaultAttribute and CurrentFieldValue>
<!wp_category:Crystal Reports.NET 2003>
<!wp_category: Chapter 7 - Using the Formula Editor
Using the Default Attribute and Current Field Value
The value assigned to a property in design mode is called the property’s default attribute. When assigning a formula to that property, the default value is overridden by what is in the formula. There are many times when a formula is only used to specify what happens in a unique circumstance (e.g. an inventory item being out of stock) and you don’t want the formula to override the default value every time. The rest of the time you want the default value to be left unchanged. There are two ways of doing this: not specifying a value in the formula or using the keyword DefaultAttribute.
When you don’t specify a value, you are letting Crystal Reports use what was specified in the Format Editor. For example, you may want a field to be red if its value is less than the minimum quantity, otherwise use the attribute specified in the Format Editor. You can use a formula like the following:
If {Inventory.OnHand} < {InventoryItems.MinimumQty} Then
Formula = crRed
End If
If the condition is true, then the color becomes red. If it is false, then the color is left unchanged.
Although this is acceptable, it isn’t perfectly clear what the color will be if the condition isn’t true. As a second alternative you can use an Else statement and specify the result to be DefaultAttribute. By doing this you are telling someone reading your code that the color will either be crRed or the attribute that is specified on the Format Editor. The new code would look like the following:
If {Inventory.OnHand} < {InventoryItems.MinimumQty} Then
Formula = crRed
Else
Formula = DefaultAttribute
End If
Formulas can be made generic so that they can be used on different fields. By replacing the field name with the keyword CurrentFieldValue, the formula can be called in various places on the report. This keyword returns the current value of the field that is being formatted. The CurrentFieldValue is seen in Chapter 11 which discusses Cross-Tab reports.
<!wp_title:7. 8 The Highlighting Expert>
<!wp_category:Crystal Reports.NET 2003>