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 http://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.
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?
Hi Laura,
Please take a look at my new post with a view for Vendor Monthly Totals.
-Victoria
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
Victoria,
Nevermind, I figured out that I was pointing to the wrong database/location.
Regards,
Gordon
Gordon,
Great, thanks for letting me know.
-Victoria