Refreshable Excel report for historical GL trial balance in Dynamics GP

Want to see your historical General Ledger trial balance in Excel? Yes, you can always print out the one from GP and dump it to Excel and manipulate the results, but there is an easier way.

Below is a stored procedure you can use to create this. As an added bonus, if you’re using GP Reports Viewer you can set this up to run inside Dynamics GP. This is using the calendar year for column labels, you can change them to match your fiscal year as needed.

What you will need:

1. The SQL stored procedure below.
2. This Excel file.
3. Either GP Reports Viewer to set this up inside Dynamics GP or some additional permissions to set this up in Excel for your users.

Here is the code for the SQL stored procedure:

create procedure gprv_sp_historical_GL_tb
@year int
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
--created Aug 13, 2015 by Victoria Yudin
--Flexible Solutions, Inc. 212-254-4112
--For updates see https://victoriayudin.com/
--Shows monthly GL trial balance (net change)
--Uses calendar fiscal year
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

set nocount on

select
g.YEAR1 [Year],
rtrim(a.ACTNUMST) Account,
rtrim(g.ACTDESCR) [Description],
sum(case g.PERIODID when 0 then 
	g.PERDBLNC else 0 end) [Beginning Balance],
sum(case g.PERIODID when 1 then 
	g.DEBITAMT-g.CRDTAMNT else 0 end) January,
sum(case g.PERIODID when 2 then 
	g.DEBITAMT-g.CRDTAMNT else 0 end) February,
sum(case g.PERIODID when 3 then 
	g.DEBITAMT-g.CRDTAMNT else 0 end) March,
sum(case g.PERIODID when 4 then 
	g.DEBITAMT-g.CRDTAMNT else 0 end) April,
sum(case g.PERIODID when 5 then 
	g.DEBITAMT-g.CRDTAMNT else 0 end) May,
sum(case g.PERIODID when 6 then 
	g.DEBITAMT-g.CRDTAMNT else 0 end) June,
sum(case g.PERIODID when 7 then 
	g.DEBITAMT-g.CRDTAMNT else 0 end) July,
sum(case g.PERIODID when 8 then 
	g.DEBITAMT-g.CRDTAMNT else 0 end) August,
sum(case g.PERIODID when 9 then 
	g.DEBITAMT-g.CRDTAMNT else 0 end) September,
sum(case g.PERIODID when 10 then 
	g.DEBITAMT-g.CRDTAMNT else 0 end) October,
sum(case g.PERIODID when 11 then 
	g.DEBITAMT-g.CRDTAMNT else 0 end) November,
sum(case g.PERIODID when 12 then 
	g.DEBITAMT-g.CRDTAMNT else 0 end) December,
sum(g.PERDBLNC) [Ending Balance]
 
from GL11111 g  --GL
inner join GL00105 a  --for account number
    on g.ACTINDX = a.ACTINDX
where g.ACCTTYPE = 1 and g.YEAR1 = @year
group by g.YEAR1, g.ACTDESCR, a.ACTNUMST

set nocount off
go
grant execute on gprv_sp_historical_GL_tb to DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.