August 13, 2015 
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:
- The SQL stored procedure below.
- This Excel file.
- 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.
Victoria Yudin 
Hi Victoria,
I am attempting to figure out the code for the report “Historical Aged Trial Balance Summary”, is this something you have posted? I can’t find it but thought best to ask.
Thanks
LikeLike
Hi Dave,
This is not something that I make available for free, partly because it typically has to be customized for each request, but mostly because it took me a huge amount of time to create. If you’re interested in purchasing this, please let me know and we can discuss in more detail.
Thanks,
Victoria
LikeLike
Hi Victoria,
Could you provide me a quote and I will take it to my finance group to see if I can get approval?
Thanks,
Dave
Dave Cowan
BUSINESS DATA ANALYTICS MANAGER
[cid:image001.png@01DB30EE.3ABA28F0]
DIRECT 808-515-8711
16-166 MELEKAHIWA STREET ⢠KEAAU, HI 96749
[cid:image002.png@01DB30EE.3ABA28F0]
FIND US ON FACEBOOKhttps://www.facebook.com/HPMHawaii/, INSTAGRAMhttps://www.instagram.com/hpmhawaii/ & LINKEDINhttps://www.linkedin.com/company/hpmhawaii/ ⢠HPMHAWAII.COMhttps://www.hpmhawaii.com/
CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and/or privileged information. Any unauthorized review, use, copying, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender immediately by reply e-mail and destroy the original message and all copies.
LikeLike
Hi Dave,
I emailed you directly, please let me know if you don’t see the email.
Thanks,
Victoria
LikeLike
Victoria,
Another incredible gift to us. You continue to amaze!
Leslie
LikeLike
Victoria,
What is the advantage of doing this in a stored procedure versus and view?
LikeLike
Hi Michael,
Good question. The big difference in this particular case is that you can provide a parameter for a stored procedure – the year. So if you have a lot of data you will get better performance from a stored procedure.
-Victoria
LikeLike