SQL View to Return Budgets with Account User-Defined Fields

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

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Looking for support or consultancy with Microsoft Dynamics GP?

I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.

Leave a Reply

Your email address will not be published. Required fields are marked *