August 11, 2011 
I had a request from a blog reader for a view that shows sales of items by year. This seemed like pretty useful code to create, so I put the following view together. I am making a few assumptions (listed in the view comments), and am hard coding years from 2005 through 2011. You can easily change the years or add new ones by following the example in my code.
create view view_Sales_by_Item_by_Year
as
-- *********************************************************************
-- view_Sales_by_Item_by_Year
-- Created Aug 11, 2011 by Victoria Yudin - Flexible Solutions, Inc.
-- For updates see https://victoriayudin.com/gp-reports/
-- Returns total sales (invoices - returns) for each item by year
-- Only posted invoices and returns are included
-- 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 Jul 12, 2012 to add Total Sales, 2012 and change field names
-- *********************************************************************
SELECT
D.ITEMNMBR Item_Number,
D.Item_Description,
sum(case when year(D.DOCDATE) = 2005 then D.SALES else 0 end) as Sales_in_2005,
sum(case when year(D.DOCDATE) = 2006 then D.SALES else 0 end) as Sales_in_2006,
sum(case when year(D.DOCDATE) = 2007 then D.SALES else 0 end) as Sales_in_2007,
sum(case when year(D.DOCDATE) = 2008 then D.SALES else 0 end) as Sales_in_2008,
sum(case when year(D.DOCDATE) = 2009 then D.SALES else 0 end) as Sales_in_2009,
sum(case when year(D.DOCDATE) = 2010 then D.SALES else 0 end) as Sales_in_2010,
sum(case when year(D.DOCDATE) = 2011 then D.SALES else 0 end) as Sales_in_2011,
sum(case when year(D.DOCDATE) = 2012 then D.SALES else 0 end) as Sales_in_2012,
sum(D.SALES) Total_Sales
FROM
(SELECT SH.DOCDATE, SD.ITEMNMBR,
coalesce(I.ITEMDESC, SD.ITEMDESC) Item_Description,
CASE SD.SOPTYPE
WHEN 3 THEN SD.XTNDPRCE
WHEN 4 THEN SD.XTNDPRCE*-1
END SALES
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.XTNDPRCE <> 0
AND SD.ITEMNMBR not like 'XXXXXXXXXXXXXXX%') D
GROUP BY D.ITEMNMBR, D.Item_Description
--the following will grant permissions to this view to DYNGRP,
--leave this section off if you do not want to grant permissions
GO
GRANT SELECT ON view_Sales_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 
Hi Victoria. I wrote all this in the SQL, it ran successfully, but I can’t ‘find’ it…
I’m not very advanced, so please forgive my stupidity…
LikeLike
Hi Robin,
What are you trying to do? If you just want to run the view in SQL to see the results, you can use the following query:
If you want to use this to create a SmartList, take a look at this blog post.
If you’re looking to do something else, please give me some details.
-Victoria
LikeLike
This report is EXACTLY what I need! However, I don’t know SQL, and am just learning Smartlist Builder. Can you show me what a calculated field would look like for one of the years? I could figure the rest out myself from that, just having trouble with calculating dates. Wish I were as smart as you!! Thanks.
LikeLike
Hi Sharon,
I don’t recommend doing this in SmartList Builder directly, it would be no less complicated than doing it in SQL and frankly, I am not sure you can duplicate everything I am doing here in SmartList Builder. The beauty of using SQL for this is that I have already done the work for you. I also have a blog post here showing how to use a SQL view with SmartList Builder. Hopefully this will be all you need.
-Victoria
LikeLike
Victoria
I have used this view for management and they like it but the wanted to see only the last four years. My issues is when I choose individual columns not “*” I get the year in the sales total quantity (see below). What am I doing wrong? I use Server Management Studio and I have both written and dragged and drop with equal results. Also management wants to add current on hand quantity which I plan to link by item number to iv00102 any suggestions?
Patrick
Item_Number Item_Description _Sales _Sales
61.40.510.01616 INT NiTi AT L .016x.016/.41x.41 2005 2006
37.30.258.00020 MB Oradon RL 22-802 20 LG 2005 2006
37.30.158.00003 MB Oradon RU 22-852 03 LG 2005 2006
LikeLike
Hi Patrick,
I changed the view slightly to put it in the new format with line numbers…that makes it easier to talk about changes. I also added a column for total sales. What I would recommend, if you wanted to only see 2009 through 2012 is to delete lines 19 through 22 and add the following code between lines 46 and 47 (this would limit the total sales column to those years):
To add on hand quantity, you can add the following after line 18:
Add the following before the GROUP BY (line 49):
And the following at the end of the GROUP BY line (line 49):
Hope that helps,
-Victoria
LikeLike
Victoria –
If I wanted to look at customers and vendors in the same manner, what is the SQL code? I have been trying with no luck – I can only get individual rows with customers/vendors and their annual sales but would prefer to see my years in columns.
LikeLike
Mark,
The trick to getting the data into yearly columns is to use the “sum” code, as in the example above. Give me a few days and I will publish some new posts to help with this.
-Victoria
LikeLike
How about the same but for customers? I’ve been trying to figure out how and which table to pull from but have not been able to figure out (dollar value).
Thanks!!!
LikeLike
Hi Mark,
The inventory sales are coming from the SOP (Sales Order Processing) module. I can change this easily enough to do it by customer…but then a few questions then come up:
-Victoria
LikeLike
I had not considered your points. Could RM and COP be combined?
I would be looking at subtotal not invoice amount. The invoice amount would give freight, tax, miscellaneous.
LikeLike
Mark,
You cannot really ‘combine’ RM and SOP, as SOP is a subset of RM. So if you want information from both, you would most likely be better off simply looking at RM data.
-Victoria
LikeLike
Thanks a lot Victoria I tested and it works perfect! If you need any help related to network and Windows Servers let me know to pay it forward! =D
– George
LikeLike
Hi Victoria, i need this but instead of year by month, what do i need to change?
Thanks,
George
LikeLike
Hi George,
I just posted a new view for this: https://victoriayudin.com/2011/08/29/sql-view-for-sales-by-item-by-month/.
-Victoria
LikeLike
Victoria-
Once again you continue to astound me with your knowledge. Wat part of this can I change to get it to reflect number of units (or quantity of item) rather than dollars? Dollars are good, but as prices change, the dollars can skew whether it is worthwhile to carry an item.
I’m blushing that you actually chose my idea to use for a blog topic. 🙂
Mark
LikeLike
Mark,
Thanks for the great idea! You know, I was actually wondering whether to do this for dollars or quantities when I was writing it. 🙂
You can change this to quantities instead by changing all instances you find of
SD.XTNDPRCE(there should be 3 of them) to beSD.QUANTITYinstead.-Victoria
LikeLike
PS, this will not take into consideraton Units of Measure…if you need that, let me know and I can take a look at it further.
-Victoria
LikeLike
Great idea !!! I am getting this error though trying to create it
Msg 102, Level 15, State 1, Procedure view_Sales_by_Item_by_Year, Line 26
Incorrect syntax near ‘=’.
Msg 102, Level 15, State 1, Procedure view_Sales_by_Item_by_Year, Line 50
Incorrect syntax near ‘D’.
LikeLike
Hi Vic,
I had a stray bracket…can you please try it again?
Thanks,
-Victoria
LikeLike
Perfect Worked like a charm!!! Another useful view/report by the Queen of GP reporting!!!
Thanks!
LikeLike
Awww! Thanks for testing it so quickly. 🙂
Have a great day,
-Victoria
LikeLike