I would like to run a vendor detail report by GL account number that shows the invoice #, invoice description, amount, invoice date, etc. for a specific vendor or by a specific GL account. What report can I run to obtain this information? I have tried to run the vendor account detail but it did not provide the information I need. Any suggestions are appreciated. Thanks.
To build on Malcolm's answer, Power BI or Excel will be the best tools. Start by creating and publishing web services for the following page: General Ledger Entries, Posted Purchase Invoices (at the least).
If using Excel, extract these queries using Data > From Other Sources > From OData Data Feed, and copy/paste the the ODATA URL for each of these web services. If using Power BI, you'll want to start by downloading and installing Power BI Desktop. And use the ODATA feed option.
The User name is the value in the User Name field on your User card. The password is the Web Service Access Key on the User card. (If the Web Service Access Key field is blank, click on the button next to the field to generate one).
Once the queries have been drawn into Excel or Power BI, you can connect the two by using the Data Modeling functionality, as suggested by Malcolm. This will then let you create a tabular or graphical report (with filters, if needed) showing the data you're looking for.
Note: If you also want to use Vendor Names instead of Vendor Nos., be sure to create a Web Service to pull data from the Vendors list page as well. And when modeling data, be sure to tie the No. field of the Vendors list page with the Vendor No. field in the Posted Purchase Invoices page.
I hope this helps. If my response has answered your question, please verify by clicking Yes next to "Did this answer your question?"
I can't see a way to do this directly with one report in D365FO because what you want overlaps both the GL and the AP ledger.
My approach would be to filter 'General Ledger Entries' for the account ranges and transactions you want then export to excel. The same with the 'Posted Purchase Invoices', filtering for the vendors you want, and export to excel. Use the two excel files as tables in Access, or similar tool, joining on the invoice number... create your report. There's a good chance you might be able to create something to do this in Power BI as well, but I've not investigated that.
Share
Report
All responses (
Answers (
5,754
