Understanding the Formula Extractor

The easiest way to create a new function is to use an existing formula as the template. Crystal Reports gives you the option of specifying an existing formula and having it extract the code from it and use it to create a new custom function. This has two benefits. The first benefit being that Crystal Reports does the work for you. But the second benefit is that it also error checks the existing formula to make sure it is compatible with custom functions. As mentioned earlier, functions have a set of rules that they must follow and Crystal Reports won’t let you create a new function that doesn’t follow these rules. If it finds that a formula can’t be converted, it lists the reasons why so that you can learn from it and make changes if necessary.

The job of the formula extractor is to convert a formula into a generic format so that it can be used as a custom function. It does this by copying the majority of the programming code and scanning the formula for any report fields or database fields that need to be replaced. Since these fields aren’t allowed in custom functions, it creates an argument for each one and replaces the field name with an argument name. To see how this works, let’s look at a simple formula that calculates the number of days between two fields. Here is the original formula:

//This formula calculates the number of days it takes to ship
//an order from the original order date (includes the order date).
{Orders.Ship Date} – {Orders.Order Date} + 1;

The first two lines are a comment telling you the formula’s purpose. The third line calculates how many days it took to ship the product after the order was placed. It adds 1 to the difference so that the date of ordering is counted as one full day. This prevents it from saying that an order was shipped in zero days. The problem with this formula is that it only works with the fields Ship Date and Order Date. If you later wanted to perform the same calculation on two different fields, then you would have to repeat this logic in another formula.

Let’s use the formula extractor to convert this to a custom function that can be called from any formula. The results of running the formula extractor are as follows:

Function (dateVar v1, dateVar v2)
//This formula calculates the number of days it takes to ship
//an order from the original order date (includes the order date).
v1 – v2 + 1;

The first line uses the Function keyword to declare that this is a custom function. The function’s arguments are listed within matching parentheses. They are called v1 and v2 by default (we’ll see how to change this to a more significant name in the next section). The original formula had two report fields, Ship Date and Order Date. This corresponds exactly to how many arguments were created in the custom function.

The next two lines of code are just a copy of the comments and don’t have any significance except to show that the function extractor will copy everything from the original formula. The last line calculates the difference between the two dates, but this time using the argument names instead of the field names. You can see how the formula extractor copied the original formula almost verbatim. The only differences being that it declared arguments for each field and replaced the field names with the argument names.

Before leaving this example, there is one more question we haven’t answered yet: What happens to the original formula? Since the formula’s logic is now inside the new custom function, shouldn’t the formula be updated to call the new function? Yes, it should. And what’s even better is that the formula extractor will do it for you. Here is the revised formula so that it calls the custom function:

DateDifference ({Orders.Ship Date}, {Orders.Order Date});

It simply calls the new function and passes the two date fields to it. The formula is greatly simplified and anytime the custom function gets modified, the changes are reflected directly in this formula.

Creating custom functions is frequently done later in the report development process. For example, you might create a report formula and display it in the report. Then a little later, as you are writing another formula, you realize that some of its logic is similar to an existing formula. Rather than redo the logic, you decide to create a custom function using the formula extractor to convert the original formula. This saves you the work of having to rewrite the entire formula from scratch.