Finance | Project Operations, Human Resources, ...

How do you round up the sum of the values?

(0) Share

Report

Posted on by Shooowtek

843

Hi,

I want to round up to an integer, the sum of the values in a given field. What will be the expression for this?

I've tried with this before:

Sum(Round(Fields!MyField.Value, 1))

Format(Sum(Fields!MyField.Value),0)

Sum(Round(Fields!MyField.Value, 0, System.MidpointRounding.AwayFromZero))

The requirements: for example, the numbers 6.1 or 6.4 or 6.8 - I want always display as 7, but I always get 6 as a result..

Thanks.

I have the same question (0)

All responses (10)

Answers (0)

Suggested answer

GirishS 27,827 Moderator on at

Like (0)

Report

Hi Shooowtek ,

Try using CEILING function in expression.

CEILING(Sum(Fields!MyField.Value))

If it doesn't work, you can do it from DP class - Using roundUp.

Thanks,

Girish S.

Was this reply helpful? Yes No
Shooowtek 843 on at

Like (0)

Report

Thank you GirishS.

Overall your suggestion is working. However, when I want to sum these values by category, unfortunately I do not get the correct total. I tried different variants of CEILING can not get the correct result.

=Sum(CEILING(Fields!Qty.Value)) => result 25

=CEILING(Sum(Fields!Qty.Value)) => result 29

Could you suggest how I should proceed?

I attach a helpful sketch.

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report

I guess its better to do it on the dp class itself. You can use roundup function to achieve the same thing.

In that case you know need to worry about the rounding when group by, since the temp table itself has rounded value.

Thanks,

Girish S.

Was this reply helpful? Yes No
Shooowtek 843 on at

Like (0)

Report

I just can't do it in the DP class because I have other calculations and operations there that are mutually exclusive with rounding. I have to round everything directly on the report.

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report

Ok, how you are assuming value must be 26 instead of 25?

what are all the values that sums up to 25?

Thanks,

Girish S.

Was this reply helpful? Yes No
Shooowtek 843 on at

Like (0)

Report

6.5 + 14.8 + 3.2 = 24.2 and it probably rounds up to 25

It should be 26, because it should add up the values already rounded up, i.e. 7 + 15 + 4 = 26.

What I don't understand is why it returns 25 as the result. In this expression Sum(CEILING(Fields!Qty.Value)) I am adding up the values rounded up, so I don't know why only the final total is rounded up....

In addition, I don't understand why it returns 29 in this expression....CEILING(Sum(Fields!Qty.Value))

It follows that for the value 6.5 it takes 8, for 14.8 it takes 16 and for 3.2 it takes 5 (8+16+5 = 29)

Or is it the case here that on a row it is already rounded up, e.g. from 6.5 to 7, and if I later sum it by category it again the value rounded up from 7 to 8?

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report

First expression is summing up all the value and do a ceiling.

For the second expression instead of ceiling use floor function

But using floor function may work for that case. But for some cases it will fail.

If that works you need to check for all the scenarios.

Thanks,

Girish S.

Was this reply helpful? Yes No
Shooowtek 843 on at

Like (0)

Report

Unfortunately, the floor does not work as we would expect. It returns a result of 23...

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report

Sorry to say that - I guess only Ceiling and Floor functions are available to round up or round down the values.

I don't have any idea on this. It will be better if you go with changing the rounding in DP class, instead of trying with report expression.

I understand you will have calculation on the DP class but from my view rounding the values in DP class is the only option.

Thanks,

Girish S.

Was this reply helpful? Yes No
Suggested answer

Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report

You can simply try using roundup function in d365fo in dp class. This will round to next number which satisfies your requirement.

For more information you can follow this link

d365ffo.com/.../

Was this reply helpful? Yes No