BAQ - Strange Behavior

Epicor ERP 10
1

I have a BAQ using data from a subquery to create the below screenshot. The formula below is what was used to produce the data in the NEEDCOUNT column (0.00). The NEED and COUNT columns show the results of the two pieces of the formula while the TIMES, PLUS, and MINUS are the result of replacing the (/) with the appropriate mathematical sign (*, + , -). I have another BAQ which uses the same formula with different fields which produces the correct result.

Has anyone seen this behavior? If so, how do I correct the behavior? Any thoughts?

image

sum(SubQuery1.Calculated_NeedOnTime)/sum(SubQuery1.Calculated_ShipCount)

2

If one of the numbers is an integer (not a decimal) SQL is kinda picky and will truncate the division. Try casting both values to a decimal or double first before dividing.

3

Both were integers; I changed them to decimals but I am getting the same result.

4

Can I see your new formula?

5

I didn’t change anything with the formula but only changed the format for the two calculated fields in the subquery to be decimal and not integer. Was i to make a change to the original formula below:

sum(SubQuery1.Calculated_NeedOnTime)/sum(SubQuery1.Calculated_ShipCount)

6

You need to change the formula, case each of the items in the formula to a decimal.

CONVERT(decimal(16,4),sum(SubQuery1.Calculated_NeedOnTime))/CONVERT(decimal(16,4),sum(SubQuery1.Calculated_ShipCount))

See if that makes a difference

2 Likes
7

I made the below change and got the desired result; I also got the desired result using your suggestion as well. Thank you.

cast(sum(SubQuery1.Calculated_NeedOnTime) As Float)/cast(sum(SubQuery1.Calculated_ShipCount) As Float)*100

8

NP
Don’t forget to mark the answer as solved.