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 2012 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 http://victoriayudin.com/gp-reports/
--Returns total sales quantities fulfilled (invoices - 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
--***********************************************************************************
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(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.
Could this be done by site?
Rick,
Here is the same thing by site: http://victoriayudin.com/2013/02/28/sql-view-for-sales-quantities-by-item-by-site-by-year/.
-Victoria
Thank You just what I was looking for