Microsoft Dynamics NAV (Archived)

Calculate beginning balance per GL Account number with SQL

(0) Share

Report

Posted on by Community Member

Hi,

I am working on a requirement where I need to calculate beginning balance for a GL Account number using SQL.

For example,

Report = Balance Sheet for May, 2015

GL Account number = 1005,

I need to calculate beginning balance using SQL query.

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

Jens Glathe 6,092 on at

Like (0)

Report
Copy link

Link copied!

I am mildly worried by this kind of posts. I mean no offense, but what is NAV for? Why circumvent its functionality to implement parts in SQL, for whatever reason? If you must use this information in a different system on an on-demand basis, why not use it via web services? You have the C/AL functionality there, can use flowfields and other checking which is harder or impossible to do directly in SQL.

Was this reply helpful? Yes No
Verified answer

mmv 11,471 on at

Like (0)

Report
Copy link

Link copied!

Hi,

The Balance displayed in the GL Account Card is from the "G/L Entry" table, so you may write a query to bring the data from [Company Name$G_L Entry] table where the [Posting Date] < 01 MAY 2015 and [Account No_] = '1005', take the sum of [Amount] field.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

I regularly download a simple trial balance consisting of location, department, account #, account description, beg bal, p1, p2, p2...activity, and then the ending balance. 5,000 lines, 18 columns, on-demand. Plug it in and play.

Reason being that that is all that is needed to produce a rich set of custom financial reports, which no way in h-e-double-hockeysticks can be created in NAV.

That's one possible reason...

Was this reply helpful? Yes No
Jens Glathe 6,092 on at

Like (0)

Report
Copy link

Link copied!

Point taken :) RDLC reports really are a festering sore.

Was this reply helpful? Yes No
Suggested answer

Roberto Stefanetti 12,998 on at

Like (0)

Report
Copy link

Link copied!

hi,

only some suggestions.

you can use Excel Power Pivot & Power Query to build nice "BI like" reports.

nice doc here:

www.wise.is/.../BIwhitepaper_MicrosoftDynamicsNAV2015.pdf

you can use also SSRS (SQL Server Reporting Services) based on standard NAV Sample Reports with parameters

robertostefanettinavblog.wordpress.com/.../sql-server-reporting-services-ssrs-links-and-nav-demo-reports-2

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

Thanks, your suggestion worked !

Was this reply helpful? Yes No
amerriwether 25 on at

Like (0)

Report
Copy link

Link copied!

Kinjal,

I saw the solution for obtaining the beginning GL Balances. I was wondering if you have the SQL Query to add the GL Transactions? Thank you.

Anthony

Was this reply helpful? Yes No
amerriwether 25 on at

Like (0)

Report
Copy link

Link copied!

Why don't you just answer the guys question? These types of replies stink of pompous. Answer the question.. or get out ofvthe conversation.

Was this reply helpful? Yes No