Spend reporting by stock items number


I want to be able to extract via ODBC spend by items number. Which tables would people use to do this please? I need to run this for our procurement guys.

Many thanks


Spend meaning consumption? Try with InventTrans which contains information about inventory transactions.

Hi - many thanks for your response. It was more the supplier invoices less credits, by item number. The Inventtrans table doesn’t seem to line up with that - even if I only use the transaction type 3, “purchase order”. The report is to show suppliers what we have spent with them and on what

In VendInvoiceTrans you will find item number, quantities, prices and line amounts but no reference to vendor account. Is the tool you are using allowing joining two tables?

I can - it’s essentially SQL

So: VendInvoiceTrans join VendInvoiceJour where VendInvoiceTrans.InvoiceId = VendInvoiceJour.InvoiceId
These two tables contain all data that you need.

That worked perfectly. Apologies more questions but I don’t suppose you have any sort of road map / data dictionary for AX 2009 that shows these type of links?

No. But Microsoft website is very helpful. Look for the table and then go to Relations.

VendInvoiceTrans: msdn.microsoft.com/…/aa625652(v=ax.50).aspx

Again many thanks for your help - that is a fantastic resource