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 where 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.
Writing Formulas in 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.
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 your 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 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 Crystal syntax or 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 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.
Figure 7-2. Formula buttons.
The buttons shown in Figure 7-2 are described here:
The first button, a disk with an “x”, checks the syntax, saves the formula and closes the Formula Editor. It returns you to the report designer. You will probably use this one most of the time.
A disk, the second button, saves the formula and checks the syntax. You can continue working on the existing formula.
A blank sheet, the third button, creates a new formula. It first saves the current one and checks its syntax before creating a new formula.
The first button on the second row, 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.
Whenever you save a formula, the syntax is always verified. This ensures 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.
