Dynamics Community Forum Thread Details

How to get data from GL Entry and GL budget entry in any BI tool ?

I am creating a report with the data of GL entry and GL Budget entry in any BI tool. I want to show actual vs target, variance or dimension wise report. I don't know which the field on the basis of which we can extract the data. I want to show the data dimension wise- dimension value wise.

how I can extract the data from these two tables? On which field i should extract the data.

I want to know that is there any way in NAV we can find out that how NAV is creating the relationship in background OR is there any document that has the list of relationships among tables ?

Thanks for your time.

Hi mogliBl,

Starting with the basics:

If your BI tool reads from NAV tables, look to Table 17 (G/L Entry) and Table 96 (G/L Budget Entry). From your post, it looks like you already have this part figured out.

If your BI tool uses OData, then you'll want to create web services in NAV to expose the following pages: Page 20 (General Ledger Entries), and Page 120 (G/L Budget Entries). I always recommend using Pages and OData because you will have an easier time working with Flowfields (calculated virtual fields) and will not have to re-create / recalculate that.

Regarding fields:

Global Dimension 1 and 2 are available out of the box on both the General Ledger Entries page and the G/L Budget Entries. Those, in combination with the G/L Account No. and Date (or more appropriately, filtered on a date range like Month) should get you what you need. In Power BI, there is a visualization called Matrix that allows you to show the breakdown value wise.

If you want to do this for Shortcut Dimensions 3 through 8, then you'll need to work with the Dimension Set ID field on each of those two pages, and also include the Dimension Set Entry table in your dataset / data model.

On the General Ledger Entries page and the G/L Budget Entries page, you will find an integer value in the Dimension Set ID page. In the Dimension Set Entry table, filter on that integer value in the Dimension Set ID page, and you will find one or more records showing the combination of Dimension / Dimension Values that it refers to.

I hope this helps. If my response has answered your question, please take a moment to verify it by clicking Yes next to "Did this answer your question?"

Quick Links

Leaderboard