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.

6 Responses to “Refreshable Excel report for monthly GL budget in Dynamics GP”

  1. I’ve just been writing something similar which I am pleased I have not posted yet as yours is for more compact.

    Like

  2. Victoria,

    Fantastic! Showed this to my lead accountant after adjusting to our fiscal year and he said that the CFO was just recently looking for this reporting from GP. He wants to send flowers:) The CFO is extremely happy with this as well.

    Thank you. You rock!

    Like

Trackbacks/Pingbacks

  1. Refreshable Excel report for monthly GL budget in Dynamics GP – 4/28, Victoria Yudin | - April 29, 2015

    […] Continue reading on Source Blog […]

    Like

  2. Refreshable Excel report for monthly GL budget in Dynamics GP | Victoria Yudin - Microsoft Dynamics GP Community - April 29, 2015

    […] Victoria Yudin delivers a Refreshable Excel report for monthly GL budget in Dynamics GP  […]

    Like

  3. DynamicAccounting.net | Refreshable Excel report for monthly GL budget in Dynamics GP | Victoria YudinRefreshable Excel report for monthly GL budget in Dynamics GP | Victoria Yudin - DynamicAccounting.net - April 29, 2015

    […] Victoria Yudin delivers a Refreshable Excel report for monthly GL budget in Dynamics GP  […]

    Like

  4. Interesting Findings & Knowledge Sharing » Refreshable Excel report for monthly GL budget in Dynamics GP - April 28, 2015

    […] See the original post: Refreshable Excel report for monthly GL budget 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: