 # A total of a formula instead of a formula of totals.

Saturday 31 December 2011 @ 3:12 pm

Say you have several columns of numbers that get added together like Price, Tax and Shipping. At the end of a customer group you would have three subtotals. You might decide to write a formula that adds those three subtotals together to get the combined total for the customer. It would work fine but there is usually a more efficient way to do this. Instead of writing a formula that combines the subtotals I would write a formula that combines the fields at the detail level. It would look like this:

`   {order.ExtPrice} + {order.tax} + {order.shipping}`

Then I would do a subtotal of this formula for the customer. The subtotal of this formula gets you the same value as the formula that combines the subtotals.  So why is this more efficient?  Because if you have other groups, or if you need a Grand total all you have to do is check “add to all group levels” when creating the summary field.  (If your version of CR doesn’t have this check mark you can copy and paste the summary field to all the other sections like report and group footers. ) This one formula can support all group levels as well as the grand totals.  And if you ever have to change the original formula you only have to change it in one place.

Using the original method you would have to write a separate formula for each group level and another for the grand totals.  And if the formula ever changes you would need to modify every one of the formulas.

In my mind I picture it this way:  Instead of going “down” the subtotals first and then “across” in a combined formula, you first go across to combine the details, and then go down to summarize that formula.

If you want to learn more about the 4 different types of totals that you can do in CR, and when to use each type, you should get “The Expert’s Guide to Totals in Crystal Reports “.

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