January 9, 2012 
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 functional 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 update years
-- Updated Mar 25, 2018 to update years
-- Updated Apr 10, 2018 to fix labels
-- **********************************************************
SELECT
T.CUSTNMBR [Customer ID],
C.CUSTNAME [Customer Name],
C.CUSTCLAS [Class ID],
sum(case when YEAR(T.DOCDATE) = 2018
then T.Amount else 0 end) [2018 Sales],
sum(case when YEAR(T.DOCDATE) = 2017
then T.Amount else 0 end) [2017 Sales],
sum(case when YEAR(T.DOCDATE) = 2016
then T.Amount else 0 end) [2016 Sales],
sum(case when YEAR(T.DOCDATE) = 2015
then T.Amount else 0 end) [2015 Sales],
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(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.
Victoria Yudin 
Victoria
please ignore my last email – I see where you have given a response on this already- using the “fiscal” year – I was looking for financial year – same thing.
Thanks
Ray
LikeLike
Hi Victoria,
Is there a way to calculate the same figures – based on Financial Year -rather than calendar year? – The financial period my client is working in, is 01-Nov to 31-Oct
Thanks
Ray Phelan
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Thanks for your help!
Have a great day.
ricky
LikeLike
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,
LikeLike
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
LikeLike
Thanks, worked like a charm
ricky
LikeLike
thanks for the quick reply!
LikeLike
I would like to generate the start date automatically ’01/01/2014′
sum(case when DOCDATE between ’01/01/2014′ and
dateadd(yy,-1,getdate()) then YourAmount else 0 end) Last_Year
Is there a way to enter the month and day but have it get the year automatically?
thanks,
ricky
LikeLike
Ricky,
Instead of DOCDATE, you can use the following:
dateadd(yy, datediff(yy, 0, getdate()), 0)
That will calculate the first day of the current year.
-Victoria
LikeLike
I need to get the first day of the previous last (last year) and the first day of the year -2 (2013).
Would this work?
dateadd(yy-1, datediff(yy, 0, getdate()), 0)
dateadd(yy-2, datediff(yy, 0, getdate()), 0)
thanks
ricky
LikeLike
Sorry, no. Here is the correct syntax:
–current year:
dateadd(yy, datediff(yy, 0, getdate()), 0)
–last year:
dateadd(yy, -1, dateadd(yy, datediff(yy, 0, getdate()), 0))
–2 years back:
dateadd(yy, -2, dateadd(yy, datediff(yy, 0, getdate()), 0))
-Victoria
LikeLike
Thanks.
You are a life saver!
ricky
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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,
LikeLike
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:
-Victoria
LikeLike
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
LikeLike
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
LikeLike
Hi Patrick,
Try putting the column names in brackets, like I have them in my code. So for example:
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
LikeLike
Hi Victoria,
I was wondering if there was any way to include the “Default Ship To Address” columns to the above sql report? I am looking to initiate a mailing campaign based on customer sales and need the address associated with the customer. Any help would be greatly appreciated.
LikeLike
Hi Oriotelex,
You can just add some code to my view, here is an example that adds most of the address fields for you:
select
t.*,
a.CNTCPRSN [Contact Person],
a.ADDRESS1 [Address 1],
a.ADDRESS2 [Address 2],
a.ADDRESS3 [Address 3],
a.CITY City,
a.[STATE] [State],
a.ZIP Zip
from view_Customer_Totals_by_Year t
left outer join RM00101 c
on t.[Customer ID] = c.CUSTNMBR
left outer join RM00102 a
on c.CUSTNMBR = a.CUSTNMBR
and c.PRSTADCD = a.ADRSCODE
-Victoria
LikeLike
Thank you for your super quick response Victoria.
I tried running the following code but am receiving an error.
SELECT
t.*,
a.CNTCPRSN [Contact Person],
a.ADDRESS1 [Address 1],
a.ADDRESS2 [Address 2],
a.ADDRESS3 [Address 3],
a.CITY City,
a.[STATE] [State],
a.ZIP Zip
T.CUSTNMBR [Customer ID],
C.CUSTNAME [Customer Name],
C.CUSTCLAS [Class ID],
sum(case when YEAR(T.DOCDATE) = 2019
then T.Amount else 0 end) [2019 Sales],
sum(case when YEAR(T.DOCDATE) = 2018
then T.Amount else 0 end) [2018 Sales],
sum(case when YEAR(T.DOCDATE) = 2017
then T.Amount else 0 end) [2017 Sales],
sum(case when YEAR(T.DOCDATE) = 2016
then T.Amount else 0 end) [2016 Sales],
sum(case when YEAR(T.DOCDATE) = 2015
then T.Amount else 0 end) [2015 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
from view_Customer_Totals_by_Year t
left outer join RM00101 c
on t.[Customer ID] = c.CUSTNMBR
left outer join RM00102 a
on c.CUSTNMBR = a.CUSTNMBR
and c.PRSTADCD = a.ADRSCODE
LEFT OUTER JOIN RM00101 C — customer master
ON T.CUSTNMBR = C.CUSTNMBR
GROUP BY T.CUSTNMBR, C.CUSTNAME, C.CUSTCLAS
LikeLike
Not sure if what you posted above is stripping some stuff out, or if that’s how it is copying from your browser into SQL. In any case, I can tell some stuff is missing. Can you please try using a different browser, maybe Chrome and recopy/paste the code from there into SQL? I just tested in Chrome and it worked with no issues.
-Victoria
LikeLike
Also, it looks like you may have combined the code for the view and the separate code I sent. That will not work. Create the view first. Then use the code in my reply above separately.
-Victoria
LikeLike
Thank you again for your quick response. I am new to sql and therefore am unsure on how to go about that. Is there any way you can send me a demonstration? Thanks!
LikeLike
Best thing to do would be to get some help or training on this either from your IT department or your GP partner.
-Victoria
LikeLike