azurecurve

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script contains a SQL view which reports on sales transactions from one year compared against the prior year; it returns the number of transactions, number of items, costs, sales price and profit margins.

-- drop view if it exists
IF OBJECT_ID (N'uv_AZRCRV_SalesReportWithYearPriorComparison', N'V') IS NOT NULL
    DROP VIEW uv_AZRCRV_SalesReportWithYearPriorComparison
GO
-- create view
CREATE VIEW uv_AZRCRV_SalesReportWithYearPriorComparison 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).
*/
WITH cteSalesReportData AS
	(
		SELECT
			FORMAT(['Sales Transaction History'].DOCDATE, 'yyyyMM') as YearMth
			,['Item Master'].ITEMNMBR
			,['Item Master'].ITEMDESC
			,['Item Master'].ITMCLSCD
			,['Sales Transaction Amounts History'].LOCNCODE
			,SUM(
					CASE WHEN ['Sales Transaction Amounts History'].SOPTYPE = 3 THEN
						['Sales Transaction Amounts History'].XTNDPRCE
					ELSE
						['Sales Transaction Amounts History'].XTNDPRCE * -1
					END
			) AS 'Sales In Period'
			,SUM(
					CASE WHEN ['Sales Transaction Amounts History'].SOPTYPE = 3 THEN
						['Sales Transaction Amounts History'].EXTDCOST
					ELSE
						['Sales Transaction Amounts History'].EXTDCOST * -1
					END
			) AS 'COGS In Period'
			,SUM(['Sales Transaction Amounts History'].MARGINPERC) / SUM(['Sales Transaction Amounts History'].NUMBER) AS 'Margin %'
			,SUM(['Sales Transaction Amounts History'].QTYTOINV) AS 'Quantity'
			,COUNT(['Sales Transaction History'].SOPNUMBE) AS 'TrxCount'
		FROM
			IV00101 AS ['Item Master']
		LEFT JOIN
			(
				SELECT
					SOPNUMBE
					,SOPTYPE
					,LOCNCODE
					,ITEMNMBR
					,CMPNTSEQ
					,XTNDPRCE
					,EXTDCOST
					,CASE WHEN EXTDCOST = 0 THEN 100 ELSE (XTNDPRCE / EXTDCOST) * 100 END AS MARGINPERC
					,QTYTOINV
					,1 AS NUMBER
				FROM
					SOP10200
				UNION ALL
					SELECT
						SOPNUMBE
						,SOPTYPE
						,LOCNCODE
						,ITEMNMBR
						,CMPNTSEQ
						,XTNDPRCE
						,EXTDCOST
						,CASE WHEN EXTDCOST = 0 THEN 100 ELSE (XTNDPRCE / EXTDCOST) * 100 END AS MARGINPERC
						,QTYTOINV
						,1 AS NUMBER
					FROM
						SOP30300
			) AS ['Sales Transaction Amounts History']
				ON
					['Sales Transaction Amounts History'].ITEMNMBR = ['Item Master'].ITEMNMBR
				AND
					['Sales Transaction Amounts History'].CMPNTSEQ = 0
				AND
					['Sales Transaction Amounts History'].SOPTYPE IN (3,4)
		LEFT JOIN
			(
				SELECT
					SOPNUMBE
					,SOPTYPE
					,DOCID
					,DOCDATE
				FROM
					SOP10100
				UNION ALL
					SELECT
						SOPNUMBE
						,SOPTYPE
						,DOCID
						,DOCDATE
					FROM
						SOP30200
			) AS ['Sales Transaction History']
				ON
					['Sales Transaction History'].SOPNUMBE = ['Sales Transaction Amounts History'].SOPNUMBE
				AND
					['Sales Transaction History'].SOPTYPE = ['Sales Transaction Amounts History'].SOPTYPE
		GROUP BY
			FORMAT(['Sales Transaction History'].DOCDATE, 'yyyyMM')
			,['Item Master'].ITEMNMBR
			,['Item Master'].ITEMDESC
			,['Item Master'].ITMCLSCD
			,['Sales Transaction Amounts History'].LOCNCODE
	)

SELECT
	DB_NAME() AS 'Company'
	,['This Year'].LOCNCODE AS 'Site'
	,['This Year'].YearMth
	,['This Year'].ITEMNMBR AS 'Item Number'
	,['This Year'].ITEMDESC AS 'Item Description'
	,['This Year'].ITMCLSCD AS 'Item Class'
	,ISNULL(['This Year'].TrxCount, 0) AS 'Trx TY'
	,ISNULL(['Last Year'].TrxCount, 0) AS 'Trx LY'
	,ISNULL(['This Year'].Quantity, 0) AS 'Qty of Item TY'
	,ISNULL(['Last Year'].Quantity, 0) AS 'Qty of Item LY'
	,ISNULL(['This Year'].[Sales In Period], 0) AS 'Sales This Year'
	,ISNULL(['Last Year'].[Sales In Period], 0) AS 'Sales Last Year'
	,ISNULL(['This Year'].[COGS In Period], 0) AS 'Cost This Year'
	,ISNULL(['Last Year'].[COGS In Period], 0) AS 'Cost Last Year'
	,ISNULL(['This Year'].[Sales In Period], 0) - ISNULL(['This Year'].[COGS In Period], 0) AS 'Margin This Year'
	,ISNULL(['Last Year'].[Sales In Period], 0) - ISNULL(['Last Year'].[COGS In Period], 0) AS 'Margin Last Year'
	,CASE WHEN ISNULL(['This Year'].[COGS In Period], 0) = 0 THEN
		0
	ELSE
		(ISNULL(['This Year'].[Sales In Period], 0) / ISNULL(['This Year'].[COGS In Period], 0)) * 100 
	END AS 'Margin % This Year'
	,CASE WHEN ISNULL(['Last Year'].[COGS In Period], 0) = 0 THEN
		0
	ELSE
		(ISNULL(['Last Year'].[Sales In Period], 0) / ISNULL(['Last Year'].[COGS In Period], 0)) * 100 
	END AS 'Margin % Last Year'
