calculator

SQL view for customer yearly totals in Dynamics GP


As a follow up to my SQL view for vendor yearly totals, here is something similar for customers.

I have combined sales less credits/returns in one column, please take a look at the notes above the code (on lines 5 through 16 below) to see more details about the logic I am using. The code below will give you 2009 through 2014 (*updated on Jan 22, 2014) calendar years as well as ‘life-to-date’ total sales. You can add or remove years as needed following my example.

For more Dynamics GP Receivables code, take a look at my Receivables 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_Customer_Totals_by_Year
AS

-- **********************************************************
-- Created Jan 9, 2012 by Victoria Yudin - Flexible Solutions
-- For updates please check https://victoriayudin.com/gp-reports/
-- Includes all posted receivables transactions in GP
-- Document dates and calendar years are used for groupings
-- Voided transactions are excluded
-- Sales amount adds Invoices, Debit Memos and Finance Charges
--      and subtracts Credit Memos and Returns
-- Amount includes subtotal plus misc amount less trade discount
-- Only fucntional currency is shown
-- Note that Returns are assumed as 'on account', if this is
--     not typically the case, Returns should be excluded
-- Total columns shows all years, not just what is shown in
--     the other columns
-- Updated Jan 22, 2014 to add 2013 and 2014 and remove
--     2008 and 2008
-- **********************************************************

SELECT
T.CUSTNMBR [Customer ID],
C.CUSTNAME [Customer Name],
C.CUSTCLAS [Class ID],
sum(case when YEAR(T.DOCDATE) = 2014
  then T.Amount else 0 end) [2014 Sales],
sum(case when YEAR(T.DOCDATE) = 2013
  then T.Amount else 0 end) [2013 Sales],
sum(case when YEAR(T.DOCDATE) = 2012
  then T.Amount else 0 end) [2012 Sales],
sum(case when YEAR(T.DOCDATE) = 2011
  then T.Amount else 0 end) [2011 Sales],
sum(case when YEAR(T.DOCDATE) = 2010
  then T.Amount else 0 end) [2010 Sales],
sum(case when YEAR(T.DOCDATE) = 2009
  then T.Amount else 0 end) [2009 Sales],
sum(T.Amount) [Total Sales]

FROM -- all posted RM transactions, exclude voids
(SELECT  CUSTNMBR, DOCDATE, GLPOSTDT, DOCNUMBR,
 case when RMDTYPAL in (1,3,4) then (SLSAMNT + MISCAMNT - TRDISAMT)
   when RMDTYPAL in (7,8) then -1*(SLSAMNT + MISCAMNT - TRDISAMT)
   else 0
   end Amount
 FROM RM20101
 WHERE VOIDSTTS = 0
UNION ALL
 SELECT  CUSTNMBR, DOCDATE, GLPOSTDT, DOCNUMBR,
 case when RMDTYPAL in (1,3,4) then (SLSAMNT + MISCAMNT - TRDISAMT)
   when RMDTYPAL in (7,8) then -1*(SLSAMNT + MISCAMNT - TRDISAMT)
   else 0
   end Amount
 FROM RM30101
 WHERE VOIDSTTS = 0) T

LEFT OUTER JOIN RM00101 C  -- customer master
     ON T.CUSTNMBR = C.CUSTNMBR

GROUP BY T.CUSTNMBR, C.CUSTNAME, C.CUSTCLAS

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

