Building Custom Functions

This chapter has shown you a multitude of functions built into Crystal Reports. But these functions were designed to be utilized by a wide variety of people and they can’t possibly do everything you need them to do. Because of this, Crystal Reports gives you the ability to create your own custom functions that are designed for your specific needs. Just like the Crystal Reports’ functions, your custom functions can be used within any formula on your report.

If you write a lot of formulas for your reports, you might notice that some of the logic is repeated in different formulas. For example, if your company has offices in multiple countries, performing currency conversions in a formula is very common. You have to re-type this logic into each formula. To make matters worse, if the logic ever changes then you have to go back and fix it in every formula that used it. This can become a maintenance headache. By taking this common logic out of the formulas and putting it into a custom function, you centralize the code into a single location. The custom function can now be referenced by other formulas in the report and making changes to it is done in a single place. When the function is updated, all the formulas that reference will have new results.

Before getting into the steps for creating a custom function, let’s make sure we thoroughly understand how they work. This will make it easier to see how to best utilize them as well as understand what their limitations are.

How Custom Functions Work

To understand how custom functions work, you first have to understand their purpose. Custom functions allow you to share and reuse formula logic. Putting common logic into a single function makes it easier to maintain this code and makes it possible to simplify formulas that use the function.

Sharing program logic is a huge benefit because this isn’t possible with formulas. Throughout this whole chapter we’ve discussed how to use the Formula Workshop to build formulas, but these formulas are all independent of each other. They can’t talk to each other or have one call the other. A custom function’s sole purpose is to help other formulas do their job.

Since we were working with formulas in the previous chapter, let’s look at their characteristics and see how functions are different. Formulas are listed in the Field Explorer under the Formula Fields node. They are displayed on a report by dragging and dropping them onto a report. Custom functions can’t be used on their own (i.e. dragged and dropped onto a report). They have to be called from a formula. Since they can’t be displayed directly on a report, you won’t find them listed in the Field Explorer window. They are only listed within the Formula Workshop window under the Custom Functions category.

Formulas work with report data to produce their return value. As the report data changes, their results change. Custom functions are stateless. This means that they don’t have access to any report data and they have to have all external data passed directly to them via their arguments.

Formulas can’t directly pass data to another formula. If you want to share data, you have to use Global variables that are available to all formulas. Custom functions define a set of arguments that are used to get data from a formula. A formula talks directly to a custom function by passing data to these arguments.

Formulas are not allowed to return an array or a range type variable. Custom functions are allowed to do this.

Due to the differences between formulas and custom functions, limitations are imposed on custom functions.

• You can’t use report fields and database fields. This would violate the rules of being stateless.
• You can’t use global variables or shared variables (unless passed as an argument). However, you can create local variables and use them within the custom function.
• You can’t use recursion (a function calling itself repeatedly).
• You can’t use Evaluation Time, Print State or Document Properties functions.
• You can’t use the functions: Rnd(), CurrentFieldValue(), DefaultAttribute(), and GridRowColumnValue().

Now that you have an understanding of how custom functions work and what their limitations are, let’s see how to create them.

Creating Custom Functions

There are two ways to create a custom function. The first is to use an existing function as the basis and have Crystal Reports analyze it and build a new function from it. This is referred to as extracting the formula. The second is to create it from scratch on your own. You do this when you don’t have an existing formula to work with. Formula extraction is the easier of the two methods because you let Crystal Reports do most of the work.