Below is another variation on my view for sales by item by year and view for sales quantities by item by year. This one shows the total item quantity sold by site by year. The view below makes a number of assumptions (listed in the view comments in green), and I am hard coding years from 2006 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.
Some additional resources:
- Sales Order Processing (SOP) SQL views
- Sales Order Processing (SOP) commonly used tables
- Other GP Reporting links
CREATE VIEW view_Sales_Qty_by_Item_Site_Year AS -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ -- view_Sales_Qty_by_Item_Site_Year -- Created Feb 28, 2013 by Victoria Yudin, Flexible Solutions Inc -- For updates see http://victoriayudin.com/gp-reports/ -- Returns total sales quantities fulfilled (invoices less -- returns) for each item by site 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 transactions -- Voided transactions 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, D.LOCNCODE Site_ID, sum(case when year(D.DOCDATE) = 2006 then D.Qty else 0 end) [2006_Qty], sum(case when year(D.DOCDATE) = 2007 then D.Qty else 0 end) [2007_Qty], sum(case when year(D.DOCDATE) = 2008 then D.Qty else 0 end) [2008_Qty], sum(case when year(D.DOCDATE) = 2009 then D.Qty else 0 end) [2009_Qty], sum(case when year(D.DOCDATE) = 2010 then D.Qty else 0 end) [2010_Qty], sum(case when year(D.DOCDATE) = 2011 then D.Qty else 0 end) [2011_Qty], sum(case when year(D.DOCDATE) = 2012 then D.Qty else 0 end) [2012_Qty], sum(case when year(D.DOCDATE) = 2013 then D.Qty else 0 end) [2013_Qty], sum(D.Qty) Total_Qty from (select SH.DOCDATE, SD.ITEMNMBR, SD.LOCNCODE, 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, D.LOCNCODE go grant select on view_Sales_Qty_by_Item_Site_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.