BAQ Report to Excel error

Epicor ERP 10
1

I have BAQ Report that runs fine, but when I run it to launch in Excel, I get the following error:

“We found a problem with some of the content in ‘BAQ_Reportxxxx’ Do you want us to try to recover as much …”

image
image918×170 2.02 KB

When I hit Yes (to try and recover), I see a couple of things stand out. They always appear to be a line where the QOH is exactly zero. This happens when the sum of the bins is zero (like having 100 LB in Bin 101, and -100 LB in Bin 102).

image
image802×198 4.4 KB

The values in the report are all correct. But I’d like to fix whatever is causing the excel file that is generated to have that error.

Note that this report is normally emailed to me, and the attachment can have this issue as well.

2

Some more info on the BAQ Report…

It appears as though the extended value (the column with Excel warning marker, and ‘0.00000000…’ entry) is the issue.

The expression for that is:

=iif(Fields!Part_CostMethod.Value = "A", 
    Fields!Calculated_Avg_Total.Value, 
  iif(Fields!Part_CostMethod.Value = "S", 
    Fields!Calculated_Std_Total.Value, 
Fields!Calculated_Last_Total.Value)) * Sum(Fields!PartBin_OnhandQty.Value)

If I remove the SUM() part, I don’t get the Excel error (I also don’t get the result I want)

3

Have you tried limiting the length of the string or rounding the sum to X decimals? It looks like it is being passed to excel as a string, but Epicor is converting the sum as a decimal with way too many trailing zeros. Could be some floating point error with a super minisucle amount instead of zero which rounding would fix.

4

After “recovering”, Excel gives the following info on that cell:

image

The error only occurs when the Sum(Fields!PartBin_OnhandQty.Value) part of the expression is zero.

When the cost used is zero, it works:

image
image.png772×114 8.62 KB