SQL view to show monthly totals for Dynamics GP Vendors


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.

14 Responses to “SQL view to show monthly totals for Dynamics GP Vendors”

  1. 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?

    Like

    • 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

      Like

      • 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!!

        Like

  2. Does this include voided payments?

    Like

  3. 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 ??

    Like

    • 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

      Like

  4. 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?

    Like

    • 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

      Like

Trackbacks/Pingbacks

  1. DynamicAccounting.net | SQL view to show monthly totals for Dynamics GP Vendors | Victoria YudinSQL view to show monthly totals for Dynamics GP Vendors | Victoria Yudin - DynamicAccounting.net - May 4, 2016

    […] Victoria Yudin has a new SQL view to show monthly totals for Dynamics GP Vendors […]

    Like

  2. Interesting Findings & Knowledge Sharing » Everything Dynamics GP #49 - December 12, 2011

    […] SQL view to show monthly totals for Dynamics GP Vendors […]

    Like

  3. SQL view to show monthly totals for Dynamics GP Vendors | Victoria Yudin - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community - December 12, 2011

    […] Comments 0 Victoria Yudin has a new SQL view to show monthly totals for Dynamics GP Vendors […]

    Like

  4. Everything Dynamics GP #49 - About Dynamics, Development and Life - GP Technical Blogs - Microsoft Dynamics Community - December 12, 2011

    […] SQL view to show monthly totals for Dynamics GP Vendors […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: