Tutorial 3-5. Using a Formula for the Running Total

Using a formula to replace the running total field probably sounds like a lot of work. But you’ll see that it’s really quite simple once you do it a couple times. This tutorial uses a formula to print the line number on every row, and for each page it gets reset back to zero. This is similar to the RowNumber function in Crystal Reports except that the RowNumber function is cumulative over the entire report and never gets reset back to zero.

1. Open the Crystal Reports sample report, Group By Intervals.rpt. Save it as RunningTotalFormula.rpt.
2. We want to put the line number in the left most column of the page, but there isn’t room because the Customer Name is left justified. So move the Customer Name field and its header one quarter inch to the right.
3. Let’s create the summary formula which tracks the line number. In the Field Explorer window, right-click on the Formula Fields category and select New. Name the formula LineNumber. Click the OK button to open the Formula Workshop.
4. Enter the following formula:

WhilePrintingRecords;Global NumberVar LineNumber;LineNumber := LineNumber + 1;

This formula creates a global variable, LineNumber, and increments it by one each time it is called. Since this formula doesn’t use any database fields, it has to have the WhilePrintingRecords keyword to force it to be calculated while the report runs.

5. Click the Save and Close button to save the formula and return to the designer.
6. Drag the LineNumber formula onto the report in the Details section. Put it directly to the left of the Customer Name field and resize it so that it fits.
7. By default, the LineNumber field will display two decimal places. You need to format this field so that it displays as an integer. Right-click on the field and select Format Field. On the Number tab select the format “-1123”. Click the OK button to save the change.
8. Now we need to create the reset formula so that the line number gets reset back to zero for every page. Create another new formula and call it LineNumberReset.
9. In the Formula Workshop dialog box, enter the following formula:

WhilePrintingRecords;Global NumberVar LineNumber;LineNumber := 0;

10. This formula resets the LineNumber variable back to zero. Click the OK button to save the formula and return to the designer.
11. Drag the LineNumberReset formula onto the Page Footer section.
12. Since you don’t want the user to see a zero in the page footer, it should be suppressed. Right-click on the field and select Format Field.
13. In the Format Editor dialog box, click on the Common tab and check the Suppress property. Click the OK button to close the dialog box.
12. Preview the report and you should see a line number on every row and the line number gets reset for every page printed. It should look similar to the following figure.