Crystal Reports in the Real World— Custom Functions

Some examples of custom functions include handling divide-by-zero errors and handling multilanguage 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 an error. 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 0—and 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:

1.	Open the sample report Chap4Formulas.rpt. 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. [View full size image]
5.	Click OK to close the window and the function is now part of the report. Right-click on the function name and choose Add to Repository. If prompted, enter the logon information for your Crystal Enterprise system and the custom function will be added to the Crystal Enterprise Repository making it available to all the users who have access to the repository.
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. [View full size image]

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. Create a new parameter named Language (described in Chapter 5, “Implementing Parameters for Dynamic Reporting”) and 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. [View full size image]
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. [View full size image]