The Formula Expert

There are two buttons available at the bottom of the Formula Name dialog box: Use Expert and Use Editor. Clicking the Use Expert button will create the formula using the Formula Expert. If you choose the Use Editor button, you ll be able to create your formula in the Formula Editor, which is largely unchanged from previous Crystal Reports versions.

If, after naming your formula, you click the Use Expert button, the Formula Expert will appear, as shown in Figure 5-2. This expert is designed to help you create a formula without having to use the Crystal Reports formula language. While this sounds tempting to those who may, at least initially, be slightly put off by the Crystal formula language, the Formula Expert immediately introduces one substantial caveat: only formulas using a single custom function can be created in the expert. If you want to create a formula using regular Crystal Reports formula language functions or math operators, the Formula Expert won t provide appropriate capabilities. And even if you are using a custom function, the Formula Expert won t allow you to enclose it inside another custom function or use the custom function with any other Crystal Reports formula language syntax. In any situation where you can t use the Formula Expert, you ll need to use the Formula Editor to create your desired formula.

Figure 5-2: The Formula Expert

The first step in using the Format Expert is to determine the custom function you want to base your formula on. Choose this function in the Custom Function Supplying Logic list. You may choose a function from either the Report Custom Functions category (these are custom functions that are already part of your report) or the Repository Custom Functions category (these are custom functions that reside in your Crystal Enterprise repository). In either case, click the plus sign next to the desired category and navigate through the tree until you find the desired custom function you wish to use. Once you select that custom function, a description of the function will appear in the Summary area (if the description was added when the custom function was created). The data type that the function will return (string, number, and so forth) will appear in the Return Type area. And, any necessary parameters or arguments that the function requires to be supplied will be added to the Function Arguments area at the bottom of the Formula Expert.

Then, simply choose the database fields, other formula fields, or typed-in values that you want to supply as arguments to the custom function. You may do this by typing in a number, string, date, or other value directly in the Value area for the desired argument. Or if you click the down arrow to display a drop-down list, you ll see a list of fields you ve previously added to the report that match the data type of the argument (for example, if the argument requires a date-time value, only date-time fields that you ve previously added to the report will appear in the drop-down list). You may also choose drop-down list options to Enter Constant Value, which will display a separate dialog box where you may choose or type a value (which is just the same as typing the value in directly without clicking the down arrow). You may also select Choose Other Field from the drop-down list, which will display a separate dialog box listing all fields in all database tables that match the data type of the argument.

If you ve supplied several values to arguments in the Function Arguments list and wish to discard them all, click the Reset Values button above the Function Arguments list. All previously supplied arguments will be discarded, and you ll need to choose arguments again. If you want more general information on the custom function you ve chosen to base your formula on, click the More Info button to see a dialog box showing all the function s properties.

Once you ve supplied all the necessary parameters, click the Save and Close button in the Formula Workshop toolbar. If the formula you re creating in the Expert is based on a function from the repository, you ll be reminded that creating this formula will add the chosen repository custom function (and any other repository functions that it may be based on) to the report. This will be required to use this formula, so click Yes to acknowledge this message.

The formula will be created and added to the Field Explorer, where it may be dragged to your report just like any other field. If you later wish to edit this formula, either right-click the formula in the report s Design or Preview tab and choose Edit Formula from the pop-up menu, choose Field Explorer options to edit the existing formula, or display the Formula Workshop and choose and edit the formula from within it. The Formula Workshop containing the Formula Expert will reappear with the formula already in it. If you wish to see the formula displayed in the Formula Editor instead of the Formula Expert, click the Use Expert/Editor button in the Formula Workshop toolbar ”the formula will be displayed in the Formula Editor instead.

Note  

Creating custom functions that you may base Formula Expert formulas on is covered in more detail in Chapter 6.