| Some examples of custom functions include handling divide-by-zero errors and handling multi-language text. Both of these examples are described in this section. A common reason for divide-by-zero errors is simply that a field might not be populated. If a given field has not been populated but it is used in the report, Crystal converts it to a default value. Unless modified, the default value for a numeric field that returns NULL is 0. This means that if there is a formula calculating percent of capacity (Current_Amount/Max_Amount) but the item is new and therefore no max amount has been set, then the Max_Amount field in the database is likely blank. When the preceding formula is applied to the database fields, then the result will be a divide by zero error and the report will fail. Current_Amount/Max_Amount would resolve to some real value divided by NULL, the NULL would be converted to the default value of 0, and the result would be some number divided by 0and a divide-by-0 error is the result. To avoid this, create a custom function to handle all division. The custom function simply checks for a denominator of 0 and handles it appropriately. First create the custom function follow these steps: 1. | Open the sample report Chap4Formulas.rpt or the report you have created through this chapter. From the Field Explorer, select Formula Fields and click New. Type in a name for the formula such as Source Formula and click Use Editor.
| 2. | When the Formula Workshop window opens, enter the following formula:
If {Employee.Supervisor ID} = 0 Then 0 Else {Employee.Employee ID}/{Employee.Supervisor ID};
Note Although it seems (and is) odd to build a formula using ID fields in a calculation, what is important is the field types. The fields are used to build the custom function based on their data types rather than actual content. The previous fields are abstracted to simply numeric fields named v1 and v2. | 3. | With the Formula Workshop still open, mouse over the New button near the top of the window and click the down arrow. From the list choose Custom Function, enter the name DivBy0, and click the User Extractor button.
| | | 4. | When the Extract Custom Function from Formula window opens, select the @Source Formula item from the list of formulas. Rename the arguments from v1 and v2 to Denominator and Numerator, respectively (see Figure 4.26).
Figure 4.26. Using the Custom Function Extractor, create a custom function from a formula. 
| 5. | Click OK to close the window and the function is now part of the report. If you are using BusinessObjects Enterprise (or Crystal Reports Server), you could now right-click on the function name and choose Add to Repository. When prompted, enter the logon information for your BusinessObjects Enterprise system and the custom function will be added to the Enterprise Repository making it available to all the users who have access to the repository and enabling all report writers to avoid fatal divide by zero errors in their reports.
| 6. | Click Save and Close (see Figure 4.27).
Figure 4.27. The custom function is now part of the repository making it available to users who have appropriate rights. 
|
Another example of a custom function might be how to handle some standard text options. For example, it might be useful to have a parameter drive the column header for a field. 1. | Keep the report open. From the Field Explorer, create a new parameter named Language with the potential input values of English, French, or Italian (described in Chapter 5, "Implementing Parameters for Dynamic Reporting"). Also add a new formula named Country Source (described previously). Add the following formula code:
If {?Language} = "English" Then "Country" Else If {?Language} = "French" Then "Pays" Else If {?Language} = "Italian" Then "Paese";
| 2. | Repeat steps 3, 4, 5, and 6 to create a new custom function called Country extracted from the Country Source formula.
| 3. | To use the new custom function, create a new formula named Country. From the list of functions, expand the Custom Functions and double-click the Country formula. The function takes one argument, pass in the Language parameter (see Figure 4.28).
Figure 4.28. The custom function accepting the Language parameter. This parameter determines what the function does. 
| | | 4. | Click Save and Close. If prompted, enter the text English. Place the new formula in the group header alongside the other column headers and add the country field below it on the detail line.
| | | 5. | Save the report as Chap4FormulaswithCustom.rpt. Refresh the report passing in "English," "French," and "Italian" to see the effect (see Figure 4.29).
Figure 4.29. The field header changes to display Country in appropriate languages. 
|
|
