Using the Formula Expert
The previous section made references to the Formula Expert dialog box, but unfortunately, you probably didn’t completely understand what it was because it had not been discussed yet. The Formula Expert is a tool that makes it easier for you to use the custom functions you’ve written. It lets you select a custom function and populate the arguments without writing any code. When you select the custom function to use, it lists every argument within the function. You fill in a value for each argument and save it. Behind the scenes, it is creating the call to the function for you. Effectively, you created a formula without writing a single line of code. It was all done by pointing and clicking the mouse.
At first, this sounds like a pretty good way to write formulas in your report. After all, not writing any code is much easier than learning Basic syntax or Crystal syntax. Unfortunately, there is a caveat. The Formula Expert is very limited in what it can do. It is so limited that you may find that you rarely use it. Here are the limitations of the Formula Expert:
- It is only compatible with custom formulas that you already created.
- It can only reference a single custom function.
- You can’t add any additional logic around the custom function.
What I find ironic about the Formula Expert is that it is designed to be easy for someone who doesn’t know how to write formulas. But if you are smart enough to write a custom function, then it goes without saying that don’t need the Formula Expert to help you call the custom function from a formula. The exception is someone who is working on a report created by someone else or if there are functions in the Business Objects Enterprise Repository that you need to use.
To use the Formula Expert, create a new formula as you normally would by clicking on the Formula Fields list and selecting New. After entering the formula name, the Formula Workshop dialog box appears. Along the top of the toolbar is the Use Expert button.
Figure 6-3. The Use Expert button.
When you click on this button the entire Formula Workshop dialog box changes. The three report tree windows along the top and the Definition area disappear. They are replaced by a Custom Function window, a Summary window, and the Function Arguments area.
Figure 6-4. The Formula Expert dialog box.
When you click on one of the custom functions, its summary is displayed to the right and its arguments are displayed at the bottom. Figure 6-4 shows what the dialog box looks like after selecting the custom function cdFormatCurrencyUsingScaling (found in the Crystal Reports sample report Formulas.rpt).
After selecting the custom function to use, the only step left is specifying the data to pass to the function’s arguments. The list of arguments is at the bottom of the dialog box in the Function Arguments area. This area lists the argument name, its data type, and a description. The last column is the Value column. This is where you tell Crystal Reports what data you want to pass to the argument. This can be an existing report field, a constant that you type in, or any field in the report’s data source. In Figure 6-4, there are four arguments.
One interesting thing about the Formula Expert is that if you click the Formula Expert button again, the dialog box switches back to the normal Formula Expert dialog box that you are familiar with. If you look in the Definition area you’ll see the actual formula you just created using the Formula Expert. For example, the formula that was created in Figure 6-4 is this:
You can see that the end result is a one line formula that calls the custom function cdFormatCurrencyUsingScaling and populates its four arguments. It’s really quite simple.
The Formula Expert button can be clicked on when you are editing any formula. It toggles the view back and forth between the normal Formula Workshop dialog box and the Formula Expert dialog box. But you need to be careful if you are editing a formula that wasn’t created with the Formula Expert. Switching to Formula Expert mode could erase all your changes. Since the Formula Expert has the limitation of only being able to work with a single custom function, if you are editing a formula that is more advanced, then the Formula Expert won’t know how to handle it. So it just erases all your work and you have to start from scratch. Luckily, before doing this, it gives you a warning message and lets you cancel it.
Overall, my feelings about the Formula Expert are similar to my feelings about the Highlighting Expert: it’s a nice crutch for the very beginning report writer, but you’ll quickly outgrow it.
