Creating Your Own Custom Functions | Crystal Reports 10: The Complete Reference

If you install Crystal Enterprise 10, its repository ships with a starter set of custom functions. However, you ll probably soon discover a use for your own. Creating a custom function is very similar to creating a Crystal Reports formula ”most of the typical built-in functions and operators otherwise available in regular formulas are available for your use in a custom function (Chapter 5 covers formula creation in more detail).

There are two ways to create a new custom function: base it on an existing formula, or create it from scratch.

Extracting Custom Functions from Existing Formulas

If you have an existing formula in a report that contains the core set of logic that you wish to use for your custom function, you may base your custom function on it by extracting the custom function from the formula. Do this by performing these steps:

Launch the Formula Workshop by clicking the Formula Workshop button in the Expert Tools toolbar, or choosing Report Formula Workshop from the pull-down menus .
In the Formula Workshop tree, right-click the Report Custom Functions category and choose New from the pop-up menu. You may also just click the Report Custom Functions category to select it and click the New button in the Formula Workshop toolbar, or click the down arrow next to the New toolbar button and choose Custom Function from the drop-down list.
Type in a name for your custom function. Choose a name within the naming limitations discussed later in this chapter.
Click the Use Extractor button to base your custom function on an existing report formula. The Extract Custom Function from Formula dialog box will appear, as shown in Figure 6-2.

Figure 6-2: Creating a custom function based on a formula
Make any desired changes to arguments that are substituted for database fields in the original formula. You may also add descriptive text to the function definition that will appear when you use the function in the Formula Expert (discussed in Chapter 5).
If you wish to change the original formula to use the function, check the Modify Formula to Use New Custom Function check box.
Click the Enter More Info button to supply optional items, such as default argument values and other descriptive text.
Click OK to save the new custom function.

Custom Function Naming Limitations

You are more restricted in choosing names for your custom functions than you are for formulas. Because custom functions appear in the Formula Editor's function tree alongside all the built-in functions, there are several limitations:

Custom function names must begin with a letter ”you can't start a function name with a number. Also, you can include only letters , numbers , and the underscore character in your custom function name ”other special characters (such as the % or # signs) cannot be included.
Custom function names cannot contain spaces ”you can use only underscores to separate a multiword custom function name. However, you can use mixed upper- and lowercase letters to help delineate multiword custom function names.
You cannot use a name that is already used by a Crystal Reports built-in function. For example, you'll receive an error message if you try to create a custom function named ToText or Left.
You may notice that Crystal-supplied custom function names include a two- character cd prefix. Although adding your own prefix is certainly not required, it may be a handy way of identifying a certain group of custom functions. Prefixing custom function names may also help avoid naming conflicts with other custom functions contained in the repository, as well as conflicts with built-in function names.

Custom Function Arguments

One of the first adjustments you ll need to make when creating custom functions, as opposed to formulas, is handling function arguments. An argument is a parameter that you create within your function to accept a value from the formula that calls it. It then uses this value (possibly along with other arguments that are also supplied) to perform some calculation or logic and return a result to the calling formula.

If you think of Crystal Reports built-in functions, most require that you supply arguments when you use them in formulas. For example, if you use the ToText function to convert a number, date, or other non-string data type to a string, you must supply at least one argument: the non-string value to be converted. And the Left function, which returns a certain number of characters from the left side of a larger string, requires you to supply two arguments: the string to retrieve a subset of characters from, and the number of characters to retrieve. In cases of built-in functions, arguments you supply must conform to specific data types. The single-argument ToText example requires a string argument to be supplied. The Left function requires a string argument and a number argument.

When you create your own custom functions (by either extracting logic from an existing formula or creating the function from scratch), you must consider if your function will need to accept arguments from the calling formula, how many arguments your function requires, what data types the arguments will be, and if you want to supply one or more default values for each argument.

When extracting a custom function from a formula, such as the function illustrated in Figure 6-2, the number and data types of arguments in your function are automatically determined by the number of unique database fields included in the original formula. In the example shown in the figure, the original formula the custom function is based upon contains two database fields: {Orders.Order Amount} and {Customer.Last Year s Sales}. When the custom function is extracted from this formula, the database fields are removed from the function and replaced with two arguments, both of which require currency data types (the data types of the original database fields) to be supplied.

