SQL view to show yearly totals for Dynamics GP Vendors


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.

15 Responses to “SQL view to show yearly totals for Dynamics GP Vendors”

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

    Like

    • Hi Laura,

      Please take a look at my new post with a view for Vendor Monthly Totals.

      -Victoria

      Like

    • Bhavana Khanchandani Reply March 19, 2012 at 2:14 pm

      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.

      Like

      • 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

        Like

  2. 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

    Like

Trackbacks/Pingbacks

  1. DynamicAccounting.net | SQL view to show yearly totals for Dynamics GP VendorsSQL view to show yearly totals for Dynamics GP Vendors - DynamicAccounting.net - April 27, 2016

    […] is showing off a new SQL view to show yearly totals for Dynamics GP Vendors.  I’ve added a link to my SQL Scripts […]

    Like

  2. SQL view for vendor yearly totals in Dynamics GP - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - January 4, 2012

    […] Payables – you now have 2 choices.  I previously published this view that results in one row per vendor per year with the columns being the different possible totals GP […]

    Like

  3. SQL view for vendor yearly totals in Dynamics GP | Victoria Yudin - January 4, 2012

    […] Payables – you now have 2 choices.  I previously published this view that results in one row per vendor per year with the columns being the different possible totals GP […]

    Like

  4. Interesting Findings & Knowledge Sharing » SQL view to show monthly totals for Dynamics GP Vendors - December 9, 2011

    […] 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 […]

    Like

  5. SQL view to show monthly totals for Dynamics GP Vendors - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - December 9, 2011

    […] 0 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 […]

    Like

  6. SQL view to show monthly totals for Dynamics GP Vendors | Victoria Yudin - December 9, 2011

    […] Yudin 0 Comments 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 […]

    Like

  7. SQL view to show yearly totals for Dynamics GP Vendors - DynamicAccounting.net - July 19, 2010

    […] is showing off a new SQL view to show yearly totals for Dynamics GP Vendors.  I’ve added a link to my SQL Scripts page. Published: Monday, July 19, 2010, […]

    Like

  8. DynamicAccounting.net - July 19, 2010

    SQL view to show yearly totals for Dynamics GP Vendors…

    Victoria is showing off a new SQL view to show yearly totals for Dynamics GP Vendors .  I’ve added…

    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: