Totally Invoice amounts by Product Group

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

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.

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

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

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.

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