Need Help Getting ROUND or CEILING to work properly on a SQL report

Epicor ERP 10
1

I am trying to edit a field on the job traveler to round up when it faces a decimal. The field takes our Production quantity and divides by a field from the part master (UserInteger1).

The example im working with now is ProdQty=17 and UserInteger1=4. This should result in the new field displaying 5. But regardless of which command i use ( Ceiling or Round ) it still displays 4.

Below is the expression im currently using. what am i missing or doing wrong?

=CEILING(Fields!ProdQty.Value \ First(Fields!UserInteger1.Value, “JobHead”))

This is all being used on Microsoft SQL Server Report Builder

2

You can’t mix integer and decimal. (I found out that the hard way) cast your integer as a decimal before dividing/rounding.

3

Brandon,

im not sure what you mean. Are you saying the fields themselves are different data types ( decimal vs integer) so they do not cooperate? how would i go about changing the integer so it all meshes properly? Is it as simple as editing the customization where the UserInteger1 field lies and changing it from integer to decimal? or do i need to go into the ExtendedUD table maintenance menu and recreate the field?

4

fair warning, I’m not an expert on this. I had to go look at a working query to see what I had to do to make stuff work.

You can convert it within your calculated field. convert(decimal,exp), This will turn an integer into a decimal. Anything that’s a decimal use this within to make sure it’s a decimal before doing any math with it. And the ceiling function will turn in back into an integer when it’s all done, so if you use that rounded result later I think you have to do it again.

Try this and see if it does what you want:
=CEILING(convert(decimal,Fields!ProdQty.Value) \ convert(decimal, First(Fields!UserInteger1.Value, “JobHead”)))

5

I think Brandon is just saying that round/ceiling/floor may/may not have issues depending on the format of values.
And you should be able to reconcile issues right in your expression… converting, using different methods, etc…

Here is a quick example of decimal.round I threw on a job travler.rdl I was working on.

=decimal.Round(((Fields!StockQty.Value * 10.375)/2), 0, MidpointRounding.AwayFromZero)

May or may not translate directly to your report.
Since I don’t know which RDL you were using, didn’t have integer1 in my dataset, etc…
And I hard coded the " * 10.375)/2 " just so my value would always have a decimal.