The arguments that the extract process identifies are listed in the Arguments list of the Extract Custom Function from Formula dialog box. You ll see the original database fields that are being replaced with arguments, the data type of the argument (both of these columns are for your information and can t be changed), the name of the argument in the custom function, and the description of the argument. By default, the extract process will number arguments successively from the number 1, preceding the argument number with the letter v.

If you simply wish to allow the default v-numbered arguments to remain , you may ignore this section of the Extract Custom Function from Formula dialog box. However, in that case, the function illustrated in Figure 6-2 would appear like this in the function tree of the Formula Editor:

 CurrentYearDiscount(v1, v2)

It s more likely that you will want arguments in your custom function to have more meaningful names, perhaps replacing v1 with OrderAmount, and v2 with PreviousSaleAmount. To accomplish this, rename the v1 and v2 argument names in the dialog box. You may optionally add descriptions to each argument that will appear in the Formula Expert (described in Chapter 5).

The More Info Button

When you extract a custom function from a formula, you ll notice the Enter More Info button on the Extract Custom Function from Formula dialog box. If you click this button, the Custom Function Properties dialog box appears.

The shaded areas of this dialog box are just for reference ”they can t be changed here. The Summary and Argument descriptions are interchangeable with those in the Extract Custom Function from Formula dialog box ”changing the descriptions of the function and arguments in either place will change it in the other. However, the Category, Author, Display in Experts check box, argument default values, and help text can be changed only on this dialog box.

Author

This is, in essence, a comment field that you can either leave blank or fill with text of your choice (probably the name of the person who developed the custom function). This value will appear only when you edit the custom function or use it in the Formula Expert. This text won t be available in the Formula Editor.

Display in Experts

This check box determines whether or not the custom function will appear in the list of available functions when using the Formula Expert (covered in Chapter 5). If you uncheck this box, then you ll see the custom function in the function tree of the Formula Editor, but not in the list of available custom functions when using the Formula Expert.

Argument Default Values

For certain arguments, such as those that may accept department codes, months of the year, or other common values (the order amount/previous period sales examples here probably don t fall into this category), you may wish to supply a list of one or more default values that a user can choose from when they use the custom function in the Formula Expert. To supply these, click the Default Values box for the argument that you want to set the defaults for. This will display the Default Values dialog box.

Here, you may type in a default value that you want a user to be able to choose; then click the Add button. This will add the value to a list in the lower part of the dialog box. If you want to add additional values, type them in and click the Add button. If you wish to remove a value that s already been added, choose it in the lower list and click the Remove button. And if you wish to change the order of the default values you ve already added, choose the value in the list that you wish to move and click the up or down arrow to the right of the list.

When you click OK, the default value list will be added to the custom function and will appear in the argument list on the original Custom Function Properties dialog box.

Help Text

Clicking the Help Text button will simply display yet another dialog box where you can type in free-form text describing the custom function, providing more detail on its arguments or return value, or other helpful information. This help text will be visible only when editing this function later, or using it in the Formula Expert. This text won t be available in the Formula Editor.

Caution

There are certain requirements an existing report formula must meet to be used as the basis for a custom function. For example (and this list is not all-inclusive), the formula cannot contain any evaluation time functions (such as WhilePrintingRecords ), any summary functions used with database fields (such as Sum({Orders.Order Amount}) ), or any variables scoped other than Local. If you attempt to extract a custom function from such a formula, you ll either receive an error message indicating that the formula can t be used, and explaining why, or a function that may not perform the way you expected it to. You may either copy the formula to the clipboar d and paste it into the Custom Function Editor when creating the function from scratch (making modifications to the copied formula to allow it to work as a function), or edit the report formula (perhaps explicitly setting variable scope to Local), and then extract the function from the updated formula.

Creating Custom Functions from Scratch

There may be times when an existing report formula isn t available as the basis for your custom function. Also, you may have a need for a custom function that is more sophisticated than an existing formula, or the existing formula might contain elements that prohibit it from being used as the basis for a custom function (it may use global variables, evaluation time, or other limiting features). In these cases, you will want to create a custom function from scratch using the Custom Function editor.

