I searched Dynamics GP documentation and the web for a SQL query to retrieve GL Balances that include beginning balance, ending balance and net change but could not come up with anything that would help me with what I needed.
I created the following SQL which creates a table that contains all the information I need to create a PowerPivot data model (more on this in the future article). The query uses a Cartesian product (cross-join) to include all periods for a given GL account whether there is any activity or not.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[GL_AccountBalances]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[GL_AccountBalances]
GO
SELECT
ACTNUMST AS [Account Number],
ACTDESCR AS [Description],
YEAR1 AS [Fiscal Year],
PERIODID,
SY40100.PERIODDT AS [Starting Date],
SY40100.PERDENDT AS [Ending Date],
CASE WHEN ACTIVE = 1 THEN 'Active' ELSE 'Inactive' END AS [Active],
CASE WHEN PSTNGTYP = 0 THEN 'Balance Sheet' ELSE 'Profit and Loss' END AS [Financial Statement],
CASE WHEN ACCTTYPE = 1 THEN 'Posting' ELSE 'Unit' END AS [Account Type],
ACCATDSC AS [Account Category],
CASE WHEN TPCLBLNC = 0 THEN 'Debit' ELSE 'Credit' END AS [Typical Balance],
CASE WHEN TPCLBLNC = 0 THEN 1 ELSE -1 END AS [TypBalMult],
CAST(0 AS numeric(19,5)) AS [Beginning Balance],
CAST(0 AS numeric(19,5)) AS [Net Change],
CAST(0 AS numeric(19,5)) AS [Ending Balance],
GL00105.*
INTO GL_AccountBalances
FROM GL00100, SY40100, GL00105, GL00102
WHERE GL00100.ACTINDX = GL00105.ACTINDX AND
SERIES = 0 AND
ACCTTYPE IN (1, 2) AND
GL00100.ACCATNUM = GL00102.ACCATNUM
-- Update Net Change
UPDATE GL_AccountBalances
SET [Net Change] = b.PERDBLNC
FROM GL_AccountBalances a, GL10110 b
WHERE a.ACTINDX = b.ACTINDX AND
a.[Fiscal Year] = b.YEAR1 AND
a.PERIODID = b.PERIODID
UPDATE GL_AccountBalances
SET [Net Change] = b.PERDBLNC
FROM GL_AccountBalances a, GL10111 b
WHERE a.ACTINDX = b.ACTINDX AND
a.[Fiscal Year] = b.YEAR1 AND
a.PERIODID = b.PERIODID
-- Update Ending Balance
UPDATE GL_AccountBalances SET [Ending Balance] =
(SELECT SUM(b.[Net Change]) FROM GL_AccountBalances b
WHERE b.ACTINDX = GL_AccountBalances.ACTINDX AND
b.[Fiscal Year] = GL_AccountBalances.[Fiscal Year] AND
b.PERIODID <= GL_AccountBalances.PERIODID)
-- Update Beginning Balance
UPDATE GL_AccountBalances SET [Beginning Balance] = [Ending Balance] - [Net Change]
When you execute the above SQL in TWO (sample company) database and query the GL_AccountBalances table, you see the following results (partial).
As you can see, these results match the GL Summary inquiry in Dynamics GP.
DISCLAIMER: I verified the results of the above SQL on the sample company database (TWO). If you see an issue or have a suggestion to improve this SQL, please let me know.
