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


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):

actual-budget1

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:

  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_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.

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

  1. 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

    Like

    • Taylor,

      If you’re doing this directly in Excel, you have 2 options:

      1. 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.
      2. 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

      Like

  2. 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 ‘;’.

    Like

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

    Like

  4. 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

    Like

    • 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.

      Like

Trackbacks/Pingbacks

  1. Refreshable Excel report for monthly actuals vs. budgets in Dynamics GP | Victoria Yudin - Microsoft Dynamics GP Community - May 31, 2016

    […] Victoria Yudin addresses Refreshable Excel report for monthly actuals vs. budgets in Dynamics GP  […]

    Like

  2. Refreshable Excel report for monthly actuals vs. budgets in Dynamics GP | Victoria Yudin - DynamicAccounting.net - Dynamics GP Users - May 31, 2016

    […] Victoria Yudin addresses Refreshable Excel report for monthly actuals vs. budgets in Dynamics GP  […]

    Like

  3. DynamicAccounting.net | Refreshable Excel report for monthly actuals vs. budgets in Dynamics GP | Victoria YudinRefreshable Excel report for monthly actuals vs. budgets in Dynamics GP | Victoria Yudin - DynamicAccounting.net - May 31, 2016

    […] Victoria Yudin addresses Refreshable Excel report for monthly actuals vs. budgets in Dynamics GP  […]

    Like

  4. Refreshable Excel report for monthly actuals vs. budgets in Dynamics GP - Microsoft Dynamics GP Community - May 27, 2016

    […] Source: Refreshable Excel report for monthly actuals vs. budgets in Dynamics GP […]

    Like

  5. Refreshable Excel report for monthly actuals vs. budgets in Dynamics GP | The Dynamics GP Geek blog - May 27, 2016

    […] Source: Refreshable Excel report for monthly actuals vs. budgets in Dynamics GP […]

    Like

  6. Refreshable Excel report for monthly actuals vs. budgets in Dynamics GP – May 25, Victoria Yudin, #MSDynGP - May 25, 2016

    […] Continued on Source: Refreshable Excel report for monthly actuals vs. budgets in Dynamics GP | Victoria Yudin […]

    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: