December 9, 2011 
In response to a reader request, I have created this new view based on my Vendor Yearly Totals view that will show monthly totals for your Dynamics GP Vendors for all years.
This view will show both the numbers and names of the months and assumes that you have 12 periods corresponding to the calendar months. To get results for a particular year you can run the following query against this view after creating it:
select * from view_Vendor_Monthly_Totals
where [Year] = 2011 --change the year as desired
For more Dynamics GP Payables code, take a look at my Payables SQL Views page. Or check out my GP Reports page for views in other modules and additional report writing and coding tips.
CREATE VIEW view_Vendor_Monthly_Totals
AS
-- ***************************************************************
-- view_Vendor_Monthly_Totals
-- Created Dec 9, 2011 by Victoria Yudin - Flexible Solutions Inc
-- For updates please see https://victoriayudin.com/gp-reports/
-- Shows totals for all AP vendors per month and year
-- Assumes periods are calendar months
-- 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.PERIODID Period,
datename(month, DATEADD(month, VT.PERIODID, -1 )) [Month],
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.PERIODID, VT.YEAR1, VM.ADDRESS1,
VM.ADDRESS2, VM.ADDRESS3, VM.CITY, VM.[STATE], VM.ZIPCODE,
VM.COUNTRY, VM.TXIDNMBR
GO
GRANT SELECT ON view_Vendor_Monthly_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 
Replying to an old post, so I dont know if anyone will see this.
I have a question on one field in the PM00202 table that I have not been able to find elsewhere so far.
What is HISTTYPE, I know it is either 0 or 1, but there is no definiation as to what 0 or 1 means. I assumed it was open vs. closed year but I dont think that is correct.
We outsource our 1099’s and was filtering HISTTYPE = 1, but some of the amounts were only on HISTTYPE 0.
Appreciate the feedback 🙂
LikeLike
Hi Barak,
HISTTYPE usually signifies Calendar Year (value 0) or Fiscal Year (value 1). Depending on your settings for various things you might be keeping history for one and not the other. Also, if your fiscal year is not the calendar year you will see different values in these. Hope that helps.
-Victoria
LikeLike
Victoria,
Thank you so much for your response. Totally makes sense. Much appteciated!
-Barak 🙂
LikeLike
Is there a view similar to this that breaks it out by column? Similar to your Sales Qty by Item Month?
LikeLike
Hi Luke,
It’s a little difficult to turn this one into columns because of the number of different amounts it shows. Here is an example of yearly columns for just invoices and payments:
https://victoriayudin.com/2012/01/04/sql-view-for-vendor-yearly-totals-in-dynamics-gp/
Hopefully that will show you how you can do this by month, as well. Let me know if you need more help.
-Victoria
LikeLike
Hi! It seems that the PERIODID used in the PM00202 table refer to the Document Date. Is there any way to reference the Posted Date?
LikeLike
Hi Michael,
Not using the summary tables. You would have to write your code using the detail data from tables PM20000 and PM30200. Then you can base the logic on any of the dates available. One comment, if I may – POSTED date is the actual date when someone clicked the Post button. GL POSTING Date is the date the transaction appears in the General Ledger. I typically see requests for the GL Posting date, not the Posted date, so I just wanted to mention that.
-Victoria
LikeLike
Thanks so much, Victoria! I’ll see what I can do with the master/detail tables, and I’ll reach out to the finance team to confirm if they want the posted or posting date. Really appreciate the quick response and the great site!!
LikeLike
Does this include voided payments?
LikeLike
Aimee,
No, voided transactions are not included in any totals.
-Victoria
LikeLike
Great post !
how can i get DAILY totals added to this view ?.. there are Monthls and Years added , but I would like to add ‘days’ ( dates 1-31 ) as well.. .. any suggestions ??
LikeLike
es,
You would not be able to add days with this view, as it is using a summary table that only holds the monthly summary amounts. You’d have to use the actual transaction tables to do reporting by days.
-Victoria
LikeLike
Victoria – thank you for all the tips you post! Here is a question that I am frequently asked by clients. They would like a Smartlist Builder object which shows Current Payroll 401K deduction amount (based on a check date range they enter), and in the same report they need YTD Gross Pay and YTD Hours. The YTD Gross Pay we can get by using a calculated field that pulls from the Summary tables (there is a field for each Month’s Gross Pay) – so this is materially accurate… it allows pulling the YTD as long as the date range we are looking for is within a calendar year, and includes all wages within each month in the date range. However, the YTD Hours are not in the summary table. Is there a user-friendly way to do this?
LikeLike
Hi Sherlene,
If the YTD numbers need to be based on the date range entered, there is really no way to do this in SmartList, because while it allows for filters (or restrictions), it does not allow for parameters, which is what you need to accomplish this type of report. I have done a lot of similar type of reporting with SRS and Crystal, and even Excel, but in Excel it would need to be based on a stored procedure to allow for a parameter like this.
-Victoria
LikeLike
ok, thanks for the reply. Just wanted to be sure I wasn’t missing something.
LikeLike