Microsoft Dynamics GP SQL Reporting: overview for consultant

Plus SQL has advantage of joining GL with SOP, POP, IV and produce aggregated queries for specific set of posted transactions.  In this small article we will show you how to produce Balance sheet for account segment.

Let’s assume you have subdivisions, each of them has individual second segment on account.  Following statement aggregates Debit-Credit summary for this segment (1001 in the sample below):

select

b.ACTNUMBR_1+ ' '+ b.ACTNUMBR_2, sum(DEBITAMT) - sum (CRDTAMNT)

GL20000 a

join GL00100 b on a.ACTINDX=b.ACTINDX

where b.ACTNUMBR_2='1001'

and a.OPENYEAR=2007

group by b.ACTNUMBR_1+ ' '+ b.ACTNUMBR_2

order by  b.ACTNUMBR_1+ ' '+ b.ACTNUMBR_2 asc

You should see the results, similar to followings:

1000    1001   1000.00        

2000    1001   500.00        

4000    1001   -3000.00     

5000    1001   1000.00      

6000    1001   500.00         

Please, note, that you should always have 0 in summary debit minus credit, if you assume that your subdivision is responsible for its cash flow.  However, obviously in real life you should be checking if this is the case and your accounting department keeps posting accurately on relevant account segments.

 The following script will give you summary debit-credit for the segment:

select

sum(DEBITAMT), sum (CRDTAMNT), sum(DEBITAMT) - sum (CRDTAMNT)

GL20000 a

join GL00100 b on a.ACTINDX=b.ACTINDX

where b.ACTNUMBR_2='1001'

and a.OPENYEAR=2007

You can use this script to verify on the fly in SQL Query analyzer against GP company database if your subdivision 1001 balance is accurate

Your SQL developers can go ahead, use this scripts as a basic and advance them to reflect more complex logic