SQL Scripts for Microsoft Dynamics GP: Sales Report With Year Prior Comparison

Microsoft Dynamics GPThis 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

In Microsoft Dynamics 365 Business Central (Financial), how do I… Create a G/L Account

Microsoft Dynamics 365 Business CentralThis 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:

  1. The No is important as this will control where the account shows in the char of accounts and some of the reports.
  2. 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.
  3. 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:

G/L Account Card

Continue reading “In Microsoft Dynamics 365 Business Central (Financial), how do I… Create a G/L Account”