system version 11.2.400.11
ux platform version 10.1.72
Can someone please help me resolve this issue? I need to make a BAQ that shows all the invoices ever processed for each vendor/supplier. I have the 3 tables I think I need, APInvHed, APInvDtl, and Vendor. The vendor table will give me the vendor/supplier ID, and name. All other fields will come from ApInvHed and APInvDtl.
The issue I’m having is as soon as I pull any field from the APInvDtl table, I gain duplicates in my report. I am struggling how to remove these duplicates.
I added the field called PartNum from the APInvDtl table and my rows of data increases causing duplicates.
Can someone PLEASE HELP ME figure this out?? So much appreciated!! <3
Here’s how the table index looks:
APInvHed = Company, VendorNum, InvoiceNum.
APInvDtl = Company, VendorNum, InvoiceNum.
Vendor table = Company and VendorNum ONLY.
My current Query:
select distinct
[Vendor].[VendorID] as [Vendor_VendorID],
[Vendor].[Name] as [Vendor_Name],
[APInvHed].[InvoiceNum] as [APInvHed_InvoiceNum],
[APInvHed].[InvoiceDate] as [APInvHed_InvoiceDate],
[APInvHed].[InvoiceBal] as [APInvHed_InvoiceBal],
[APInvHed].[REFPONum] as [APInvHed_REFPONum],
[APInvHed].[InvoiceAmt] as [APInvHed_InvoiceAmt],
[APInvHed].[DocInvoiceAmt] as [APInvHed_DocInvoiceAmt]
from Erp.APInvDtl as [APInvDtl]
inner join Erp.APInvHed as [APInvHed] on
APInvDtl.Company = APInvHed.Company
and APInvDtl.VendorNum = APInvHed.VendorNum
and APInvDtl.InvoiceNum = APInvHed.InvoiceNum
inner join Erp.Vendor as [Vendor] on
APInvHed.Company = Vendor.Company
and APInvHed.VendorNum = Vendor.VendorNum
and ( Vendor.VendorID = ‘ACC-003’ )
If they need to know the Invoice Line Part Numbers and you add APInvDtl (which are the invoice lines), then your results will include both the Invoice header and it’s lines, increasing your result row count.
Technically these are not duplicates since the results include the Invoice Lines.
To remove the duplicates you would need to remove the APInvDtl from the BAQ.
Not sure why you need the PartNum. But if you really need to show them, but still only want one line per invoice, you can use STRING_AGG available since SQL Server 2017 STRING_AGG (Transact-SQL) - SQL Server | Microsoft Learn
This is exactly the case. When I remove the APInvDtl table the duplicate rows DO go away HOWEVER, I need a few fields from it.
These are the fields I need:
vendor name
invoice date
invoice number
part number
description
qty
uom
PO
unit cost
extended cost
amount
balance
HOW can I see these fields without any duplicate rows of data? Do I need to group? is my query off? thanks so much for your input! Much appreciated. I have been working on this for a while and just can’t seem to get it. Now it’s a priority. Thank you!
It’s not going to be possible to show fields from ApInvDtl without showing all lines on the invoice (Unless you only have one line per invoice).
Maybe you can create a dashboard where clicking on an invoice will show the details of the invoice (Separate BAQ).
Yes, this is exactly what I’m finding that as you stated, I cannot show fields from ApInvDtl without showing all lines on the invoice thus causing duplication YES I can put it on a dahsboard without issue. Of course they don’t want the report in a dashboard. I will try again. Thank you SO MUCH for your help!! Much appreciated! <3
How are you getting duplicate lines? It should only show once for each line on the invoice unless you have the same PN on different lines. Do you have a sample of the duplication?
I don’t know of any way in a BAQ to do “nested” (header/detail) output. If straight BAQ won’t work - is there anything in BAQ Report Designer that might? I don’t know SSRS to speak on it.
Outside the box, maybe something via Access/ODBC? Build the main report with APInvHed and Vendor…and use a subreport to pull data from APInvDtl.
I was just suggesting an option on how Linda might be able to solve her problem…Access report/subreport seemed to lend itself to it…
Main report - invoice 123 - supplier A - original invoice amount and open balance
Subreport - invoice 123 - invoice lines 1-10 to show what was on it
Main report - invoice 456 - supplier A - original invoice amount and open balance
Subreport - invoice 456 - lines 1-50 to show what was on it
Main report - supplier A totals for invoiced and open balance
(and so on)
Unless I’m misunderstanding completely, the issue is the BAQ is pulling in APInvHed amounts/balances with every APInvDtl line…thus duplicating the amounts charged or open…so you can’t just sum them up.
Ex: invoice 1 has 10 lines…header total $10 and open balance is $2.
BAQ results, if summarized, yields 10x$10 = $100 total, $20 open.
I built out Linda’s BAQ - maybe this will illustrate her duplication issue…filtered on one multiline invoice…same header amount appears on every detail line.
YES I can put it on a dahsboard without issue. Of course they don’t want the report in a dashboard. I will try again. Thank you SO MUCH for your help!! Much appreciated! <3
