Rounding in Crystal Reports 9: Custom Style vs Formula Round() 3

[NorthernViking]

Hi!

I have a project where we use Crystal Reports 9, and the problem I have is this.

I get a number value in from the database, which is 12.7605. Now, I've edited this by right clicking on the field in the report, choosing Customize... in the Format Editor under the Number tab.

I've set Rounding to 0.001. This works out OK, but the problem is this rounds 12.7605 to 12.760. This has raised some complaints, as the customer expect 12.7605 to be rounded to 12.761 (5 rounded upwards).

Now, I've tried adding a formula instead, taking this data field and doing Round(datafield,3). This gives me the expected result, 12.761.

I've read up a bit, and it seems to me that the Format Editor Rounding uses what's called banker's rounding (5 goes to the nearest even number, in this case 0), while the Round() function uses "normal" rounding.

Now, can anyone verify this? It seems weird to me that Crystal Reports uses different rounding standards for these two possible ways of rounding a number...

I can change this by removing all number fields from the report and adding formulas instead, but this is a lot of work and might cause unexpected results in the report.

Is there any way to force the Format Editor Rounding to not use banker's rounding??

Thanks you guys!

 

[kenhamady]

I don't think format rounding uses the bankers rounding (I have never seen that). Another possibility is that what you see as 12.7605 could already rounded from 12.76049 which would round up to 12.7605 but also round down to 12.760 depending on how many digits you display. Show more digits or write a formula that multiplies the value by 1000 to see how far out the raw value goes.

Also, the main problem with format rounding is that the underlying value is not changed by the format. Any totals will use the underlying value which means your report columns won't 'foot' correctly.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[lbass]

Ken, I think the OP is correct--I tested this just by entering the value in a formula. The Format->number->round uses the bankers rounding, but round() will round it up. Couldn't think of a fix for the formatting though, so didn't respond. Banker's rounding is the way I learned, and there is a logic to it, in that it will be less likely to skew results, since about the half the time, you are rounding down, and half, up.

-LB

 

[kenhamady]

Nope it's a bug.

When my tests all rounded correctly in v12 I decided to do some more testing in all the versions that I have. It appears that versions 9 and 10 have a bug in the custom format rounding precision. It only affects numbers that have 4 or more decimal places that end in a 5.

So I don't think anyone ever intended CR to do bankers rounding because it never happens on 1 or 2 decimal numbers. Even on 3 and 4 decimal numbers I found that 8 out of 10 values round the normal way. Version 8.5 and version 12 don't have this bug at all. Not sure on XI yet.

So NorthernViking you might have to upgrade to v12. An SP for v9 probably won't help since the bug was still around in v10.

And I have another interesting post for my blog.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[NorthernViking]

Wow... This is interesting!

Thanks for investigating this, now at least I can get back to our customer and either advise with exchanging all data fields for formulas with round() or upgrading Crystal Reports. The last sounds like the best solution, but I guess it all comes down to costs.

If you have the time, could you test for version XI as well? They did intend to upgrade to this version some time ago, but I guess v12 would be best these days... Only if you have the time, you have done more than enough as it is!

Thanks again for taking the time and getting back to me!

 

[kenhamady]

I am pretty sure lbass still uses XI and had the same behavior as you, so that tells me the bug is still there in XI. Maybe lbass can confirm for us.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[TeyBrady]

XI has the bug as well. The round function works so you have a solution to the annoyance.

 

[NorthernViking]

Thank you so much guys! APPRECIATED!!

 

[lbass]

Yes, I was using XI, and I didn't test for other numbers of decimals, so I wasn't as thorough as Ken. However, I have trouble seeing it as a bug, since I was taught to round that way and it makes sense to me, although I do see the merit in having the formatting function match the rounding function.

-LB

 

[kenhamady]

lbass,

I would agree with you if Crystal was really doing bankers rounding. What I found instead was that it rounds down to both odd and even numbers in a very odd pattern. I wrote a blog post about this and posted a PDF that shows the pattern, if anyone is interested.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[lbass]

Thanks, Ken. I'll take a look at that.

-LB