Crystal Reports and rounding errors

Thursday 5 September 2013 @ 11:05 pm

This question usually comes up related to subtotals and grand totals. Someone will wonder why subtotals don’t add up exactly to the grand total. What they are seeing is a classic ’rounding error’. It is defined as “a miscalculation that results from rounding off numbers to a convenient number of decimals”, usually when adding up the numbers. This ‘error’ is actually older than computers. It appears regularly in financial statements because the the ledgers used to generate the statements have figures in cents but the statement is shown in whole dollars. It is even more obvious when ledgers are shown in thousands. Often a note is included that says “numbers may not add up due to rounding”.

But the key thing to remember is that despite the term ’rounding error’, there isn’t any error at all. It only looks like an error to those who forget about the hidden dollars or cents. Here is a simple example.

Say you have 12 ledger accounts and they all have a value of $13.33. You want to report in dollars so they all appear as $13 on the report. Now lets pretend there are 3 groups with 4 records in each group. The group subtotals would actually be $53.32, which would each be displayed as $53. With three groups your grand total would actually be $159.96 but would display as $160. All of the numbers displayed are as accurate as they can be without showing cents. There is no actual ‘error’. Yet if you add up the three visible subtotals you would get $159 and if you add up the details you get $156. So the rounding “error” is just that the displayed numbers may not look correct, even though they are correct.  See the picture below.  The numbers Crystal show you are the closest to the real totals of the raw values, even though they aren’t the numbers that you expect.

Rounding example

Rounding example

 

 

 

 

 

 

 

 

 

 

 

 

 

 

So what are your options?

1) Educate the users that the numbers are correct. This gives you numbers that are as accurate as they can be, without showing decimals.
2) Round at the detail level. If you round every single detail to the level that you are rounding the totals, then all the totals will add up consistently. But they will all be off anywhere from a bit to a lot.
3) Use formulas to round at the subtotal level and then use variables to add up the rounded subtotals to create the grand totals. This is a bit more work but it gives you grand totals that match the subtotals, but with less distortion from rounding at the detail level.
4) Show everything with all the decimals. Very precise, but not very convenient.

I prefer the first option, which is what you find in most financial statements, along with the note.








One Response to 'Crystal Reports and rounding errors'

  1. IdoMillet - October 7th, 2013 at 1:49 pm

    SP7 of the Crystal Runtime (released in Oct 2013) fixes a bug related to this.
    ADAPT01695598
    “Using Crystal Reports APIs for Visual Studio .NET results in incorrect results when Rounding on Sum or creating Running Total”

    http://search.sap.com/#query=+1874392


Leave a Reply

Jeff-Net
Recrystallize Pro

Crystal Reports Server