Skip to main content

Notifications

Announcements

No record found.

Business Central forum
Suggested answer

Is it possible to view Item No. in same report as sales using GL entries as basis for report?

Posted on by 5

We have several reports that pull sales data from the "G_L Entry" table directly. The report includes a lot of basic info about the sale, posting date, company, document number, country, GL code etc...

I often wondered, can we somehow include the Item No. here to show the sales figures by Item No. Then we could easily see the revenue generated by individual Item No.

So I have explored if its possible but haven't found a way yet.

Steps I've Tried:

  • I learned that there's the "G_L - Item Ledger Relation" table that can be used to join the "G_L Entry" table, from there, you can join the "Value Entry" table also to the item relation table. Then finally join the "Item Ledger Entry" to the value entry table. 
  • Once I wrote SQL script to do that, I filtered it to look at 1 specific sales invoice example to see if it would produce the Item No. involved.
  • The results were interesting because it appears to depend on the type of posting or Source Code. If the Source Code is more related to inventory (INVTPCOST), then all the info is available including the Item No. as we want to see. However, if the Source Code is "SALES" then it appears that it doesn't even create an entry to the "G_L - Item Ledger Relation" table as you can see by the NULL values on attached screenshot. If it doesn't create those entries, then there is automatically no way to link it back to the Item Ledger Entries.

Items_2600_GLEntriesQuestion.jpg_2D00_640x480.jpg_2D00_1410x216.jpg

Questions

1. Can anyone confirm this is the case, anything I'm missing?

2. Could there be some other way to do this that I'm missing with still using GL Entries as the main source of sales data? 

Thanks.

  • Suggested answer
    Dallefeld Profile Picture
    Dallefeld 11,423 User Group Leader on at
    RE: Is it possible to view Item No. in same report as sales using GL entries as basis for report?

    It's definitely not the easiest thing to do. Every entry related to inventory transactions that is posted to the general ledger has an associated value entry but not necessarily an associated item ledger entry. The value entry table is the association from item ledger to general ledger. There is nothing gained by going from the value entry back to the item ledger.

    You can use the Document No. from the general ledger to search/filter the value entry table. Source Code is too generic as sales shipments and sales invoices are both source code of SALES.

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

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

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

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,537 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,520 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans