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.

7 Responses to “Refreshable Excel report for historical GL trial balance in Dynamics GP”

  1. Victoria,
    Another incredible gift to us. You continue to amaze!
    Leslie

    Like

  2. Victoria,

    What is the advantage of doing this in a stored procedure versus and view?

    Like

    • 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

      Like

Trackbacks/Pingbacks

  1. Refreshable Excel report for historical GL trial balance in Dynamics GP | Victoria Yudin - Microsoft Dynamics GP Community - August 17, 2015

    […] Victoria Yudin offers a Refreshable Excel report for historical GL trial balance in Dynamics GP  […]

    Like

  2. DynamicAccounting.net | Refreshable Excel report for historical GL trial balance in Dynamics GP | Victoria YudinRefreshable Excel report for historical GL trial balance in Dynamics GP | Victoria Yudin - DynamicAccounting.net - August 17, 2015

    […] Victoria Yudin offers a Refreshable Excel report for historical GL trial balance in Dynamics GP  […]

    Like

  3. Refreshable Excel report for historical GL trial balance in Dynamics GP – 8/13, Victoria Yudin | - August 13, 2015

    […] Continue reading on Source Blog […]

    Like

  4. Interesting Findings & Knowledge Sharing » Refreshable Excel report for historical GL trial balance in Dynamics GP - August 13, 2015

    […] More: Refreshable Excel report for historical GL trial balance in Dynamics GP […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: