Totally Invoice amounts by Product Group

ERP 10
#1

Trying to create a simple BAQ that shows total invoices by Product Group.
image

So the problem is:

  1. If I sum on InvcHead.InvoiceAmt the total is wrong because of the one to many relationship between InvcHead and InvcDtl.(I need InvcDtl.ProdCode to get the Product Group.)
  2. I have tried summing on InvcDtl.ExtPrice + InvcDtl.TotalMiscChrg but the total is off < $1000.00 on + $350,000.00 in invoices.
    In round numbers:
    Inv Total = $3500000
    Total from #1 above = $480000
    Total from #2 above = $348500

Is there some relationship I am unaware of? Is my Invoice calculation on values in InvDtl wrong?

Any help would be appreciated.
Thanks

#2

You won’t need the join to ProdGrup unless you want the Description. Have you tried making it an outer join instead?

#3

I do need description. I have not tried that. Not sure I know how in the baq creator.

#4

Click on the join between InvcDtl and ProdGrup. The lower section will show the fields used in the join. On the far right there is a Dropdown to select the type of join. You will want to show all rows from InvcDtl.

#5

Thanks Jason. I have never noticed that. Sure that will solve my problem.