Unified Operations & Dynamics AX Forum

Expand all | Collapse all

Report for Vendor Invoice list with GL breakdown

  • 1.  Report for Vendor Invoice list with GL breakdown

    TOP CONTRIBUTOR
    Posted Jan 21, 2019 06:38 PM
    I am a bit baffled from not being able to meet a seemingly basic accounting requirement, so hoping I am just not looking in the right place.

    We need a report that list all invoices recorded for a particular vendor, and for each invoice specify the GL distribution of the invoice, by ledger account:

    For example:
    Invoice # 123456 Invoice total: 105$
    breakdown:
    -- acct 640000 office supplies $60
    -- acct 650000 travel expense $40
    -- acct 210000 VAT paid           $5

    Invoice # 543567   Invoice total: 320$
    breakdown:
    .....
    .....

    any such thing?


    ​​

    ------------------------------
    Zvika Rimalt
    Functional Consultant
    Vancouver BC
    ------------------------------


  • 2.  RE: Report for Vendor Invoice list with GL breakdown

    SILVER CONTRIBUTOR
    Posted Jan 22, 2019 11:22 AM
    Hello

    I believe the "History by Transaction" report in Accounts Payable should meet your requirements.

    Thanks,

    ------------------------------
    Arush Kuthiala
    Western Computer
    Toronto ON
    ------------------------------



  • 3.  RE: Report for Vendor Invoice list with GL breakdown

    TOP CONTRIBUTOR
    Posted Jan 30, 2019 09:11 PM
    Edited by Zvika Rimalt Jan 30, 2019 09:47 PM
    "History by Transaction" seems to give me for each vendor the full GL breakdown (both "credit" and "debit" side) - and I only want to see the breakdown "debit side"
    In addition it will show me everything (payments and invoices) where I need to only see invoices.
    Still, it allows me to filter by vendor, so it is getting closer than anything else I have seen so far, thanks!

    ------------------------------
    Zvika Rimalt
    Functional Consultant
    Vancouver BC
    ------------------------------



  • 4.  RE: Report for Vendor Invoice list with GL breakdown

    SILVER CONTRIBUTOR
    Posted Jan 30, 2019 09:20 PM
    Hi Zavika

    If you leave the Main Account blank in the drop down it will show for all GL codes.

    Thanks,

    Arush

    ------------------------------
    Arush Kuthiala
    Western Computer
    Toronto ON
    ------------------------------



  • 5.  RE: Report for Vendor Invoice list with GL breakdown

    TOP CONTRIBUTOR
    Posted Jan 22, 2019 11:24 AM
    Hello Zvika.  It does sound like a pretty simple request, but it's a bit tricky. Most of the vendor reporting is based on the AP subledger.  The AP subledger doesn't have the offsetting ledger detail you're looking for.  If you go to the GL you can find the offsetting ledger detail, but you can't filter by vendor/invoice as they aren't part of the GL.  There is a "Vendor Invoice Journal" report in the AP module in the version I've got (D365 FO v8.0 pu15).  It lets you filter by vendor/invoice and has a drill down to the whole invoice.  Not exactly what you're looking for, but it's a start.  The link between the subledger and GL is there.  You should be able to build a custom SSRS report using that report as an inspiration.  It is also something you could build via PowerBI.  I'm not aware of an ootb report that haw what you want, but that doesn't mean it isn't in there somewhere.

    ------------------------------
    Kaare Boraas
    Relationship Manager
    PCL Constructors Inc.
    AB
    ------------------------------



  • 6.  RE: Report for Vendor Invoice list with GL breakdown

    TOP CONTRIBUTOR
    Posted Jan 22, 2019 03:53 PM
    Hi All

    We are using AX 2012 R3, and several occasion we were asked this report. unfortunately its something which is not out of box. The GL data and the vendor data is stored in different tables

    We are using following:
    -Designed ATLAS report for this requirement,
    -SQL (run by data admin personnel

    Best regards

    ------------------------------
    Nazim Lalji
    Dynamics AX Analyst
    Nazim Lalji
    Los Angeles CA
    ------------------------------



  • 7.  RE: Report for Vendor Invoice list with GL breakdown

    Posted Jan 24, 2019 12:06 PM
    I, too, have been baffled -- and very frustrated! -- by the fact that this report is not available. It really leaves me with pretty much no way to track my departmental budget. I cannot answer the question that always come to me from management, specifically, "Why are your actual expenses higher than what we budgeted?"

    There is another discussion of this topic at the link below. I was unable to do what Colby put in his reply. I appealed to some consultants we are using for reports and customizations. They said they can create the report using Power BI, but our management has told me they don't want to pursue this right now. I'm sure that's because it'll cost us consulting fees. Very frustrating!

    Unified Operations & Dynamics AX ForumUnified Operations & Dynamics AX Forum
    a9e3416c837a&tab=digestviewer#bm7d817825-973e-4028-84eb-cc5b5c561b9a&CLK=7ec28185-3068-47e6-b327-c7c68e1b08f4





    ------------------------------
    Mavis Moon
    Pivot Interiors, Inc.
    Santa Clara CA
    ------------------------------



  • 8.  RE: Report for Vendor Invoice list with GL breakdown

    TOP CONTRIBUTOR
    Posted Jan 25, 2019 08:33 AM
    It's not ideal, but if you go to General Ledger>Inquires>Voucher Transactions, you can personalize it to add the Document and Description fields. Depending on how you have your default descriptions set up for transactions from AP and Purchasing, the vendor key can be part of that default description. You can then export to Excel and filter by the vendor key in the description field.

    The one advantage to using this inquiry is that it does give you the full GL accounts and amounts as individual lines, so you can view both sides of the transaction for a given invoice.

    Like I said, it's not a great solution, but it can work. Also, since the full ledger account is available on this inquiry, you can filter by main account and/or a specific dimension if you need to produce a list of transactions that way for review.

    ------------------------------
    Steve Latta
    Accountant
    Ortec, Inc.
    Easley SC
    ------------------------------



  • 9.  RE: Report for Vendor Invoice list with GL breakdown

    TOP CONTRIBUTOR
    Posted Jan 25, 2019 08:40 AM
    Edited by Steve Latta Jan 25, 2019 08:40 AM
    Depending on which version and CU you are on, the accounting source explorer (General Ledger>Common>Accounting Source Explorer) gives you ​everything you are looking for:

    Full ledger account
    Account name
    Party number and Party Name (on vendor transactions, this is the vendor number and vendor name)
    Amount

    It's still an inquiry you'll need to filter to get what you want, but it has all the fields you need.

    ------------------------------
    Steve Latta
    Accountant
    Ortec, Inc.
    Easley SC
    ------------------------------



  • 10.  RE: Report for Vendor Invoice list with GL breakdown

    TOP CONTRIBUTOR
    Posted Jan 30, 2019 09:50 PM
    Hi Steve - how would you filter by vendor in the Accounting Source Explorer?

    ------------------------------
    Zvika Rimalt
    Functional Consultant
    Vancouver BC
    ------------------------------



  • 11.  RE: Report for Vendor Invoice list with GL breakdown

    TOP CONTRIBUTOR
    Posted Jan 31, 2019 09:56 AM
    ​Hi Zvika,

    On mine (in AX 2012 R3), there are columns for Party Number and Party Name. These will display the customer or vendor information as appropriate to the line. I'm not sure if they are visible there by default or if I added them via a personalization. With those columns, I can filter by vendor very easily and it shows the general ledger account and amount, along with the vendor number and vendor name, for each line of the transaction - both debit and credit side. See the image below - I reordered the columns for clarity.

    Accounting Source Explorer

    ------------------------------
    Steve Latta
    Accountant
    Ortec, Inc.
    Easley SC
    ------------------------------



  • 12.  RE: Report for Vendor Invoice list with GL breakdown

    GOLD CONTRIBUTOR
    Posted Jan 31, 2019 10:52 AM
    ​Hi Steve,

    I, too, use AX2012 R3 CU12. The Party Name and Party Number columns are visible in the Accounting Source. However, the fields are blank. The information is not populating. Is there a setting I need to select within the GL parameters to have this information populate? Or will my IT Group have to go in and change a setting under System Administration?

    Thank you,

    ------------------------------
    Grace Gillenwater
    Accounting Clerk
    Landau Uniforms
    Olive Branch MS
    ------------------------------



  • 13.  RE: Report for Vendor Invoice list with GL breakdown

    TOP CONTRIBUTOR
    Posted Jan 31, 2019 04:41 PM
    From the online documentation I m finding for the "accounting explorer" it sounds like that only certain functionalities in D365 who "support" that frame work, would fill the "party" and other fields.
    The examples given are of the Expense Management module.

    ------------------------------
    Zvika Rimalt
    Functional Consultant
    Vancouver BC
    ------------------------------



  • 14.  RE: Report for Vendor Invoice list with GL breakdown

    TOP CONTRIBUTOR
    Posted Jan 31, 2019 05:44 PM
    I am not aware of any modifications we made to get those to populate, and looking further at mine, it seems that only transactions from the AP side are. I cannot, for example, get any results when I filter by customer number. I will try to do some more research.

    ------------------------------
    Steve Latta
    Accountant
    Ortec, Inc.
    Easley SC
    ------------------------------



  • 15.  RE: Report for Vendor Invoice list with GL breakdown

    GOLD CONTRIBUTOR
    Posted Feb 04, 2019 10:29 AM
    ​Thank you Steve and Zvika. I will do a little research on my end as well.

    ------------------------------
    Grace Gillenwater
    Accounting Clerk
    Landau Uniforms
    Olive Branch MS
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role