26 Responses to “SQL view for customer yearly totals in Dynamics GP”

  1. Hi Victoria

    I was able to roll up the totals by month using this code. I would also like to get the number of invoices by month (count distinct docnumbr).
    Can you help me,
    thanks,
    ricky

    Like

    • Hi Ricky,

      You can do something like this to get the monthly sales and count of invoices:

      select
      T.CUSTNMBR [Customer ID],
      C.CUSTNAME [Customer Name],
      C.CUSTCLAS [Class ID],
      sum(case when year(T.DOCDATE) = 2015 
          and month(T.DOCDATE) = 9
        then T.Amount else 0 end) [Sep 2015 Sales],
      sum(case when year(T.DOCDATE) = 2015 
          and month(T.DOCDATE) = 9
          and T.RMDTYPAL = 1 
          then 1 else 0 end) [Sep 2015 Invoices]
      
      from -- all posted RM transactions, exclude voids
      (select CUSTNMBR, DOCDATE, GLPOSTDT, DOCNUMBR, RMDTYPAL,
       case when RMDTYPAL in (1,3,4) then (SLSAMNT + MISCAMNT - TRDISAMT)
         when RMDTYPAL in (7,8) then -1*(SLSAMNT + MISCAMNT - TRDISAMT)
         else 0
         end Amount
       from RM20101
       where VOIDSTTS = 0
      union all
       select CUSTNMBR, DOCDATE, GLPOSTDT, DOCNUMBR, RMDTYPAL,
       case when RMDTYPAL in (1,3,4) then (SLSAMNT + MISCAMNT - TRDISAMT)
         when RMDTYPAL in (7,8) then -1*(SLSAMNT + MISCAMNT - TRDISAMT)
         else 0
         end Amount
       from RM30101
       where VOIDSTTS = 0) T
      
      left outer join RM00101 C  -- customer master
           on T.CUSTNMBR = C.CUSTNMBR
      
      group by T.CUSTNMBR, C.CUSTNAME, C.CUSTCLAS
      

      -Victoria

      Like

      • Ricky,

        Sorry, the formatting sometimes gets stripped out of the code on the email replies. If you click back through to the blog, I updated the code in my last reply to be formatted for SQL.

        -Victoria

        Like

      • Thanks for your help!
        Have a great day.
        ricky

        Like

      • I would like to get YTD totals and compare it to Last year same period. This doesn’t work for me.
        Any ideas?
        CASE WHEN DOCDATE BETWEEN ’01/01/2015′ AND (SELECT GETDATE()
        CASE WHEN DOCDATE BETWEEN ’01/01/2014′ AND (SELECT DATEADD(YEAR,-1,GETDATE()))
        thanks,

        Like

        • Ricky,

          You’ve got a few things in there that will not work. Below is what I would recommend. You will need to replace “YourAmount” and “YourTable” with appropriate values:

          select
          sum(case when DOCDATE between ’01/01/2015′ and getdate()
          then YourAmount else 0 end) Year_to_Date,
          sum(case when DOCDATE between ’01/01/2014′ and
          dateadd(yy,-1,getdate()) then YourAmount else 0 end) Last_Year

          from YourTable

          -Victoria

          Like

  2. Victoria,
    Your report SQL view for customer yearly totals in Dynamics GP is great but I was wondering how it might be changed to pull in information from customers where they shipped to another territory?

    We have some customers where their primary billing address is in one state but they predominantly bill to and ship to another state. As they are all under the Primary Class ID of one state, how can I get the sales ship to another state?
    Thank you

    Like

    • Hi Pam,

      This view really won’t be able to give you that level of detail. You would most likely need to go to the SOP tables directly for that. While I don’t have anything specifically like this, you should be able to put it together with some examples from the views on this page.

      -Victoria

      Like

  3. Is there anything like this to show total customer sales by year for all revenue? Or does that get tricky due to 3rd party products like WennSoft and such? I’m using this view and the total sales with this view do not add up to the customers’ total sales I’m seeing in GP windows like the Customer Yearly Summary Inquiry window. I get that the year has to be closed in order to be “Last Year” info. Technically right now, 2012 is our “last year” and my 2012 totals don’t match this 2012 totals view list. Maybe I’m just missing something. Thanks!

    -John

    Like

  4. Hi Victoria,
    I’m having challenges on how to generate customer balances trend on GP.

    i want to be able to generate a report that will compare each customer balance last month to this month, then look at quarterly, bi-annually and yearly. look at the sample of report below.

    Customer Name Balance Jan Balance Feb Balance March

    Customer A xxxxxxx XXXX xxxx

    Customer B xxxx xxxxx xxxxx

    Customer C xxxxxx xxxxx xxxxxxx

    The report will help me to check if my collection or Receivable manager is working or not

    Rgds

    Seafetcher

    Like

    • Hi Seafetcher,

      There is really no easy or out-of-the-box way to do this. Each month end balance would need to be calculated separately, then combined into an overall report. This can be done, but each month end calculation would be a pretty complicated stored procedure. Another option might be to calculate the month end numbers and store them in a separate table (or even database), so you can more easily use it for other reporting. You can probably come up with a SQL job to do this regularly. This would work if the numbers never changed, otherwise these stored balances would need to get updated.

      -Victoria

      Like

  5. Hi Victoria,
    For SQL view for customer yearly totals, how can I get total by Fiscal Year. Similarly I need Fiscal Year for the AP Yearly Totals

    Thanks,

    Like

    • Iqbal,

      One way would be to change the formulas to specify the dates. For example, if your fiscal year 2012 was from 7/1/2011 to 6/30/2012, you could change the formula on line 21 to say:

      sum(case when T.DOCDATE between '7/1/2011' and '6/30/2012' then T.Amount else 0 end) [2012 Sales],
      

      -Victoria

      Like

      • Hi Victoria,
        Thanks for the quick response. I replaced the line with the Date parameters and it worked perfectly.

        I tested this on the Customer Totals By Year. I will do the same on the AP Vendor Totals by Year

        Thanks again for the all the helpful views you have created so far and thanks for the modification

        Iqbal

        Like

  6. Victoria

    I’m using studio to query against this view. When I use ‘*” I get all the columns and all their totals correct. However, when I try and call just a couple of columns by name such as 2012_Sales, or 2011_Sales I get a column name of _Sales and the year in as a total. I tried using spaces that appear in your code above but I got the same result. How did I get in left field with this one?

    Patrick

    Like

    • Hi Patrick,

      Try putting the column names in brackets, like I have them in my code. So for example:

      select [Customer ID], [2012 Sales], [2011 Sales]
      from view_Customer_Totals_by_Year
      

      Alternately, you could change the view to have different column names, using underscores or no spaces…like Sales2011, Sales2012, then you would be able to select the columns using the name without the brackets. Also, if you are using this code for Crystal Reports, you would want to take the spaces out of the column names, as Crystal is not usually happy with spaces in table or field names.

      -Victoria

      Like

Trackbacks/Pingbacks

  1. SQL view for customer yearly totals in Dynamics GP | Victoria Yudin - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community - January 11, 2012

    […] Comments 0 Victoria Yudin provides a SQL view for customer yearly totals in Dynamics GP […]

    Like

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

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

    Like

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

    […] more here: SQL view for customer yearly totals in Dynamics GP VN:F [1.9.13_1145]Please wait while your rating is cast…Rating: 0.0/10 (0 votes cast)VN:F […]

    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: