January 23, 2012 
Ever since I published my view for sales by item by year I started receiving requests for the same type of view showing quantities instead of amounts. There are two ways of doing this, once from inventory and another from sales. Hard to say which is the best, as I have seen arguments for both, but I prefer to do this from the SOP module. The view below makes a number of assumptions (listed in the view comments in green), and I am hard coding years from 2000 through 2013 as well as adding an overall total column at the end. You can easily change the years or add new ones by following the example in my code.
To see additional Dynamics GP SOP code please take look at this page. For additional GP reporting information and links, check out my GP Reports page.
create view view_Sales_Qty_by_Item_by_Year
as
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
--view_Sales_Qty_by_Item_by_Year
--Created Jan 23, 2012 by Victoria Yudin - Flexible Solutions Inc
--For updates see https://victoriayudin.com/gp-reports/
--Returns total sales quantities fulfilled (SOP invoices less
-- returns) for each item by year
--Only posted invoices and returns are included
--Quantity is calculated by multiplying by QTYBSUOM column in
-- case other UofM's are used on transations
--Voided transations are excluded
--Item Description is taken from Inventory Item Maintenance for
-- all inventory items and from SOP line items for
-- non-inventory items
--Updated Jun 14, 2013 to add year 2013
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
SELECT
D.ITEMNMBR Item_Number, D.Item_Description,
sum(case when year(D.DOCDATE) = 2000
then D.Qty else 0 end) as [2000_Qty],
sum(case when year(D.DOCDATE) = 2001
then D.Qty else 0 end) as [2001_Qty],
sum(case when year(D.DOCDATE) = 2002
then D.Qty else 0 end) as [2002_Qty],
sum(case when year(D.DOCDATE) = 2003
then D.Qty else 0 end) as [2003_Qty],
sum(case when year(D.DOCDATE) = 2004
then D.Qty else 0 end) as [2004_Qty],
sum(case when year(D.DOCDATE) = 2005
then D.Qty else 0 end) as [2005_Qty],
sum(case when year(D.DOCDATE) = 2006
then D.Qty else 0 end) as [2006_Qty],
sum(case when year(D.DOCDATE) = 2007
then D.Qty else 0 end) as [2007_Qty],
sum(case when year(D.DOCDATE) = 2008
then D.Qty else 0 end) as [2008_Qty],
sum(case when year(D.DOCDATE) = 2009
then D.Qty else 0 end) as [2009_Qty],
sum(case when year(D.DOCDATE) = 2010
then D.Qty else 0 end) as [2010_Qty],
sum(case when year(D.DOCDATE) = 2011
then D.Qty else 0 end) as [2011_Qty],
sum(case when year(D.DOCDATE) = 2012
then D.Qty else 0 end) as [2012_Qty],
sum(case when year(D.DOCDATE) = 2013
then D.Qty else 0 end) as [2013_Qty],
sum(D.Qty) Total_Qty
FROM
(SELECT SH.DOCDATE, SD.ITEMNMBR,
coalesce(I.ITEMDESC, SD.ITEMDESC) Item_Description,
CASE SD.SOPTYPE
WHEN 3 THEN SD.QTYFULFI*QTYBSUOM
WHEN 4 THEN SD.QUANTITY*QTYBSUOM*-1
END Qty
FROM SOP30200 SH
INNER JOIN
SOP30300 SD
ON SD.SOPNUMBE = SH.SOPNUMBE
AND SD.SOPTYPE = SH.SOPTYPE
LEFT OUTER JOIN
IV00101 I
ON I.ITEMNMBR = SD.ITEMNMBR
WHERE SH.VOIDSTTS = 0
AND SH.SOPTYPE IN (3,4)
AND SD.ITEMNMBR not like 'XXXXXXXXXXXXXXX%') D
GROUP BY D.ITEMNMBR, D.Item_Description
GO
GRANT SELECT ON view_Sales_Qty_by_Item_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 
Thank you for your SQL Views they have been very helpful. Our financial year runs October-September how can this be defined in this view?
LikeLike
Hi Rosesflo,
You can change the code that specifies just he year to instead give it specific dates. So instead of year(D.DOCDATE) = 2021 you can use:
D.DOCDATE between ‘2021-10-01’ and ‘2022-09-30’
-Victoria
LikeLike
Thanks very much , makes perfect sense…. Did not think of that!
LikeLike
Thank you for your response, not sure what I’m missing but I got an error ‘incorrect syntax’ ,’)’ expecting and , or this is what I changed it to – sum(case when year (D.GLPOSTDT between ‘2016-10-01’ and ‘2017-09-30’) = 2017
LikeLike
Looks like you added what I wrote in the middle of the formula instead of replacing it. Also, looks like you’ve already got changes to my code, as you’re using GLPOSTDT and not DOCDATE – hard to know if you have any other changes that will break things.
Your line should read as follows:
sum(case when D.GLPOSTDT between ‘2016-10-01’ and ‘2017-09-30’
LikeLike
Hi Victoria
Could you clarify the following line please?
AND SD.ITEMNMBR not like ‘XXXXXXXXXXXXXXX%’
Thanks
Stu
LikeLike
Hi Stu,
Once in a while I come across GP datasets where the printing of the GP alignment form has caused data with all X’s to be saved in some of the tables. This is not valid data, so I am excluding it from the results with that line in my code. If you actually have a valid item number that starts with ‘XXXXXXXXXXXXXXX’ you should comment out that line from the code.
-Victoria
LikeLike
Victoria
I can run this view in SQL but Crystal keeps getting an error. It notes Incorrect syntax near ‘.2006’. Any ideas?
LikeLike
Sorry. I removed date preceding that year
LikeLike
Patrick, so you’re all set? 🙂
-Victoria
LikeLike
No, I checked the edit I did this morning and it conforms to the rest of your code. I even re installed the view as you wrote it and I still get an error when I run it in Crystal. Could the brackets or the underscore be an issue? It errors out on the first date column chosen. Currently, 2012
LikeLike
Hi Patrick,
Try recreating the view with the fields labeled Qty_2012 instead of 2012_Qty, etc. It might be having a problem with the number in front. The underscore should be no problem and the brackets are only used by SQL, Crystal should not see any brackets. If it does, something else is going on.
-Victoria
LikeLike
Winner and still Champion… Victoria!!! Changing the number to the back of the column name worked.
LikeLike
Excellent!! 🙂
LikeLike
Victoria, How can I modify this to see Quantity Sold by Item Number and broken out then by site, year, and then month. To try and account for seasonal shifts. Thanks in advance.
LikeLike
Zach,
Are you saying you want to be able to compare the same month across multiple years? Like:
Item — Site — May 2013 — May 2012 — May 2011
Or something else?
-Victoria
LikeLike
Not exactly. I would like to see monthly totals (QTY) for a given year. I would then manipulate the data a little. I have a purchaser that bases orders on a six month quantity sold average.
LikeLike
Zach,
I just published a new script that will hopefully accomplish what you need.
-Victoria
LikeLike
Could this be done by site?
LikeLike
Rick,
Here is the same thing by site: https://victoriayudin.com/2013/02/28/sql-view-for-sales-quantities-by-item-by-site-by-year/.
-Victoria
LikeLike
Thank You just what I was looking for
LikeLike