Question

Vendor Spend BAQ Issue

  • 20 March 2023
  • 5 replies
  • 133 views
M

Hello,

I am very new to BAQ and SQL. I have not created this BAQ or report, but I have been tasked with fixing it.

The problem: The current BAQ is not pulling the correct numbers. When we pull the data from the supplier tracker (links) it shows different summations; by a huge margin on both sides. 

Please see attached for the BAQ itself and the designer file.

Thank you!

select 
	[Vendor].[VendorNum] as [Vendor_VendorNum],
	[Vendor].[VendorID] as [Vendor_VendorID],
	[Vendor].[Name] as [Vendor_Name],
	[Vendor].[Address1] as [Vendor_Address1],
	[Vendor].[Address2] as [Vendor_Address2],
	[Vendor].[Address3] as [Vendor_Address3],
	[Vendor].[City] as [Vendor_City],
	[Vendor].[State] as [Vendor_State],
	[Vendor].[ZIP] as [Vendor_ZIP],
	[Vendor].[PhoneNum] as [Vendor_PhoneNum],
	[Vendor].[TermsCode] as [Vendor_TermsCode],
	[POHeader].[OrderDate] as [POHeader_OrderDate],
	[POHeader].[TotalCharges] as [POHeader_TotalCharges],
	[POHeader].[TotalMiscCharges] as [POHeader_TotalMiscCharges],
	[POHeader].[TotalOrder] as [POHeader_TotalOrder],
	[VendCnt].[ConNum] as [VendCnt_ConNum],
	[VendCnt].[Name] as [VendCnt_Name],
	[VendCnt].[PhoneNum] as [VendCnt_PhoneNum],
	[VendCnt].[EmailAddress] as [VendCnt_EmailAddress],
	[Vendor].[PrimPCon] as [Vendor_PrimPCon]
from Erp.Vendor as Vendor
inner join Erp.POHeader as POHeader on 
	Vendor.Company = POHeader.Company
	and Vendor.VendorNum = POHeader.VendorNum
left outer join Erp.VendCnt as VendCnt on 
	VendCnt.Company = Vendor.Company
	and VendCnt.VendorNum = Vendor.VendorNum
	and VendCnt.ConNum = Vendor.PrimPCon

 

5 replies

M

Please ignore the attachment on the original post. This one here is the correct one.

1 Attachment
W

Is your BAQ supposed to return both open and closed POs - total spend with vendors?

M

Period to Period, our Received not invoiced is not an issue. The goal is to capture invoiced dollars either associated with matched purchase orders or misc invoice dollars. The exercise is how much do we owe a supplier/vendor according to their invoicing... Sum by supplier invoice date is fine.

M

Hello,

I received a good BAQ focused on invoices instead and it works for what we want it to do.

However, I would like to make it a report. Can anyone help with what Options and Filters to use?

Thank you

1 Attachment
M

I have gone ahead and made a Dashboard instead which fits my needs.

Thank you.

Reply


Terms & Conditions