The last post was a SQL view to return purchases by vendor by fiscal year in an update of a prior script which only returned the information by the calendar year; I did a similar view at the time for the Sales series which also returned information for the calendar year. This post is an update of that one to return the information linking into the Financial Calendar setup to return the sales by customer by financial year.
IF OBJECT_ID (N'uv_AZRCRV_SalesByCustomerByYear', N'V') IS NOT NULL
DROP VIEW uv_AZRCRV_SalesByCustomerByYear
GO
CREATE VIEW uv_AZRCRV_SalesByCustomerByYear 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 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
['Receivables Transactions'].CUSTNMBR AS 'Customer ID'
,['RM Debtor MSTR'].CUSTNAME AS 'Customer Name'
,['RM Debtor MSTR'].CUSTCLAS AS 'Class ID'
,['Receivables Transactions'].DOCDATE AS 'Year'
,CASE WHEN ['RM Debtor MSTR'].INACTIVE = 1 THEN
'Inactive'
ELSE
'Active'
END AS 'Status'
,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].SLSAMNT ELSE ['Receivables Transactions'].SLSAMNT * -1 END) AS 'Sales Amount'
,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].TRDISAMT * -1 ELSE ['Receivables Transactions'].TRDISAMT END) AS 'Trade Discount'
,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].FRTAMNT ELSE ['Receivables Transactions'].FRTAMNT * -1 END) AS 'Freight'
,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].MISCAMNT ELSE ['Receivables Transactions'].MISCAMNT * -1 END) AS 'Miscalleneous'
,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].TAXAMNT ELSE ['Receivables Transactions'].TAXAMNT * -1 END) AS 'Tax Amount'
,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].DOCAMNT ELSE ['Receivables Transactions'].DOCAMNT * -1 END) AS 'Total Amount'
FROM
(SELECT
CUSTNMBR
,YEAR1 AS DOCDATE
,GLPOSTDT
,DOCNUMBR
,RMDTYPAL
,SLSAMNT
,TRDISAMT
,FRTAMNT
,MISCAMNT
,TAXAMNT
,SLSAMNT - TRDISAMT + FRTAMNT + MISCAMNT + TAXAMNT AS DOCAMNT
FROM
RM20101
INNER JOIN
SY40101
ON
DOCDATE BETWEEN FSTFSCDY AND LSTFSCDY
WHERE
VOIDSTTS >= 0
AND
RMDTYPAL IN (1,3,4,7,8)
UNION ALL
SELECT
CUSTNMBR
,YEAR1 AS DOCDATE
,GLPOSTDT
,DOCNUMBR
,RMDTYPAL
,SLSAMNT
,TRDISAMT
,FRTAMNT
,MISCAMNT
,TAXAMNT
,SLSAMNT - TRDISAMT + FRTAMNT + MISCAMNT + TAXAMNT AS DOCAMNT
FROM
RM30101
INNER JOIN
SY40101
ON
DOCDATE BETWEEN FSTFSCDY AND LSTFSCDY
WHERE
VOIDSTTS >= 0
AND
RMDTYPAL IN (1,3,4,7,8)
) AS ['Receivables Transactions']
LEFT JOIN
RM00101 AS ['RM Debtor MSTR']
ON
['Receivables Transactions'].CUSTNMBR = ['RM Debtor MSTR'].CUSTNMBR
GROUP BY
['Receivables Transactions'].CUSTNMBR
,['RM Debtor MSTR'].CUSTNAME
,['RM Debtor MSTR'].CUSTCLAS
,['RM Debtor MSTR'].INACTIVE
,['Receivables Transactions'].DOCDATE
GO
GRANT SELECT ON uv_AZRCRV_SalesByCustomerByYear TO DYNGRP
GO
The view can easily be plugged into SmartList Designer, SmartList Builder, a refreshable Excel Report, a SQL Server Reporting Services report or any other type of reporting tool.
