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.

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.

Figure 7-2. Formula buttons.

The buttons shown in Figure 7-2 are described here:

A blank sheet, the 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.