Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Cost of Goods Sold ent...
Finance | Project Operations, Human Resources, ...
Suggested answer

Cost of Goods Sold entry tie back to Item ID

(0) ShareShare
ReportReport
Posted on by jerhsysacct 67

Hi

We have a requirement to show the entry to COGS by item id so that are auditors can see the relationship between the standard cost of item and the posting it creates in GL when the product is sold.  I cannot find a way in the front end through any personalisation to join specific sales order line to the item all the way through to the GL.  It is a problem when there are multiple sales order lines on an order which gets invoiced together.  The standard sales invoice posting creates separate lines in the GL for each item to recognise COGS but I cannot see a way to link the 2 and neither can our BI team.  The pic shows the transaction origin report.  Obviously we can tie up by amount but that is not the same as an ID. 

Any advice would be gratefully received.

pastedimage1601652072592v1.png

  • Suggested answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 293,278 Super User 2025 Season 1 on at
    RE: Cost of Goods Sold entry tie back to Item ID

    Hi jerhsysacct,

    Ludwig is correct. The details are stored in the subledger for inventory. Technically, all details what had been posted are stored in the tables InventTransPosting and InventSettlement. The inventory value report mentioned by Ludwig is using the details from these tables. You can also consider a specific custom report. In the past, I created it at some customers. The last time was on AX2009. Due to the changed data model in AX2012 compared to AX2009, this report can't be used on AX2012.

    I would first try to setup Inventory value reports. Ludwig did write some blogs about this report: dynamicsax-fico.com/.../ [He probably forgot to share this ;) ]

    If this would not answer you question, you can consider a customization for a report to compare inventory transactions with the ledger amounts with help of the tables mentioned above. Initially, it would be a report in total as one voucher amount can be a summarized posting of multiple items.

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Cost of Goods Sold entry tie back to Item ID

    Hi Joe,

    There is a general 'accounting' related problem with those COGS related inventory postings.

    The problem is that inventory transactions are not recorded 1:1 in accounting but that they are posted in a summarized manner; meaning that item transactions that share the same main account - findim combination are clumped into a single accounting posting line.

    If your auditors now come and want to have an explanation of the origin of the COGS main account postings you are a bit lost.

    One way is using an item findim.

    Another one - but this is something that you need to check - is the inventory value report. This report allows you to show more than just values but also COGS and other inventory cost transactions. Maybe that is already sufficient for your auditors.

    I don't have any other smart and easy workaround to analyze your COGS apart from doing some PowerBI analysis, which is, however, probably not very easy because of the points mentioned above.

    Best regards,

    Ludwig

  • jerhsysacct Profile Picture
    jerhsysacct 67 on at
    RE: Cost of Goods Sold entry tie back to Item ID

    Hi Ludwig

    Unfortunately we do not have the Item ID as a financial dimension.  Also many items are in the same item group so again would not be sufficient.  

    Are you suggesting there is no link between item and COGS entry line. I did look at the accounting source explorer and there are many empty "reference" fields. Could there be a way to populate these with the item id when the posting is made?

    thanks

    Joe

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Cost of Goods Sold entry tie back to Item ID

    Hi jerhsysacct,

    Do you have an item financial dimension?

    If you have one then this is easy, just run the trial balance by findim and you get your COGS split up by item (findim).

    If you don't then you would have to try doing some calculations based on your inventory transactions and the adjustments that are made e.g. by the inventory close. That's, however, not easy as it will require a lot of calculations.

    Would a reporting of the COGS by item group be sufficient? This should be easier especially if you post your item groups on different accounts.

    Best regards,

    Ludwig

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,278 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 232,000 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans