SQL view for vendor yearly totals in Dynamics GP


There has been a lot of talk lately about the year end close Payables and Receivables. I feel like I have spent the last week or two justifying my reasoning for not needing to perform the year end close for Payables and Receivables to many of my customers and blog readers. (For more on this, please see my Year end close in Dynamics GP blog post.)

My main reasoning has always been that the only thing accomplished by the year end close is updating the ‘amounts since last close’ year-to-date and last-year totals and since that can be easily (and maybe even better?) accomplished by a custom report, why waste time on the year end close for these modules in GP? However, where is this custom report?

For 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 tracks for vendors. This is often useful for comparing year to year information for one vendor at a time.

However, many people have asked to see this in columns representing years, so they can see all the vendor totals at the same time for all the years. That’s what you have below. I included separate columns for amounts billed and paid – please take a look at the notes (in green) for more details on the logic. The code below will give you 2009 through 2014 calendar years as well as ‘life-to-date’ totals. You can add or remove years as needed following my example.

I will be posting a similar view for Receivables in the next few days, so keep an eye out. 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_Totals_by_Year
AS
-- **************************************************************
-- Created Jan 4, 2012 by Victoria Yudin - Flexible Solutions Inc
-- For updates please check https://victoriayudin.com/gp-reports/
-- Includes all posted payables transactions in GP
-- Document dates and calendar years are used for groupings
-- Voided transactions are excluded
-- Billed amount adds Invoices, Finance Charges and Misc Charges
--      and subtracts Credit Memos and Returns
-- Only functional currency is shown
-- Note that Returns are assumed as 'on account', if this is not
--      typically the case, Returns should be excluded
-- Updated Jul 23, 2013 to add Vendor Class and Payment Terms
-- Updated Jan 28, 2013 to remove 2007 and add 2013
-- Updated Mar 25, 2014 to remove 2008 and add 2014
-- **************************************************************

SELECT
P.VENDORID Vendor_ID,
V.VENDNAME Vendor_Name,
V.VNDCLSID Vendor_Class,
V.PYMTRMID Payment_Terms,

sum(case when year(P.DOCDATE) = 2014 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) [2014 Billed],
sum(case when year(P.DOCDATE) = 2014 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) [2014 Paid],
sum(case when year(P.DOCDATE) = 2013 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) [2013 Billed],
sum(case when year(P.DOCDATE) = 2013 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) [2013 Paid],
sum(case when year(P.DOCDATE) = 2012 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) [2012 Billed],
sum(case when year(P.DOCDATE) = 2012 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) [2012 Paid],
sum(case when year(P.DOCDATE) = 2011 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) [2011 Billed],
sum(case when year(P.DOCDATE) = 2011 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) [2011 Paid],
sum(case when year(P.DOCDATE) = 2010 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) [2010 Billed],
sum(case when year(P.DOCDATE) = 2010 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) [2010 Paid],
sum(case when year(P.DOCDATE) = 2009 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) [2009 Billed],
sum(case when year(P.DOCDATE) = 2009 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) [2009 Paid],
sum(case when P.DOCTYPE < 6 then P.DOCAMNT
         else 0 end) Life_Billed,
sum(case when P.DOCTYPE = 6 then P.DOCAMNT
         else 0 end) Life_Paid

FROM --all posted payables transactions, exclude voids
(SELECT VENDORID, DOCTYPE, DOCDATE, PSTGDATE,
 case when DOCTYPE in (4,5) then DOCAMNT *-1
      else DOCAMNT end DOCAMNT
 FROM PM20000
 WHERE VOIDED = 0
 UNION ALL
 SELECT VENDORID, DOCTYPE, DOCDATE, PSTGDATE,
 case when DOCTYPE in (4,5) then DOCAMNT *-1
      else DOCAMNT end DOCAMNT
 FROM PM30200
 WHERE VOIDED = 0) P

INNER JOIN -- vendor master
 PM00200 V
 ON V.VENDORID = P.VENDORID

GROUP BY P.VENDORID, V.VENDNAME, V.VNDCLSID, V.PYMTRMID

GO
GRANT SELECT ON view_Vendor_Totals_by_Year 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.