In this case, the steps for initially creating the custom function are the same. Create and name the new custom function in the Formula Workshop as described previously in the chapter. However, in the Custom Function Name dialog box, click the Use Editor button to display the Custom Function Editor inside the Formula Workshop, as shown in Figure 6-3.

Figure 6-3: The Custom Function Editor

The Custom Function Editor is almost identical to the Formula Editor discussed in detail in Chapter 5. The difference is that the field tree, containing a list of database fields, is not visible. And the function tree, which contains a list of built-in Crystal Reports functions, contains a slightly reduced set of built-in functions. This variance is because custom functions are designed to be independent of any particular report they are placed in; you can t include database fields in a custom function ”there would be no way of ensuring these fields would be available when the function is called from another report. You are also prevented from including the limited set of built-in functions (such as Evaluation Time, Print State, and others) because of the stateless nature of a custom function.

Tip	Even though the function tree contains a reduced set of options, it will still display a Custom Function category where you can choose another existing custom function to use in the current custom function.

You may now just create the custom function logic by double-clicking built-in functions in the function tree and operators in the operator tree. You may, of course, type in these items yourself, as well as associated formula code. And as with the Formula Editor, you can check the syntax of your custom function before saving by clicking the Check button in the toolbar or typing ALT-C.

Syntax Choices and Requirements

When you create a custom function, you have the choice of both Crystal Reports formula languages (or syntaxes) just like you do when creating a report formula. Choose the desired syntax from the drop-down list toward the right of the Custom Function Editor toolbar. The function tree and the operator tree will adjust based on the chosen syntax. You ll also see a slight difference in the formula text that is automatically added by Crystal Reports.

The basic premise of creating a custom function directly in the Custom Function Editor is to design a formula to accept any necessary values passed into the function from the calling formula; perform evaluations, calculations, If-Then-Else tests, and so forth within the custom function; and return a value back to the calling formula. If you have created functions in a programming language, such as Visual Basic, then this should be a fairly straightforward process. If you haven t programmed in a programming language in the past, then getting the hang of custom function creation may take some time. But, if you re familiar with Crystal Reports formulas in general, you should be able to apply your existing knowledge to custom function creation pretty quickly.

Here is an example of a Basic Syntax custom function that returns the spelled-out name of a company department based on an abbreviated name passed in as an argument:

 Function SpelledDepartment (Abbreviation As String) As String 
 Select Case Abbreviation 
 Case "HR" 
 SpelledDepartment = "Human Resources" 
 Case "IT" 
 SpelledDepartment = "Information Technology" 
 Case "EXEC" 
 SpelledDepartment = "Executive" 
 Case "ENG" 
 SpelledDepartment = "Engineering" 
 Case Else 
 SpelledDepartment = Abbreviation 
 End Select 
 End Function

The basic (no pun intended) layout of a Basic syntax function is formula code within a Function and End Function block. The Function statement declares the name of the function (the same name you used when you created the function), a list of any arguments and data types the function should accept (a string argument named Abbreviation in this case), and the type of data the function will return to the calling formula (the data type As portion is optional ”if you don t include it, the data type the function returns will be determined by the data type you assign to the function name later inside the function).

The last statement within the Function “End Function block that assigns a value to the function name will determine what the function returns to the calling formula. In this case, the function uses a Select Case construct (from the Control Structures category of the operator tree) to test for various values of the passed Abbreviation argument, setting the function name to a string literal based on the value of the argument.

Here is the same function in Crystal syntax:

 Function (StringVar Abbreviation) 
 Select Abbreviation 
 Case "HR" : 
 "Human Resources" 
 Case "IT" : 
 "Information Technolgoy" 
 Case "EXEC" : 
 "Executive" 
 Case "ENG" : 
 "Engineering" 
 Default: 
 Abbreviation

In this case, there is no Function “End Function block ”all the formula text included in the function is implicitly considered to be within this block. The argument being passed to the function is included in parentheses immediately after the Function keyword, preceded by the data type that the argument is assigned (data type keywords are the same as those for declaring variables ”see the Variable Declarations section of the operator tree for proper spelling). Similar to the Basic syntax example, the last statement inside the function to evaluate will determine what the function returns to the calling formula. In this example, the Case statement that matches the passed argument will return a string value to the calling formula.

Complex Data Types and Optional Arguments

