SQL View to Return Budgets By Month

The budget functionality in Microsoft Dynamics GP isn’t the strongest with reporting being particularly weak. The ability to report on budgets in Management Reporter does somewhat redeem this area of functionality.

However, the absence of a SmartList Object for budgets is quite a big issue, as SmartList is a very nice flexible reporting tool which the majority of my clients know well. For those with SmartList Builder, it was easy enough to create a SmartList Object for them.

With the introduction of SmartList Designer, we were able to roll out the SmartList budget report to all of the clients who wanted it.

The script is below and returns the budget information with the beginning balance, 12 hard-coded periods and total horizontally across the page.

-- drop stored proc if it exists
IF OBJECT_ID (N'uv_AZRCRV_BudgetByMonth', N'V') IS NOT NULL
    DROP VIEW uv_AZRCRV_BudgetByMonth
GO
-- create view
CREATE VIEW uv_AZRCRV_BudgetByMonth AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
SELECT
	['Budget Summary Master'].BUDGETID AS 'Budget ID'
	,['Budget Master'].BUDCOMNT AS 'Budget Description'
	,['Budget Master'].YEAR1 AS 'Budget Year'
	,['Account Index Master'].ACTNUMST AS 'Account'
	,['Account Master'].ACTDESCR AS 'Account Description'
	,['Account Category Master'].ACCATDSC AS 'Account Category Description'
	,SUM(CASE WHEN ['Budget Summary Master'].PERIODID = 0 THEN BUDGETAMT ELSE 0 END) AS 'Beginning Balance'
	,SUM(CASE WHEN ['Budget Summary Master'].PERIODID = 1 THEN BUDGETAMT ELSE 0 END) AS 'Period 1'
	,SUM(CASE WHEN ['Budget Summary Master'].PERIODID = 2 THEN BUDGETAMT ELSE 0 END) AS 'Period 2'
	,SUM(CASE WHEN ['Budget Summary Master'].PERIODID = 3 THEN BUDGETAMT ELSE 0 END) AS 'Period 3'
	,SUM(CASE WHEN ['Budget Summary Master'].PERIODID = 4 THEN BUDGETAMT ELSE 0 END) AS 'Period 4'
	,SUM(CASE WHEN ['Budget Summary Master'].PERIODID = 5 THEN BUDGETAMT ELSE 0 END) AS 'Period 5'
	,SUM(CASE WHEN ['Budget Summary Master'].PERIODID = 6 THEN BUDGETAMT ELSE 0 END) AS 'Period 6'
	,SUM(CASE WHEN ['Budget Summary Master'].PERIODID = 7 THEN BUDGETAMT ELSE 0 END) AS 'Period 7'
	,SUM(CASE WHEN ['Budget Summary Master'].PERIODID = 8 THEN BUDGETAMT ELSE 0 END) AS 'Period 8'
	,SUM(CASE WHEN ['Budget Summary Master'].PERIODID = 9 THEN BUDGETAMT ELSE 0 END) AS 'Period 9'
	,SUM(CASE WHEN ['Budget Summary Master'].PERIODID = 10 THEN BUDGETAMT ELSE 0 END) AS 'Period 10'
	,SUM(CASE WHEN ['Budget Summary Master'].PERIODID = 11 THEN BUDGETAMT ELSE 0 END) AS 'Period 11'
	,SUM(CASE WHEN ['Budget Summary Master'].PERIODID = 12 THEN BUDGETAMT ELSE 0 END) AS 'Period 12'
	,SUM(['Budget Summary Master'].BUDGETAMT) AS 'Total'
FROM
	GL00201 AS ['Budget Summary Master']
INNER JOIN
	GL00200 AS ['Budget Master']
		ON
			['Budget Master'].BUDGETID = ['Budget Summary Master'].BUDGETID
INNER JOIN
	GL00105 AS ['Account Index Master']
		ON ['Account Index Master'].ACTINDX = ['Budget Summary Master'].ACTINDX
INNER JOIN
	GL00100 AS ['Account Master']
		ON ['Account Master'].ACTINDX = ['Account Index Master'].ACTINDX
INNER JOIN
	GL00102 AS ['Account Category Master']
		ON ['Account Category Master'].ACCATNUM = ['Account Master'].ACCATNUM
GROUP BY
	['Budget Summary Master'].BUDGETID
	,['Budget Master'].BUDCOMNT
	,['Budget Master'].YEAR1
	,['Account Index Master'].ACTNUMST
	,['Account Master'].ACTDESCR
	,['Account Category Master'].ACCATDSC
GO

-- grant execute permission on stored proc to DYNGRP
GRANT SELECT ON uv_AZRCRV_BudgetByMonth TO DYNGRP
GO

-- execute stored proc
SELECT * FROM uv_AZRCRV_BudgetByMonth
GO

If you have more than twelve periods, you can add additional SQL code before the total row.

UPDATED 2020-05-26 to add Budget Year column.