16 Responses to “SQL view for vendor yearly totals in Dynamics GP”

  1. Victoria
    Thanks, I followed all the required steps and created the view, and smartlist, followed the direction for adding permission for smartlist to default user and role.

    Still I can only view data if I logged in to GP with SA credential, any suggestion on what am I doing wrong?

    Like

    • Hi Hetal,

      If this is your first SmartList based on a SQL view, you will need to give permissions to your users to see SmartLists based on SQL views. Here are the settings under Security Tasks:

      • Product: SmartList Builder
      • Type: SmartList Builder Permissions
      • Series: SmartList Builder Permissions or SmartList Builder (depends on the GP version/Service Pack)
      • Operations: View SmartLists with SQL Tables

      -Victoria

      Like

  2. Hi Victoria,

    How can I take the above and break it out by date range. I need specific date parameters for example 01-01-2014 thru 05-31-2014.

    Thank you for all the help!

    Like

  3. I wish I had found this before I spent the last 2 hours trying to figure out why my numbers weren’t matching GP. (Hint: I hadn’t included PM20000 in my report) You’ve once again saved me from considerable frustration and unnecessary work. Who knows how much longer I would have spent trying to figure this out. Thank you so much for this excellent blog, Victoria!

    Like

  4. I modified your SQL slightly to always give me the most recent years of data:

    sum(case when year(P.DOCDATE) = year(getdate()) and P.DOCTYPE < 6
    then P.DOCAMNT else 0 end) [CY Billed],
    sum(case when year(P.DOCDATE) = year(getdate())-1 and P.DOCTYPE < 6
    then P.DOCAMNT else 0 end) [CY-1 Billed],
    …etc

    In my report I then created a formula that displays the year number for the appropriate column.

    Now I don't have to modify and re-create the view each new year.

    Thanks for this website! It's been a GREAT resource for me!

    Like

  5. The SQL view for vendor yearly totals in Dynamics GP is very handy! Can it be expanded to also show the number of invoices billed for the current year and Life to Date? The view will give the totals as per the Vendor Yearly Summary Inquiry window where you have the Amount Billed (1st line) and the Number of Invoices (last line).

    Like

    • Hi Peter,

      You can add the following code after line 45 to accomplish this:

      ,sum(case when P.DOCTYPE = 1 and year(DOCDATE) = year(getdate()) 
      then 1 else 0 end) [Invoices Cur Yr],
      sum(case when P.DOCTYPE = 1 then 1 else 0 end) [Invoices Life]
      

      This will only add up the invoices (doc type of 1). To determine the current year it will look at the current date on the SQL server.

      -Victoria

      Note: after the updates made Jul 23, 2013, this code would need to be added after line 50, instead.

      Like

Trackbacks/Pingbacks

  1. SQL view for vendor yearly totals in Dynamics GP | Victoria Yudin | DynamicAccounting.net - July 24, 2013

    […] Victoria gives us a SQL view for vendor yearly totals in Dynamics GP  […]

    Like

  2. Interesting Findings & Knowledge Sharing » Everything Dynamics GP #50 ! - January 11, 2012

    […] SQL view for vendor yearly totals in Dynamics GP […]

    Like

  3. Everything Dynamics GP #50 ! - About Dynamics, Development and Life - GP Technical Blogs - Microsoft Dynamics Community - January 11, 2012

    […] SQL view for vendor yearly totals in Dynamics GP […]

    Like

  4. Interesting Findings & Knowledge Sharing » SQL view for customer yearly totals in Dynamics GP - January 9, 2012

    […] a follow up to my SQL view for vendor yearly totals, here is something similar for […]

    Like

  5. SQL view for customer yearly totals in Dynamics GP - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - January 9, 2012

    […] 0 As a follow up to my SQL view for vendor yearly totals, here is something similar for […]

    Like

  6. SQL view for customer yearly totals in Dynamics GP | Victoria Yudin - January 9, 2012

    […] 9, 2012 by Victoria Yudin 0 Comments As a follow up to my SQL view for vendor yearly totals, here is something similar for […]

    Like

  7. SQL view for vendor yearly totals in Dynamics GP | Victoria Yudin - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community - January 5, 2012

    […] Comments 0 Victoria Yudin has a new SQL view for vendor yearly totals in Dynamics GP. […]

    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: