Refreshable Excel report for monthly actuals vs. budgets in Dynamics GP

May 25, 2016 by Victoria Yudin 20 Comments

A lot of the requests I get are for actual vs. budget reporting. Certainly FRx and Management Reporter can do this pretty easily, but more and more I see users wanting this in Excel with a refreshable report. This blog post will give you everything you need to create a refreshable Excel report that shows monthly and YTD actual vs. budget for every account when you select a year, month (period for those not on a calendar fiscal year) and budget ID.

Here is a screenshot of the results (click on the image to make it bigger):

My example uses ‘month’, 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 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 stores procedure:

create procedure gprv_actual_vs_budget
@year int, @month int, @budget varchar(15)
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
--created May 25, 2016 by Victoria Yudin
--Flexible Solutions, Inc. 212-254-4112
--For updates see https://victoriayudin.com/
--shows monthly and YTD actuals vs.budgets w/variance
--includes unit accounts
--shows open and historical years
--uses net changes, not ending balances for balance sheet accts
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

set nocount on

select
rtrim(an.ACTNUMST) Account,
rtrim(m.ACTDESCR) [Account Description],
coalesce(a.Monthly,ah.Monthly,0) [Monthly Actual],
coalesce(b.Monthly,0) [Monthly Budget],
coalesce(a.Monthly,ah.Monthly,0)
  - coalesce(b.Monthly,0) [Monthly Variance],
coalesce(a.YTD,ah.YTD,0) [YTD Actual],
coalesce(b.YTD,0) [YTD Budget],
coalesce(a.YTD,ah.YTD,0)
  - coalesce(b.YTD,0) [YTD Variance]
from GL00105 an -- account numbers

left outer join --actuals from open year
(select a.ACTNUMST,
 sum(case when g.PERIODID = @month
    then g.DEBITAMT - g.CRDTAMNT
    else 0
    end) Monthly,
 sum(case when g.PERIODID <= @month
    then g.DEBITAMT - g.CRDTAMNT
    else 0
    end) YTD
 from GL11110 g
 inner join GL00105 a
    on g.ACTINDX = a.ACTINDX
 where g.YEAR1 = @year and g.PERIODID <= @month
 group by a.ACTNUMST) a --actuals open year
    on an.ACTNUMST = a.ACTNUMST

left outer join --actuals from historical year
(select a.ACTNUMST,
 sum(case when g.PERIODID = @month
    then g.DEBITAMT - g.CRDTAMNT
    else 0
    end) Monthly,
 sum(case when g.PERIODID <= @month
    then g.DEBITAMT - g.CRDTAMNT
    else 0
    end) YTD
 from GL11111 g
 inner join GL00105 a
    on g.ACTINDX = a.ACTINDX
 where g.YEAR1 = @year and g.PERIODID <= @month
 group by a.ACTNUMST) ah --actuals historical year
    on an.ACTNUMST = ah.ACTNUMST

left outer join --budgets
(select a.ACTNUMST,
 sum(case when b.PERIODID = @month
    then b.BUDGETAMT
    else 0
    end) Monthly,
 sum(case when b.PERIODID <= @month
    then b.BUDGETAMT
    else 0
    end) YTD
 from GL00201 b
 inner join GL00105 a
    on b.ACTINDX = a.ACTINDX
 where b.BUDGETID = @budget and b.YEAR1 = @year
 group by a.ACTNUMST) b --budgets
    on an.ACTNUMST = b.ACTNUMST

left outer join GL00100 m --account master
on an.ACTINDX = m.ACTINDX

--only show rows that are not all zeros,
--if you want to see all accounts,
--remove the where clause below
where a.Monthly <> 0
   or a.YTD <> 0
   or b.Monthly <> 0
   or b.YTD <> 0
   or ah.Monthly <> 0
   or ah.YTD <> 0

order by an.ACTNUMST

set nocount off

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

Dynamics GP, Excel reports, GL SQL code, GP Reports code, GP Reports Viewer, GP SQL scripts

Excel reports, General Ledger, GP Reports code, GP Reports Viewer, GP SQL stored procedure

SQL code to show top X percent of vendor data

Did you know you can change Void dates on Payables transactions?

20 Responses to “Refreshable Excel report for monthly actuals vs. budgets in Dynamics GP”

Thami Reply September 13, 2017 at 10:37 am

Thanks for the script, great stuff. Can you please assist on how can I use date range instead of @month. Or do I kill the case then add my date range in the where clause?