FROM
	cteSalesReportData AS ['This Year']
LEFT JOIN
	cteSalesReportData AS ['Last Year']
		ON
			['Last Year'].ITEMNMBR = ['This Year'].ITEMNMBR
		AND
			['Last Year'].YearMth = ['This Year'].YearMth - 100 -- subtract 100 from 202003 to get March last year
		AND
			['Last Year'].LOCNCODE = ['This Year'].LOCNCODE
WHERE
	['This Year'].YearMth IS NOT NULL
UNION
	SELECT
		DB_NAME() AS 'Company'
		,['Last Year'].LOCNCODE AS 'Site'
		,['Last Year'].YearMth + 100 AS YearMth
		,['Last Year'].ITEMNMBR AS 'Item Number'
		,['Last Year'].ITEMDESC AS 'Item Description'
		,['Last Year'].ITMCLSCD AS 'Item Class'
		,ISNULL(['This Year'].TrxCount, 0) AS 'Trx TY'
		,ISNULL(['Last Year'].TrxCount, 0) AS 'Trx LY'
		,ISNULL(['This Year'].Quantity, 0) AS 'Qty of Item TY'
		,ISNULL(['Last Year'].Quantity, 0) AS 'Qty of Item LY'
		,ISNULL(['This Year'].[Sales In Period], 0) AS 'Sales This Year'
		,ISNULL(['Last Year'].[Sales In Period], 0) AS 'Sales Last Year'
		,ISNULL(['This Year'].[COGS In Period], 0) AS 'Cost This Year'
		,ISNULL(['Last Year'].[COGS In Period], 0) AS 'Cost Last Year'
		,ISNULL(['This Year'].[Sales In Period], 0) - ISNULL(['This Year'].[COGS In Period], 0) AS 'Margin This Year'
		,ISNULL(['Last Year'].[Sales In Period], 0) - ISNULL(['Last Year'].[COGS In Period], 0) AS 'Margin Last Year'
		,CASE WHEN ISNULL(['This Year'].[COGS In Period], 0) = 0 THEN
			0
		ELSE
			(ISNULL(['This Year'].[Sales In Period], 0) / ISNULL(['This Year'].[COGS In Period], 0)) * 100 
		END AS 'Margin % This Year'
		,CASE WHEN ISNULL(['Last Year'].[COGS In Period], 0) = 0 THEN
			0
		ELSE
			(ISNULL(['Last Year'].[Sales In Period], 0) / ISNULL(['Last Year'].[COGS In Period], 0)) * 100 
		END AS 'Margin % Last Year'
	FROM
		cteSalesReportData AS ['Last Year']
	LEFT JOIN
		cteSalesReportData AS ['This Year']
			ON
				['Last Year'].ITEMNMBR = ['This Year'].ITEMNMBR
			AND
				['Last Year'].YearMth = ['This Year'].YearMth -100 -- subtract 100 from 202003 to get March last year
			AND
				['Last Year'].LOCNCODE = ['This Year'].LOCNCODE
	WHERE
		['This Year'].YearMth IS NULL
	AND
		['Last Year'].YearMth <= FORMAT(GETDATE(), 'yyyyMM') - 100
GO

-- grant select permissions to DYNGRP
GRANT SELECT ON uv_AZRCRV_SalesReportWithYearPriorComparison TO DYNGRP
GO

Click to show/hide the Series Index


New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1Specify Dimensions on Posted Entries Applications With Realized Gains or Losses
New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1Specify Dimensions on Posted Entries Applications With Realized Gains or Losses
New Functionality In Microsoft Dynamics 365 Business Central 2024 Wave 1Specify Dimensions on Posted Entries Applications With Realized Gains or Losses

This post is part of the In Microsoft Dynamics 365 Business Central (Financial), how do I… series and of the wider In Microsoft Dynamics 365 Business Central, how do I… series which I am posting as I familiarise myself with Microsoft Dynamics 365 Business Central.

Now that we’ve introduced the chart of accounts we can take a look at creating a new G/L account.

There is a few considerations when creating a new account:

The No is important as this will control where the account shows in the char of accounts and some of the reports.
Expansion space should be allowed in the chart of accounts numbering, to allow for new accounts to be created in the future and have them appear in the correct place.
The No does not have to be all numeric, it can be a mix of alpha and numeric characters.

New accounts, regardless of type, are created from the Chart of Account list page by clicking on the New button to open the G/l Account Card window: