Dynamics Community Forum Thread Details

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

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.