LikeLike
- Victoria Yudin Reply September 17, 2017 at 6:26 am
  
  Thami,
  
  The tables I am using in this code only show monthly amounts. You would need to convert your date range into the year and period/month to be able to use it with this code. Then you’d have to change all the formulas to accommodate that. In addition, if you want to cross years with your date range, the code would potentially need further changes.
  
  -Victoria
  
  LikeLike
Alicia Reply June 13, 2017 at 1:39 pm

Hi Victoria. Your website is Fantastic!!! For the refreshable excel reports, how do you change the fiscal year in excel? It defaults to 2014, but I would like to run the report for another year such as 2017.

LikeLike
- Victoria Yudin Reply June 14, 2017 at 2:32 am
  
  Hi Alicia,
  
  Thank you for your kind words!
  
  Are you talking about a refreshable report that you have, or one that I’ve posted? If one of your own, I am guessing that you have 2014 hardcoded somewhere. If you’re using the out-of-the-box GP functionality, you will have to change it to be hardcoded to 2017 or whatever year you want. Or to dynamically pick something based on the current date.
  
  If you’re using my method of using a stored procedure to generate the Excel report, you can make the year a parameter and have the user enter what they want. But then you would have to either run the report directly in Excel or use our product, GP Reports Viewer, to run it inside GP.
  
  -Victoria
  
  LikeLike
Taylor Reply February 8, 2017 at 5:04 pm

Thanks Victoria, for this great query. Could you tell me what additional permissions are required to run this query in Excel? I attempted to run it, but my permission was denied for the database that I’m attempting to query. Thanks for your help!

Taylor

LikeLike
- Victoria Yudin Reply February 9, 2017 at 5:23 am
  Taylor,
  
  If you’re doing this directly in Excel, you have 2 options:
  
  Create a SQL login that uses SQL Server Authentication can be shared by multiple people. When running the report, they will have to type in the user ID and password.
  
  Create a SQL login that uses the Windows Authentication for each individual user. When using the report, it will pass through the permissions from the Windows login.
  
  In either case, once you create the SQL login, you would need to give that SQL user permissions to the database and SQL objects needed for the report.
  
  Hope that helps.
  
  -Victoria
  
  LikeLike
Randy Lane Reply January 17, 2017 at 11:18 am

Victoria,
I tried running this query to create the procedure and I get errors:
Msg 4145, Level 15, State 1, Procedure gprv_actual_vs_budget, Line 36
An expression of non-boolean type specified in a context where a condition is expected, near ‘;’.
Msg 4145, Level 15, State 1, Procedure gprv_actual_vs_budget, Line 53
An expression of non-boolean type specified in a context where a condition is expected, near ‘;’.
Msg 4145, Level 15, State 1, Procedure gprv_actual_vs_budget, Line 70
An expression of non-boolean type specified in a context where a condition is expected, near ‘;’.

LikeLike
- Victoria Yudin Reply January 17, 2017 at 12:33 pm
  
  Hi Randy,
  
  Sorry about that. Periodically the blog software replaces the less than and greater than signs with weird characters. I believe I put them all back properly – can you please try it again?
  
  Thanks,
  -Victoria
  
  LikeLike
  - Randy Lane Reply January 17, 2017 at 3:43 pm
    
    Worked great!! Thanks.
    
    LikeLike
JimmyDarkmoon Reply August 17, 2016 at 2:41 pm

Vic, pic link not working (to see enlarged versions)…

LikeLike
- Victoria Yudin Reply August 17, 2016 at 2:46 pm
  
  Thanks for letting me know. I think I fixed it. 🙂
  
  -Victoria
  
  LikeLike
Steve Reply May 25, 2016 at 8:58 am

Victoria,

I use a variant of this for Financial and other reports based on the excellent examples you provide. Thanks so much for posting these gems of knowledge.
Steve

LikeLike
- Sunila Reply November 8, 2016 at 8:04 am
  
  Hi Victoria
  Appreciate if you could help me with an answer. I posted a Cash receipt transaction and applied it to the related sales invoice. Thereafter I voided the receipt without unapplying the invoice. Kindly let me know the implication of this.
  Thank You
  Joseph.
  
  LikeLike
  - Victoria Yudin Reply November 8, 2016 at 8:45 am
    
    Hi Joseph,
    
    Voiding a cash receipt will automatically unapply it from any invoices it was applied to.
    
    -Victoria
    
    LikeLike