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 2007 through 2012 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, Inc.
-- For updates please check http://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 columns
-- *********************************************************************
SELECT
T.CUSTNMBR [Customer ID], C.CUSTNAME [Customer Name], C.CUSTCLAS [Class ID],
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(case when YEAR(T.DOCDATE) = 2008 then T.Amount else 0 end) [2008 Sales],
sum(case when YEAR(T.DOCDATE) = 2007 then T.Amount else 0 end) [2007 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.
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,
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
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
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
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