Why can’t I total this formula?

Thursday 24 August 2006 @ 3:19 pm

I see this type of question all the time.

“I wrote 3 formulas and Crystal lets me do a grand total of 2 of them, but not the the third one. Why not?”

When I teach this concept in class I have to start by explaining the “hump” that exists in the report creation process. The hump is my name for the step where Crystal does all of the grouping, sorting and totaling. Most formulas can be calculated before the hump, while some must be calculated after the hump.

Say you write a formula that uses a subtotal as part of the calculation. This formula will have to be calculated after the hump because it has to wait for all of the totals to be calculated first. If you want to then do a total of this formula you have a problem. The total would need to happen as part of the hump, but the formula won’t be ready until after the hump. And once you have crossed the hump, you can’t go back.

So what do you do if you are trying to total a formula, and that formula uses another total? The formula itself cant be totaled the normal way because you can only go over the hump once, So to total a formula like this you must write a running total using variables. This technique is described in both the Expert’s Guide to Crystal Reports Formulas or the Expert’s Guide to Totals in Crystal Reports.

The technical terms for formulas that are before or after the hump are “first pass formulas” and “second pass formulas”. First pass formulas are those that are calculated before the hump. Most formulas are first pass formulas, and can be used for sorting, grouping, selecting and totaling. A formula only has to wait for the second pass if it:

1) Uses a summary function like Sum() or Maximum()
2) Uses the functions Next() or Previous()
3) Uses a variables that is shared with subreports
4) Starts with the function WhilePrintingRecords
5) Uses another formula that includes one of these 4 features

There are several restrictions on what you can do with second pass formulas. As you already know, you can’t use them with total functions, which also means that you can’t use them in Cross-tabs or Charts. They are also ineligible for grouping, sorting, or being put into the selection formula.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro