Help! Calculated field Total of summary field giving incorrect result.

[ecpmII]

Please help with this SOON!!! - Calculated field Total of summary field giving incorrect result.

 

We have a report that has the following problem, hope someone can assist us with this real soon. Thanks.

 

Student Principal is a calculated field (unstored). The calculation is based on the Amount Collected (a number field) multiply by the Student Discount (a number field) divided by 100.

 

Example: cStudentPrincipal = AmountCollected * StudentDiscount / 100

218.44 * 80 / 100 = 174.75

In the body of the report the cStudentPrincipal computes fine.

 

We have a summary field (sRptStudentPrincipal) that is Total of cStudentPrincipal.

This field is in a sub-summary part and prints when the department breaks.

This result is sometimes one cent too much or one cent too little (it seems to be randomly truncating or rounding).

 

The same summary field is in the trailing Grand Summary to get the total of all departments at the end of the report.

 

Please help. We have tried the calculated field (cStudentPrincipal) as stored calculation that did not work.

 

Is there something unique or extra that has to be done when using a calculated field as total in a summary field? We have several report/layout with this problem.

 

Also, calculated field seems to randomly round or truncate incorrectly. What is the best way to use to ensure correct results all the time?

 

Thanks Very Much.

[Robert Schaub]

Your example of 218.44 * 80 / 100 = 174.75

brings back a total of 174.752

 

Which by the way could be done as follows

218.44 * .8 = 174.752

 

You are displaying decimal with 2 places

therefore if .005 or less will round down to .00

if .006 or higher will round up to .01

 

What can you do about it.? Use number like 218.50

 

i.e. your example

218.50 * 80/100 = 174.80

or

218.50 * .8 = 174.80

 

or you can display with more decimals

but who pays in fractions of pennies?

[andygaunt]

Have you tried to add the round function to the calculation field.

 

Round(AmountCollected * StudentDiscount/100,2)

 

This will always make sure that you round to two decimal places.

 

Your example of 218.44 * 80 / 100 = 174.75 is correct, because you are only displaying 2 decimal places, although it actually works out to 174.52.

 

So, when you do a subsummary, this .000 is being taken into account and will round up, giving you a false reading. But, if in your calc you use the round function, the number will only ever be 174.75 and therefore in your summary will be correct, to the nearest dime (or quarter, or cent)

 

HTH