Writing Formulas with the Formula Workshop
Formulas are referenced in different ways depending upon
which part of the report is using them. But they all share the same editor. The
Formula Workshop is the command center for entering and editing formulas. Getting
a solid understanding of how to use the Formula Workshop is essential before
getting into the details of writing formulas. The Formula Workshop is shown in
Figure 5-1. The Formula Workshop window.
The Formula Workshop consists of five separate windows.
The left window along the side is the Workshop Tree. It shows every formula
being used in the report and is categorized by the type of formula. The three
windows along the top present the library of fields and functions used for
writing formulas. Within these three windows is every available database field,
built-in function, and operator. The bottom window is the Definition area where
you write and edit formulas. It is like a fancy Notepad editor where you enter
text and it highlights different keywords.
Using the Toolbars
Before getting into the details of what each window does
and how to use it, let's first explore the toolbars along the top and the
buttons on them. Each group of buttons has a different way of assisting you with
These buttons make minor modifications to the windows
within the Formula Workshop. The first button toggles the categories and items
to be either sorted or unsorted. Sorting the categories makes it easier to find
an item in the tree. Clicking the button a second time returns them to their
The last three buttons hide one of the three other windows.
The three windows that they hide are the Report Fields window, Functions
window, and the Report Operators window. Clicking the button a second time
displays the window again.
There are some other buttons that work with the windows,
but they are scattered in different locations around the toolbars.
This button toggles the display of the Workshop Tree
window along the left side. It is located above the other three buttons.
The Show Formatting Formulas button toggles the display of
formatted report objects within the Workshop Tree window. This makes it easy to
see which report objects have conditional formatting. For example, when you
initially open the Formula Workshop window and look in the Workshop Tree
window, under the Formatting Formulas node is a list of every report section.
Clicking on each section expands that node and shows every report object in
that section. The objects that have conditional formatting can be expanded one
more level to see those formulas.
Of course, not every report object has conditional
formatting formulas associated with it. When you click the Show Formatting button,
the report objects that don't have any conditional formatting formulas are
hidden. This makes it easy for you to look at the report sections and quickly
see how much conditional formatting has been used and look at the details. If
this is a report you haven't worked on in a while (or a report that someone
else created), then this button helps you quickly understand what is happening
behind the scenes.
The Use Expert/Editor button opens the Formula Expert. When
clicking this button, all the windows except the Workshop Tree are removed and
the Formula Expert appears in their place. It helps you create formulas based
on a custom function. The Formula Expert discussed in more detail in Chapter 6.
Creating New Formulas
Figure 5-2. Menu items for the New Formula button.
The New button creates a new formula. When you click on it,
it shows you a list of the different types of formulas and you click on the type
you want to create. If you've been working on an existing formula and haven't saved
it yet, you are asked if you want to save it before continuing.
These five buttons assist you with writing formulas. The
first button checks the syntax of the formula. If it finds an error in the
formula, it prompts you with a message box telling you if there is a syntax
error or not. You can check the syntax after you are finished writing the
formula or you can do it after each line. Checking the syntax after each line lets
you find a problem and correct it before going forward. This is good when you
are just learning how to write formulas and you aren't confident of what you
are doing yet. It's also useful if you are writing a complex formula and you
want to double check that you didn't make a typo.
The next two buttons are the Undo and Redo buttons. You are
probably familiar with these from other programs such as Microsoft Word or
Excel. The Undo button removes the last change you made. The Redo button puts
that change back if you decide that you shouldn't have clicked the Undo button.
It's like an Undo-The-Undo button. J
The last two buttons help you find information. The
magnifying glass browses data of a field. This is the same as the Browse Data
button we saw with the report experts. It is only available when a field in the
Report Fields window is selected.
The last button is the Find button. As you might expect, it
finds the specified text within the formula and highlights it. But that is just
one part of its functionality. What is a really nice feature is that it not
only searches your formula, but it searches the text within any of the windows.
Look at Figure 5-3 to see what the Find dialog box looks like.
Figure 5-3. The Find dialog box lets you do more than
just search within the formula.
Notice in the center of the dialog box that it has radio buttons
that specify where to search for the text. It finds text in Fields, Functions,
Operators, and Edit Text (the Definition area). If you've ever tried to find a
function name in the Functions tree, it can be confusing to figure out which
node the function is listed under. The Find dialog box finds it for you. And if
you only know part of the name then it does partial text finds as well.
The Find feature also replaces existing text with new
text. But this is only enabled for the Edit Text option (you aren't about to
start renaming Crystal
functions!) If you want to rename a formula name that is referenced within
another formula, remember from before that you don't have to do this with
Crystal Reports. When you rename the formula within the Field Explorer window, Crystal
Reports automatically searches throughout the entire report and updates all
formula references to use the new name.