As a Report Designer, I have utilized various types of reporting software. One of my personal favorites is SAP Crystal Reports. This handy little program contains many useful features that allow users to turn raw data from a myriad of sources into accurate and attractive reports. However, as with most of us, Crystal has her, well, ‘quirks’.
One of these quirks relates to Crystal Reports’ processing model. Logically, summaries are processed on the last pass. Most of the time this won’t be an issue. But what if we need to create a summary or running total on a formula field that contains sums?
Take, for example, a field with the following formula: {Sales.OrderTotal} – {Sales. DiscountTotal}. If we want to create a running total on this field we simply right click on it and insert a running total. Piece of cake.
Now we are going to shake things up a bit and change our formula to SUM({Sales.OrderTotal},{Sales.ProductCategory}) – SUM({Sales.DiscountTotal},{Sales.ProductCategory}). When we right click on our field the ‘Insert’ option is nowhere to be found.
Inserting summaries or running totals in Crystal Reports can cause headaches when it comes to formula fields that contain calculations. Because of Crystal’s processing model, which evaluates summaries on the last pass, these formula fields will have to be totaled using a nifty 3-step technique. These steps involve the creation of 3 different formula fields in addition to the formula field we want a running total of. Included in this process is the creation of a RESET formula, a CALCULATE formula and a DISPLAY formula. Let me break it down for you.
Step 1: Create a new formula field to reset the variable.
The formula should look similar to this one:
WhilePrintingRecords;
CurrencyVar OrderTotal;
OrderTotal:= 0
Place this field in the Group Header above the group containing the calculation formula field. Remember to suppress this field if you do not place it in a group that is suppressed.
Step 2: Create a new formula to calculate your running total.
The formula should look similar to this one:
WhilePrintingRecords;
CurrencyVar OrderTotal;
OrderTotal:= OrderTotal + {@OrderTotalwithDiscounts}
This formula field should be placed according to which group level summary you wish to create the running total on. For example, if you want to accumulate the total order amounts per each Sales.Product in a single order then place this formula field in the Sales.Product group level.
Step 3: Create a new formula to display your results.
The formula should look similar to this one:
WhilePrintingRecords;
CurrencyVar OrderTotal;
OrderTotal
This field should go into the group level, usually the group footer, below the calculate formula. I often place this field in the group footer of the group I placed the RESET formula in.
Once completed the number from the DISPLAY formula field should be a total for those calculated in your original formula field.
