July 15, 2010 
Below is a view that will show yearly totals for your Dynamics GP Vendors. It’s something we have used internally for a while, but I have recently gotten a few requests for it, so I cleaned it up and am sharing it.
This will show yearly totals for calendar years. If you want to use fiscal years instead, change the WHERE clause on line 55 below to the following:
WHERE VT.HISTTYPE = 1
CREATE VIEW view_Vendor_Yearly_Totals
AS
/***************************************************************
view_Vendor_Yearly_Totals
Shows totals for all AP vendors
Created Jul 15, 2010 by Victoria Yudin - Flexible Solutions, Inc.
For updates see https://victoriayudin.com/gp-reports/
Results shown for calendar months and functionaly currency.
***************************************************************/
SELECT VT.VENDORID Vendor_ID,
VM.VENDNAME Vendor_Name,
VM.VNDCLSID Class_ID,
case VM.VENDSTTS
when 1 then 'Active'
when 2 then 'Inactive'
when 3 then 'Temporary'
end Vendor_Status,
case VM.TEN99TYPE
when 1 then 'Not a 1099 Vendor'
when 2 then 'Dividend'
when 3 then 'Interest'
when 4 then 'Miscellaneous'
end [1099_Type],
VM.PYMTRMID Payment_Terms_ID,
VT.YEAR1 [Year],
sum(VT.AMBLDLIF) Amount_Billed,
sum(VT.AMTPDLIF) Amount_Paid,
sum(VT.TEN99ALIF) [1099_Amount],
sum(VT.FINCHLIF) Finance_Charges,
sum(VT.WROFSLIF) Writeoffs,
sum(VT.RTRNSLIF) [Returns],
sum(VT.TRDTKLIF) Trade_Discounts,
sum(VT.DISAVLIF) Term_Discounts_Avail,
sum(VT.DISTKNLF) Term_Discounts_Taken,
sum(VT.DISLSTLF) Term_Discounts_Lost,
sum(VT.Withholding_LIFE) Withholding,
sum(VT.NOINVLIF) Num_Of_Invoices,
sum(VT.NFNCHLIF) Num_Of_Finance_Charges,
VM.ADDRESS1 Address_1,
VM.ADDRESS2 Address_2,
VM.ADDRESS3 Address_3,
VM.CITY City,
VM.[STATE] [State],
VM.ZIPCODE Zip_Code,
VM.COUNTRY Country,
VM.TXIDNMBR Tax_ID
FROM PM00202 VT
INNER JOIN PM00200 VM
ON VT.VENDORID = VM.VENDORID
WHERE VT.HISTTYPE = 0
GROUP BY VT.VENDORID, VM.VENDNAME, VM.VNDCLSID,
VM.VENDSTTS, VM.TEN99TYPE, VM.PYMTRMID,
VT.YEAR1, VM.ADDRESS1, VM.ADDRESS2,
VM.ADDRESS3, VM.CITY, VM.[STATE],
VM.ZIPCODE, VM.COUNTRY, VM.TXIDNMBR
/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/
GO
GRANT SELECT ON view_Vendor_Yearly_Totals TO DYNGRP
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.
Victoria Yudin 
Do you have a report similar to this where you could enter a year and it would show you the amount per month for all vendors?
LikeLike
Hi Laura,
Please take a look at my new post with a view for Vendor Monthly Totals.
-Victoria
LikeLike
Hi Victoria:
this seems to be interesting and helpful however how should we use this in GP reprot writer as it creates report only through tables and not views.
LikeLike
Bhavana,
Frankly, I would not use GP’s Report Writer for reports if I did not have to. (Sorry David, if you are reading this.) My views are typically designed to work with SmartList Builder, Crystal Reports or SQL Server Reporting Services. My goal is to show examples of how data in GP can be accessed, or a starting point for reporting, not necessarily deliver the finished product.
-Victoria
LikeLike
Hi Victoria,
Just copied and pasted this View and when I set up the view for one company and ran it to see the results, it worked. Then I added the other companies and ran it to see the results, it worked.
However, when I went into Crystal to set up the report, I got the following message:
Failed to open a rowset.
Details: 42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ‘SQL’.
Any idea why this is happening and how to resolve it?
Thanks and regards,
Gordon
LikeLike
Victoria,
Nevermind, I figured out that I was pointing to the wrong database/location.
Regards,
Gordon
LikeLike
Gordon,
Great, thanks for letting me know.
-Victoria
LikeLike