Microsoft Dynamics NAV (Archived)

GL account data

(0) Share

Report

Posted on by Community Member Microsoft Employee

Which table stores the sum of amount of GL entry's by total accounts ?

Best regards

*This post is locked for comments

All responses (12)

Answers (1)

Suggested answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: GL account data

Hi Raokman,

You can use query as below.

SELECT [G_L Account No_] , SUM(Amount) as Amount

FROM [Demo Database NAV (9-0)].[dbo].[CRONUS International Ltd_$G_L Entry] G

Where [Posting Date] <= '05-31-17'

Group by [G_L Account No_]
Community Member Microsoft Employee on at

Like (0)

Report

RE: GL account data

but how do you limit that query to a date range or date point?
Verified answer

Community Member Microsoft Employee on at

Like (1)

Report

RE: GL account data

Use this query

SELECT [G_L Account No_] , SUM(Amount) as Amount

FROM [Demo Database NAV (9-0)].[dbo].[CRONUS International Ltd_$G_L Entry] G

Group by [G_L Account No_]
Community Member Microsoft Employee on at

Like (0)

Report

RE: GL account data

Ok i will try to use these views to get the sum of amounts grouped by account code.

please let me know if there is any other alternative .

Thank you
Suggested answer

Community Member Microsoft Employee on at

Like (2)

Report

RE: GL account data

Hi Syrine,

You would not get these fields in SQL, as these fields are calculated fields.

Instead you can use SQL Views like

Select * from .[dbo].[CRONUS International Ltd_$G_L Entry$VSIFT$1]

or

CRONUS International Ltd_$G_L Entry$VSIFT$2

CRONUS International Ltd_$G_L Entry$VSIFT$3

CRONUS International Ltd_$G_L Entry$VSIFT$4

or SQL Queries to fetch the sum from G/L Entry Table.
Community Member Microsoft Employee on at

Like (0)

Report

RE: GL account data

thank you for your answer . In fact i am aworking on a BI project which extracts data from Nav database and i was hoping to use balance field in my Datawerhouse .
Suggested answer

Nareshwar Raju Vane... 5,596 on at

Like (1)

Report

RE: GL account data

Hi,

Since Balance is a FlowField on the Table 15 inside the NAV, you cannot query it in SQL.
Community Member Microsoft Employee on at

Like (0)

Report

RE: GL account data

These are the fields . Sorry for the bad screenshots , i don't have nav licence to get to the table designer
Suggested answer

Alexander Ermakov 28,094 on at

Like (2)

Report

RE: GL account data

You can use the standard page Chart of Accounts for that purpose, looking at Balance field. If you make visible "Limit totals to" filter, you can filter the balance result by a certain date. E.g., putting "..31.01.2016" there will show you the sum for G/L accounts at 31st of January.

As Nareshwar already mentioned, this page is based on the Table 15.
Suggested answer

Nareshwar Raju Vane... 5,596 on at

Like (0)

Report

RE: GL account data

Hi,

The mentioned fields must be in Table 15. I have attached a screenshot. Please attach your screenshot too.

Hope it helps.