This is a migrated thread and some comments may be shown as answers.

Rounding decimal issue when using Sum

5 Answers 1038 Views
Report Designer (standalone)
This is a migrated thread and some comments may be shown as answers.
Ron
Top achievements
Rank 1
Ron asked on 08 Aug 2016, 02:44 PM

As shown in the image, the Amount totals are calculating correctly ( Price*NetUnits=Amount). However, when using the SUM function in the Subtotal, the calculation is off by a penny. The SUM function is adding the amounts then Rounding which is causing the issue. See the calculations below.

Line 1 (7/25/2016) - Price $13.06 * Net Units 9.44 = Amount $123.286 (Rounded $123.29)

Line 2 (7/29/2016) - Price $13.06 * Net Units 8.30 = Amount $108.398 (Rounded $108.40)

Subtotal $123.286 + $108.398 = $231.684 (Rounded $231.68)

Line 1 + Line 2 = $231.69

Any help would be appreciated. Thanks!

 

5 Answers, 1 is accepted

Sort by
0
Katia
Telerik team
answered on 10 Aug 2016, 02:09 PM
Hi Ron,

The described behavior is expected when using the Sum() function.

In case, you need to round the numbers with a custom approach you can use built-in Math Functions, for  Ceiling(), Floor(), Round() and Truncate().


Regards,
Katia
Telerik by Progress
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
Ron
Top achievements
Rank 1
answered on 10 Aug 2016, 03:19 PM
Thanks for the response Katia. I've tried using the Math functions for rounding. However, I have not been able to round to a certain decimal, only the nearest integer. What is the correct format for rounding to a decimal in an expression?
1
Accepted
Katia
Telerik team
answered on 12 Aug 2016, 01:24 PM
Hi Ron,

Let's say you want to calculate the subtotals using amount values rounded to two decimal places. For this, you can use Ceiling() function with the amount value multiplied by 100 and then divide the result by 100, for example:
= Sum(Ceiling(Fields.Amount * 100) / 100)

Another option could be to use Format() function to round the amount values, for example:
= Sum(CDbl(Format("{0:N2}", Fields.Amount)))


Regards,
Katia
Telerik by Progress
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
Ron
Top achievements
Rank 1
answered on 12 Aug 2016, 01:49 PM
Thank you Katia! You have helped me resolve the problem!
0
Jennifer
Top achievements
Rank 1
answered on 30 Sep 2016, 05:35 PM
I was having a similar problem and your solution worked for me as well!  Thanks!
Tags
Report Designer (standalone)
Asked by
Ron
Top achievements
Rank 1
Answers by
Katia
Telerik team
Ron
Top achievements
Rank 1
Jennifer
Top achievements
Rank 1
Share this question
or