Refreshable Excel report for monthly GL budget in Dynamics GP

Dynamics GP has some great functionality for uploading a General Ledger budget from Excel. However, if you have a lot of accounts, getting the data back into Excel is sometimes a little more time consuming than desired. I recently created a refreshable Excel report that uses a SQL stored procedure for this and wanted to share it.

My code uses a calendar fiscal year, but you can easily adapt this to your fiscal calendar as needed. As an added bonus, if you’re using GP Reports Viewer you can set this up to run inside Dynamics GP.

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 stores procedure:

create procedure gprv_sp_monthly_budget
@budgetid char(15)
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
--created Apr 28, 2015 by Victoria Yudin
--Flexible Solutions, Inc. 212-254-4112
--For updates see https://victoriayudin.com/
--Shows monthly GL budget for given budget ID
--Uses calendar fiscal year
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

set nocount on

select 
rtrim(a.ACTNUMST) Account,
rtrim(d.ACTDESCR) [Account Name],
sum(case when b.PERIODID = 0 
   then b.BUDGETAMT else 0 end) [Beg Bal], 
sum(case when b.PERIODID = 1 then b.BUDGETAMT else 0 end) Jan, 
sum(case when b.PERIODID = 2 then b.BUDGETAMT else 0 end) Feb, 
sum(case when b.PERIODID = 3 then b.BUDGETAMT else 0 end) Mar, 
sum(case when b.PERIODID = 4 then b.BUDGETAMT else 0 end) Apr, 
sum(case when b.PERIODID = 5 then b.BUDGETAMT else 0 end) May, 
sum(case when b.PERIODID = 6 then b.BUDGETAMT else 0 end) Jun, 
sum(case when b.PERIODID = 7 then b.BUDGETAMT else 0 end) Jul, 
sum(case when b.PERIODID = 8 then b.BUDGETAMT else 0 end) Aug, 
sum(case when b.PERIODID = 9 then b.BUDGETAMT else 0 end) Sep, 
sum(case when b.PERIODID = 10 then b.BUDGETAMT else 0 end) Oct, 
sum(case when b.PERIODID = 11 then b.BUDGETAMT else 0 end) Nov, 
sum(case when b.PERIODID = 12 then b.BUDGETAMT else 0 end) [Dec],
sum(b.BUDGETAMT) [Total]

from GL00201 b

inner join GL00105 a
   on b.ACTINDX = a.ACTINDX

inner join GL00100 d
   on b.ACTINDX = d.ACTINDX

where b.BUDGETID = @budgetid

group by a.ACTNUMST, d.ACTDESCR

order by a.ACTNUMST

set nocount off

go
grant exec on gprv_sp_monthly_budget 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.