June 14, 2013 
As many variations for item quantity summaries as I think I have posted on this blog, there is always another one to be had. A request from a reader brings us a monthly version of my Sales Quantities by Item by Year. The view below shows the total item quantity sold by site by month for a hard-coded year. I am making a number of assumptions (listed in the view comments in green), and there is also an overall yearly total column at the end. You can easily change the year as needed on line 64.
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_Month
as
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
--view_Sales_Qty_by_Item_Site_Month
--Created Jun 14, 2013 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 month for hardcoded year
--Calendar months and Document Dates are used
--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 month(d.DOCDATE) = 1
then d.Qty else 0 end) as Jan_Qty,
sum(case when month(d.DOCDATE) = 2
then d.Qty else 0 end) as Feb_Qty,
sum(case when month(d.DOCDATE) = 3
then d.Qty else 0 end) as Mar_Qty,
sum(case when month(d.DOCDATE) = 4
then d.Qty else 0 end) as Apr_Qty,
sum(case when month(d.DOCDATE) = 5
then d.Qty else 0 end) as May_Qty,
sum(case when month(d.DOCDATE) = 6
then d.Qty else 0 end) as Jun_Qty,
sum(case when month(d.DOCDATE) = 7
then d.Qty else 0 end) as Jul_Qty,
sum(case when month(d.DOCDATE) = 8
then d.Qty else 0 end) as Aug_Qty,
sum(case when month(d.DOCDATE) = 9
then d.Qty else 0 end) as Sep_Qty,
sum(case when month(d.DOCDATE) = 10
then d.Qty else 0 end) as Oct_Qty,
sum(case when month(d.DOCDATE) = 11
then d.Qty else 0 end) as Nov_Qty,
sum(case when month(d.DOCDATE) = 12
then d.Qty else 0 end) as Dec_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 year(sh.DOCDATE) = 2013 --change year as needed
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_Month 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 just discovered an issue using this report. It appears that in months where a return was processed, it is subtracting the total qty of the returns from the sales qty. I don’t know if this is 100% correct, but to fix it I changed “sh.SOPTYPE in (3,4)” to “sh.SOPTYPE in (3)” and it seems to have done the trick. Thanks, Steve
LikeLike
Hi Steve,
What you have discovered is what I intended – most companies subtract the returns from the sales to determine the net and consider that as what they have sold. In other words, if I sold 10 widgets in October, and also had 2 widgets returned in October, then I would say I have sold 8 widgets in October. If your company is looking for alternate reporting, or entering data into GP is a different way than a company selling widgets might, then you can, of course, change the logic to do whatever you need.
This is why it’s critical to understand the details and implications of what we are being asked for in reporting. Often the person requesting the report does not give enough detail to the person writing the report to determine what they really mean by things like “sales” or even what dates they want to use. When I get report requests I usually start out with a list of logic to confirm before writing any code. Something like:
Take all invoices and returns from the SOP module only (this leaves out all transactions entered directly into the Receivables module)
Exclude voided transactions
Use Document Dates (as opposed to GL Posting Dates)
-Victoria
LikeLike
Hi Victoria,
Thank you for explaining the report. It makes a lot more sense now. Yes, there are times that we want to deduct returns from sales and times we do not, so I completely understand why it is built the way it is.
Steve
LikeLike
Hi Victoria, I love this report and use it often but I am wondering if it is possible to change it to use “Orders” instead of “Invoices” so I can see in what month we received the orders, not when we invoiced them? I have played around with changing the SOPTYPE but the data does not look correct. Thanks so much for your help! Steve
LikeLike
Hi Steve,
If changing the SOPTYPE the code is looking at to 2 did not work for you, then we might need to look at your data together and also discuss exactly how the data that you want to report on is being entered to make sure we’re capturing it correctly. This is something I can help with as a consulting project, let me know if you’re interested in something like that.
-Victoria
LikeLike
Hi Victoria,
Thanks for your quick response. I think I may have figured it out. In addition to changing the SOPTYPE from 3 to 2, I realized that it was also using SD.QTYFULFI from the invoice for the quantity. I want to use the original order quantity so I changed it to SD.QUANTITY. That seems to have done the trick.
Thanks,
Steve
LikeLike
Hi Steve,
Good catch! Very glad you were able to figure that out. QTYFULFI is more correct (usually) to use for invoices, but you are right, you want QUANTITY for orders.
-Victoria
LikeLike
Hi Victoria, I would like to add quantity on hand to the view – sales by item by site by month- I am using the IV00102 table but I it is not working.
Thanks
LikeLike
Hi Rosemary,
Try this code:
selectd.ITEMNMBR Item_Number, d.Item_Description,
d.LOCNCODE Site_ID,
sum(case when month(d.DOCDATE) = 1
then d.Qty else 0 end) as Jan_Qty,
sum(case when month(d.DOCDATE) = 2
then d.Qty else 0 end) as Feb_Qty,
sum(case when month(d.DOCDATE) = 3
then d.Qty else 0 end) as Mar_Qty,
sum(case when month(d.DOCDATE) = 4
then d.Qty else 0 end) as Apr_Qty,
sum(case when month(d.DOCDATE) = 5
then d.Qty else 0 end) as May_Qty,
sum(case when month(d.DOCDATE) = 6
then d.Qty else 0 end) as Jun_Qty,
sum(case when month(d.DOCDATE) = 7
then d.Qty else 0 end) as Jul_Qty,
sum(case when month(d.DOCDATE) = 8
then d.Qty else 0 end) as Aug_Qty,
sum(case when month(d.DOCDATE) = 9
then d.Qty else 0 end) as Sep_Qty,
sum(case when month(d.DOCDATE) = 10
then d.Qty else 0 end) as Oct_Qty,
sum(case when month(d.DOCDATE) = 11
then d.Qty else 0 end) as Nov_Qty,
sum(case when month(d.DOCDATE) = 12
then d.Qty else 0 end) as Dec_Qty,
sum(d.Qty) Total_Qty,
d.QTYONHND Qty_on_Hand
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,
Q.QTYONHND
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
left outer join IV00102 Q
on sd.ITEMNMBR = Q.ITEMNMBR
and sd.LOCNCODE = Q.LOCNCODE
where sh.VOIDSTTS = 0
and sh.SOPTYPE in (3,4)
and year(sh.DOCDATE) = 2016 --change year as needed
and sd.ITEMNMBR not like 'XXXXXXXXXXXXXXX%') d
group by d.ITEMNMBR, d.Item_Description, d.LOCNCODE, d.QTYONHND
-Victoria
LikeLike
Victoria,
Thank you for the work you do for the Dynamics community. I was wondering if it would be hard to get fiscal year instead of calendar year?
Thanks.
Brenda Haynes
LikeLike
Hi Brenda,
Is your fiscal year the same dates every year or does it change? If it’s the same, can you let me know what the dates are?
-Victoria
LikeLike
Our fiscal year is always October 1st through September 30.
Thank you Victoria!
LikeLike
Hi Brenda,
Here is what the code would look like for the 2014 fiscal year:
There is a line towards the bottom with the dates and a note saying ‘change as needed’ – you can just change the year on the 2 dates if you want to look at a different fiscal year.
Hope that helps.
-Victoria
LikeLike
Sorry, the code didn’t post up correctly the first time, I think it should be fixed now.
-Victoria
LikeLike
Thank you Victoria! I appreciate the information.
Brenda
LikeLike
How can I add Qty, so the view will show Qty and Sales for the 12-Months?
LikeLike
HI Chuck,
Sorry, I am not sure what you mean…this already shows the sales quantities. Can you please give a little more detail as to what you’re looking for?
-Victoria
LikeLike
Sales Quantities & Dollars. I have learned a ton of SQL reading your blog, and have even been able to use many of yor scripts, and modify them for my purposes, but this one was beyond me. Essentially, I want to be able to have a script that shows Item, Sales Quantity and Sales Dollars by month.
LikeLike
Hi Chuck,
I just posted this new view combining the quantities and amounts per month – hopefully that’s what you were looking for.
-Victoria
LikeLike
Victoria,
Thank you, that is awesome & I see my error!
Your blog is my go-to place for GP & SQL.
Chuck
LikeLike
Chuck,
Glad to help!
-Victoria
LikeLike
Victoria,
Is there a way to use variables in creating column names? For example, I’d like to do something like this:
sum(case when month(d.DOCDATE) = 1 and year(d.DOCDATE) = year(getdate())
then d.Qty else 0 end) as JAN + YEAR(GETDATE),
This way, I can run the view to look at multiple years & have them labeled correctly.
LikeLike
Chuck, I am not aware of any easy way to do this directly in SQL. I believe this blog talks about options for possibly accomplishing this with PIVOT and dynamic names: http://www.kodyaz.com/articles/t-sql-dynamic-pivot-table-example-code.aspx.
One way that I have done this in the past is by adding formulas to column names in Crystal Reports or SSRS. However, you might be better off with the SQL code created a different way (with the data in rows instead of columns) to be be able to more easily use a reporting tool for that. Or better yet, with a reporting tool, you can just use a parameter to specify the year.
-Victoria
LikeLike
Victoria,
Thanks, I’ve looked around other places & tried several options (concat, etc) and nothing worked. I’ll just go with CY and PY, that should satisfy my users. Thanks again for your help.
Chuck
LikeLike
Like many developers, I’m sure you cringe when you have to include hard code (in this case, year). Perhaps you could show how to the same in a table function with year passed as a parameter.
Ken
LikeLike
Hi Ken!
You mean like using year(getdate()) instead? Or something else? You’ll probably like this view for the rolling 12 months. 🙂
-Victoria
LikeLike
This is perfect for what I was looking to analyze. Now we can do it for any year too. Thank you so much.
LikeLike