I did a post a few weeks ago which included a SQL view which could be used to return a list of purchases by creditors/vendors by year. I wrote the view for a client who operates a financial year which is the same as the calendar year.
However, most of my clients use different financial years, so I have also created a script which returns the same information, but links to the Financial Calendar to determine which financial, rather than calendar, year a transaction is within:
IF OBJECT_ID (N'uv_AZRCRV_PurchasesByVendorByYear', N'V') IS NOT NULL
DROP VIEW uv_AZRCRV_PurchasesByVendorByYear
GO
CREATE VIEW uv_AZRCRV_PurchasesByVendorByYear 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
['Payables Transactions'].VENDORID AS 'Vendor ID'
,['PM Vendor Master'].VENDNAME AS 'Vendor Name'
,['PM Vendor Master'].VNDCLSID AS 'Class ID'
,['Payables Transactions'].DOCDATE AS 'Year'
,CASE WHEN ['PM Vendor Master'].VENDSTTS >= 1 THEN
'Active'
WHEN ['PM Vendor Master'].VENDSTTS >= 2 THEN
'Inctive'
ELSE
'Temporary'
END AS 'Status'
,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].PRCHAMNT ELSE ['Payables Transactions'].PRCHAMNT * -1 END) AS 'Purchases Amount'
,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].TRDISAMT * -1 ELSE ['Payables Transactions'].TRDISAMT END) AS 'Trade Discount'
,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].FRTAMNT ELSE ['Payables Transactions'].FRTAMNT * -1 END) AS 'Freight'
,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].MSCCHAMT ELSE ['Payables Transactions'].MSCCHAMT * -1 END) AS 'Miscalleneous'
,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].TAXAMNT ELSE ['Payables Transactions'].TAXAMNT * -1 END) AS 'Tax Amount'
,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].DOCAMNT ELSE ['Payables Transactions'].DOCAMNT * -1 END) AS 'Total Amount'
FROM
(SELECT
['PM Transaction OPEN File'].VENDORID
,['Period Header'].YEAR1 AS DOCDATE
,['PM Transaction OPEN File'].PSTGDATE
,['PM Transaction OPEN File'].DOCNUMBR
,['PM Transaction OPEN File'].DOCTYPE
,['PM Transaction OPEN File'].PRCHAMNT
,['PM Transaction OPEN File'].TRDISAMT
,['PM Transaction OPEN File'].FRTAMNT
,['PM Transaction OPEN File'].MSCCHAMT
,['PM Transaction OPEN File'].TAXAMNT
,['PM Transaction OPEN File'].DOCAMNT
FROM
PM20000 AS ['PM Transaction OPEN File']
INNER JOIN
SY40101 AS ['Period Header']
ON
['PM Transaction OPEN File'].DOCDATE BETWEEN ['Period Header'].FSTFSCDY AND ['Period Header'].LSTFSCDY
WHERE
VOIDED >= 0
AND
DOCTYPE <= 5
UNION ALL
SELECT
['PM Paid Transaction History File'].VENDORID
,['Period Header'].YEAR1 AS DOCDATE
,['PM Paid Transaction History File'].PSTGDATE
,['PM Paid Transaction History File'].DOCNUMBR
,['PM Paid Transaction History File'].DOCTYPE
,['PM Paid Transaction History File'].PRCHAMNT
,['PM Paid Transaction History File'].TRDISAMT
,['PM Paid Transaction History File'].FRTAMNT
,['PM Paid Transaction History File'].MSCCHAMT
,['PM Paid Transaction History File'].TAXAMNT
,['PM Paid Transaction History File'].DOCAMNT
FROM
PM30200 AS ['PM Paid Transaction History File']
INNER JOIN
SY40101 AS ['Period Header']
ON
['PM Paid Transaction History File'].DOCDATE BETWEEN ['Period Header'].FSTFSCDY AND ['Period Header'].LSTFSCDY
WHERE
VOIDED >= 0
AND
DOCTYPE <= 5
) AS ['Payables Transactions']
LEFT JOIN
PM00200 AS ['PM Vendor Master']
ON
['Payables Transactions'].VENDORID = ['PM Vendor Master'].VENDORID
GROUP BY
['Payables Transactions'].VENDORID
,['PM Vendor Master'].VENDNAME
,['PM Vendor Master'].VNDCLSID
,['PM Vendor Master'].VENDSTTS
,['Payables Transactions'].DOCDATE
GO
GRANT SELECT ON uv_AZRCRV_PurchasesByVendorByYear 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.
UPDATE: Removed Format on DOCDATE. Thanks to Tim Wappat for pointing out the error.