Microsoft Dynamics GP SQL Reporting - Overview for Consultant

 


Visitors: 104

Microsoft Great Plains Dynamics GP is SQL driven application and SQL scripting should be good support for Finance department in analyzing their financial positions, especially when you have cash flow responsible subdivisions within your company. Similar job can be done in such tools as FRx, Crystal Reports, however it is often too difficult to produce new FRx report, and it is too quick to run select scripts against your GL. 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)
from
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)
from
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

Andrew Karasev, Alba Spectrum Group, http://www.albaspectrum.com help@albaspectrum.com 1-866-528-0577, 1-630-961-5918, serving GP clientele locally in Chicago and Houston plus USA and Canada Nationwide via remote support: San Francisco, Los Angeles, New Orleans, Miami, Orlando, Phoenix, Dallas, New York, Denver, Seattle, Minneapolis, Montreal, Toronto, Vancouver, Atlanta, San Diego

(410)

Article Source:


 
Rate this Article:  0.0/5(0 Ratings)

Related Articles:

Bill of Lading - Custom Reporting for Microsoft Great Plains - Overview for ..

by: Andrew Karasev (May 23, 2005) 
(Computers and Technology/Software)

Microsoft Dynamics GP: Project Accounting Integration Scenarios - Overview for ..

by: Andrew Karasev (December 29, 2005) 
(Computers and Technology/Software)

Microsoft Dynamics CRM Customization or Third Party Product - Overview for ..

by: Andrew Karasev (December 22, 2005) 
(Computers and Technology/Software)

Business Portal 3.0 for Microsoft Dynamics GP Great Plains - Overview for ..

by: Andrew Karasev (June 02, 2007) 
(Computers and Technology)

MCP Market News: Microsoft SQL Server & SQL Reporting Services

by: Guy Leba (May 17, 2005) 
(Computers and Technology/Software)

Parameters in Microsoft SQL Server 2000 Reporting Services

by: Tom Bizannes (September 26, 2005) 
(Computers and Technology/Software)

Microsoft Dynamics CRM 3.0 Customization, Reporting Integration Highlights

by: Andrew Karasev (November 15, 2005) 
(Computers and Technology/Software)

Microsoft Dynamics GP - New Customization, Development, Integration, & ..

by: Andrew Karasev (September 12, 2005) 
(Computers and Technology/Software)

Reporting for Microsoft Great Plains/Dynamics/eEnterprise: RW - ReportWriter - ..

by: Andrew Karasev (May 16, 2005) 
(Computers and Technology/Software)

Microsoft Dynamics GP 9.0 - Great Plains: Customization, Integration, Reporting .

by: Andrew Karasev (January 05, 2006) 
(Computers and Technology/Software)