Tutorial 6-1. Using the Formula Extractor

Let’s practice converting a formula using the formula extractor. As an added bonus, we’re going to convert a formula that breaks some of the rules of custom functions and see how to handle it. The formula we’re going to convert displays the histogram chart on the Crystal Reports sample report Formulas.rpt.

WhilePrintingRecords;
NumberVar i;
NumberVar delimit;
StringVar HistoGram;
if {Orders.Ship Date} – {Orders.Order Date} >= 254 then
delimit := 0
else
delimit := {Orders.Ship Date} – {Orders.Order Date};
for i := 0 to delimit do
HistoGram := HistoGram + “6”;
HistoGram

1. Open the Crystal Reports sample report Formulas.rpt. This is found in the Crystal Reports samples directory. Save the report as Custom Histogram Function.rpt.
2. Preview the report and look at the Histogram column. It displays a string with hourglass icons. Each icon represents how many days it took to ship a product after it was ordered. The formula that creates this string is called For Loop Formula and was written with Crystal syntax.
3. Open the Formula Workshop dialog box by clicking on the Formula Workshop button.
4. Create a new custom function by right-clicking on the Report Custom Functions category and selecting New. Enter the name HistogramChart.
5. Click the Use Extractor button. The dialog box immediately opens and tells you that the formula can’t be converted because it contains the function WhilePrintingRecords and it uses global variables. Let’s see if we can get around this.
6. Click the Cancel button so you return to the Formula Workshop dialog box. Find the For Loop Formula and edit it. Since the function WhilePrintingRecords can’t be called, comment it out. We will add it back into the original formula after it gets converted. The new code should look like this:

//WhilePrintingRecords

7. The second error is that the formula uses global variables. The variable declarations don’t define a scope, but Crystal syntax says that if a scope isn’t specified then the default scope is Global. If you look at the formula you’ll see that the variables are really only used locally and declaring them as global isn’t necessary. To fix this, add the Local keyword at the beginning of the variable declarations.

Local NumberVar i;
Local NumberVar delimit;
Local StringVar HistoGram;

8. Save your changes.
9. Try to run the formula extractor again (see steps 3-5). This time there are no errors listed and the Formula Extractor dialog box opens.
10. Change the argument names from v1 and v2 to ShipDate and OrderDate respectively.
11. At the bottom of the dialog box is the Modify Formula checkbox. Click on it so that For Loop Formula gets automatically updated to call this function.
12. Click the OK button to save your changes. At this point the HistogramChart custom function gets created and the For Loop Formula gets updated.
13. We are almost finished except for one thing. In Step 6, you commented out the WhilePrintingRecords function because it isn’t allowed in a custom function. But we still need to call it from the report formula. So let’s edit the formula and add this function back.
14. You should still be in the Formula Workshop dialog box, so click on the For Loop Formula to edit it. The dialog box probably doesn’t look like what you are used to. This is because the formula was modified with the formula extractor. Click on the Use Wizard button to return the screen to normal.
15. The formula’s code is shown in the Definition area. It has one line that calls the HistorgramChart() function and passes the two date fields as arguments. We originally commented out the WhilePrintingRecords function, so let’s add it back as the first line of the formula so that it gets called for every detail record.
16. At this point, you’ve added the custom function and modified the original formula. Click the Save and Close button to close the Formula Workshop and save your changes.
17. Preview the report and you should see that the Histogram column still displays a list of hourglasses representing how many days it took to ship a product. Even though the report looks the same, you’ve made a lot of changes behind the scenes. The HistogramChart() can now be called elsewhere in the report if you need to display it.

This tutorial walked you through the steps of converting a fairly complex formula into a custom function. You also learned that even though the formula extractor might initially tell you that a formula can’t be converted, you can temporarily modify the formula to convert it and then go back and correct it later.