Day: April 1, 2021

SQL View to Return Budgets with Account User-Defined Fields

Ian Grieve5 minute read0 comment

Microsoft Dynamics 365 Business CentralI was helping a client create a budget report recently where they wanted to have the same information available in more than one reporting too. While queries could be written and embedded there is scope for them to then diverge over time; the solution to this is to create a SQL view which all of the reporting tools can then select to make sure they always have the same data.

The view uses data from the following tables:

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_Budgets', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_Budgets
GO
-- create view
CREATE VIEW uv_AZRCRV_Budgets AS
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	['Budget Master'].BUDGETID
	,['Budget Master'].YEAR1
	,['Budget Summary Master'].PERIODID
	,['Account Master'].ACTINDX
	,['Account Master'].ACTNUMBR_1
	,['Account Master'].ACTNUMBR_2
	,['Account Master'].ACTNUMBR_3
	,['Account Master'].ACTNUMBR_4
	,['Account Master'].ACTNUMBR_5
	,['Account Master'].ACTNUMBR_6
	,['Account Master'].ACTNUMBR_7
	,['Account Master'].ACTNUMBR_8
	,['Account Master'].ACTNUMBR_9
	,['Account Master'].ACTNUMBR_10
	,['Account Index Master'].ACTNUMST
	,['Account Master'].ACTDESCR
	,['Account Category Master'].ACCATDSC
	,['Account Master'].USERDEF1
	,['Account Master'].USERDEF2
	,['Account Master'].USRDEFS1
	,['Account Master'].USRDEFS2
	,['Budget Summary Master'].BUDGETAMT
FROM
	GL00200 AS ['Budget Master']
INNER JOIN
	GL00201 AS ['Budget Summary Master']
		ON ['Budget Summary Master'].BUDGETID = ['Budget Master'].BUDGETID
INNER JOIN
	GL00100 AS ['Account Master']
		ON
			['Account Master'].ACTINDX = ['Budget Summary Master'].ACTINDX
INNER JOIN
	GL00105 AS ['Account Index Master']
		ON
			['Account Index Master'].ACTINDX = ['Budget Summary Master'].ACTINDX
INNER JOIN
	GL00102 AS ['Account Category Master']
		ON
			['Account Category Master'].ACCATNUM = ['Account Master'].ACCATNUM
GO

GRANT SELECT ON uv_AZRCRV_Budgets TO DYNGRP
GO