In the preceding examples, the custom function accepted a single simple string value as an argument and returned a single simple string value as a result. However, you may have more complex custom functions that need to deal with more than one argument, or complex data types , such as array and range values. While using the extract function, discussed earlier in the chapter, can create custom functions with multiple arguments, it can t create functions that accept or return complex data types. To accept range values or arrays as arguments, or pass similar data types back to the formula, you must use the Custom Function editor.

You also may create a special kind of argument in the Custom Function editor called an optional argument. By placing the Optional keyword in front of an argument name, you specify that the formula calling the function does not have to supply this argument. If the formula does supply the argument, it will override the default value for the argument that you must supply if you declare an argument as optional.

For example, if you create the following as the first line of a Basic syntax function:

 Function DaysBetweenDates _ 
 (BDate As Date, Optional EDate As Date = CurrentDate)

you will be able to call the function in a formula using one or two arguments, as the second argument is optional. If you call the function using two arguments, both will be supplied to the function. If you call the function using only one argument, that argument will be supplied as the BDate argument and the current date from the computer s system clock (CurrentDate is a built-in function) will be supplied as the second argument. Adding optional arguments will create multiple occurrences of the function in the function tree to indicate the ability to call the function with multiple arguments. For example, the single custom function created with the Function statement illustrated previously will appear in the function tree twice.

Optional arguments are declared in Crystal syntax with the Optional keyword as well, but with the standard Crystal syntax data type spellings and colon -equal operator used to assign the default value.

 Function (DateVar BDate, Optional DateVar EDate := CurrentDate)

Tip

Finer points on Crystal and Basic syntax when used in custom functions, such as how to declare complex data types in arguments and syntax differences between custom functions and report formulas, can be found in Crystal Reports online help. Search for Custom Functions Basic Syntax, or Custom Functions Crystal Syntax.

Modifying Existing Custom Functions

Once you ve created a custom function, you may wish to change it later. One of the benefits of custom functions over copying and pasting the same formula logic over and over again in multiple formulas is that all formulas based on a custom function will automatically reflect the change made in the one custom function. As such, a great deal of effort can be eliminated by sharing custom functions in the repository and making a single change to the repository. The change will automatically be reflected in all formulas in all reports that make use of that function. Storing custom functions in the repository is covered later in this chapter and in Chapter 7.

If you ve added the custom function just to one report, you may change a custom function in the same way you would change a formula: select it in the Formula Workshop and make changes directly to the formula text in the Custom Function Editor. When you save changes, any formulas using the function will immediately reflect the change.

Note

Even though you were able to initially extract a custom function from a formula using a dialog box, you will need to make changes to the custom function in the Custom Function Editor. Once you ve saved any custom function, even by extracting it from a formula, editing must be done in the Custom Function Editor.

If you ve added custom functions to your report from the repository, you ll notice that the Custom Function Editor formula text area is disabled for editing when you select the custom function in the Formula Workshop tree. This is because you have not disconnected the custom function from the repository. In order to change the contents of a repository-based function, you must disconnect the function ”this is similar to checking out the function from the repository.

To disconnect a custom function from the repository, right-click the function you wish to edit in the Formula Workshop tree (this must be a function in the Report Custom Functions category ”you can t disconnect or edit a custom function that hasn t first been added to the report). Choose Disconnect from Repository from the pop-up menu. You ll notice two changes: first, the custom function can now be edited in the Custom Function Editor, and second, the small icon appearing next to the custom function name in the Formula Workshop tree will no longer display the small linked icon.

You may not only edit the formula text of an existing custom function, but the summary, author, category, and other nonformula items that you specified when first creating the function. With the function you wish to edit displayed in the Custom Function Editor, click the Toggle Properties Display button in the Formula Workshop toolbar, or right-click the function name in the Formula Workshop tree and choose Toggle Property Display from the pop-up menu. The Custom Function Editor will be replaced with a dialog box summarizing all the other properties that can be set for the function. Make any necessary changes on this screen and click the same toolbar button or use the same pop-up menu choice to toggle back to the Custom Function Editor.

Once you ve made changes to the custom function and saved the changes, you may wish to add your new custom function to the repository for sharing with other reports or users. If the custom function is a modified version of an existing repository function, you will probably want to reconnect the custom function back to the repository with changes. The following section describes how to add a custom function to the repository.