August 29, 2011 
Here is a new Dynamics GP SQL view in reponsse to a reader that liked my view for sales by item by year, but wanted to see the same thing by month. I am making a few assumptions (listed in the view comments), and am hard coding one year at a time (2011 in this example). You can easily change the year as needed for one year at a time.
create view view_Sales_by_Item_by_Month
as
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_Sales_by_Item_by_Month
-- Created Aug 29, 2011
-- by Victoria Yudin - Flexible Solutions, Inc.
-- For updates see https://victoriayudin.com/gp-reports/
-- Returns total sales (invoices - returns) for each item by
-- month (for the specified year)
-- Only posted invoices and returns are included
-- 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
-- Document Date is used (not GL Posting Date)
-- Updated Aug 29, 2011 to add Generic Description, Item Class
-- and User Category 1
-- Updated Jan 25, 2012 to make column names Crystal-friendly
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
SELECT
D.ITEMNMBR Item_Number,
D.Item_Description,
D.Generic_Description,
D.Item_Class,
D.User_Category_1,
sum(case when month(D.DOCDATE) = 1
then D.SALES else 0 end) as [Jan_Sales],
sum(case when month(D.DOCDATE) = 2
then D.SALES else 0 end) as [Feb_Sales],
sum(case when month(D.DOCDATE) = 3
then D.SALES else 0 end) as [Mar_Sales],
sum(case when month(D.DOCDATE) = 4
then D.SALES else 0 end) as [Apr_Sales],
sum(case when month(D.DOCDATE) = 5
then D.SALES else 0 end) as [May_Sales],
sum(case when month(D.DOCDATE) = 6
then D.SALES else 0 end) as [Jun_Sales],
sum(case when month(D.DOCDATE) = 7
then D.SALES else 0 end) as [Jul_Sales],
sum(case when month(D.DOCDATE) = 8
then D.SALES else 0 end) as [Aug_Sales],
sum(case when month(D.DOCDATE) = 9
then D.SALES else 0 end) as [Sep_Sales],
sum(case when month(D.DOCDATE) = 10
then D.SALES else 0 end) as [Oct_Sales],
sum(case when month(D.DOCDATE) = 11
then D.SALES else 0 end) as [Nov_Sales],
sum(case when month(D.DOCDATE) = 12
then D.SALES else 0 end) as [Dec_Sales]
FROM
(SELECT SH.DOCDATE, SD.ITEMNMBR,
coalesce(I.ITEMDESC, SD.ITEMDESC) Item_Description,
coalesce(I.ITMGEDSC, '') Generic_Description,
coalesce(I.ITMCLSCD,'') Item_Class,
coalesce(I.USCATVLS_1,'') User_Category_1,
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%'
AND year(SH.DOCDATE) = 2011 --change year as needed
) D
GROUP BY D.ITEMNMBR, D.Item_Description, D.Generic_Description,
D.Item_Class, D.User_Category_1
-- add permissions for DYNGRP
GO
GRANT SELECT ON view_Sales_by_Item_by_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
This view works for me ,but i require to show on a report from select date (today) is it possible to show 12 months back .The problem is that the years overlap and i would have to show for year 2011 and 2012 .I am trying to combine both reports into one whereby can display 12 months sales .This report is for trends and reorder .Please assist
Thanks
Logan
LikeLike
Hi Logan,
So what exactly would you want the ‘logic’ for the columns to be? For example, today is 8/23/2012. Would the columns be:
8/1/2011 – 8/31/2011
9/1/2011 – 9/30/2011
…
7/1/2012 – 7/31/2012
and then on 9/1/2012 they would change to:
9/1/2011 – 9/30/2011
10/1/2011 – 10/31/2011
…
8/1/2012 – 8/31/2012
Or would you want some kind of partial month calculations happening?
-Victoria
LikeLike
Hi Vic
This is exactly what i am looking for the script to do .(12 full months backwards ) if that makes any sense
No partial months required
-Logan
LikeLike
Hi Logan,
Give me a few days and I will publish a new blog post with a ‘rolling’ 12 month version of this view. That’s a pretty common request and it would be good to have an example of the date calculations for something like that.
-Victoria
LikeLike
Hi Vic
Thank you , much appreciated , eagerly await your solution
Regards
-Logan
LikeLike
Hi Logan,
In case you didn’t see it yet, I posted this earlier today. Hope this helps with what you were looking for.
-Victoria
LikeLike
Hello Victoria
Thank you for all the help. Did you get a chance to post the rolling 12 month code?
-ricky
LikeLike
Hi Rciky,
Yep, click here to see that code.
-Victoria
LikeLike
Victoria,
Great Code. Now I need to show “Orders Taken” by month. I will be looking for SOPTYPE = 2. However, I have to join the data from SOP30200/SOP30300 tables with the data from SOP10100/SOP10200 tables to get all Orders created each month whether the order is fulfilled and invoiced or still open. How can I do this?
LikeLike
Hi Raouf!
I have code here that combines open and historical SOP data. Usually I pare this down to just the fields I need…but this will give you a good start, even if you want to use it as is.
So instead of the FROM section (lines 39 through 61) in the code on this post you could point to that view with all the SOP line items. You might need to slightly adjust some names…but hopefully that will save you from having to write all the code yourself. 🙂
Hope that helps,
-Victoria
LikeLike
Hi Victoria,
One more question. What if I want to transfer the output table to a cross table. Meaning changing
ORDNUM | CUST | ORDDATE | LINEITEM | PRICE | DELIVERY | SHIPDATE
ORD1 Cust1 1/1/2012 PN1 $100 UPS 2/1/2012
ORD1 Cust1 1/1/2012 PN2 $200 UPS 2/1/2012
ORD2 Cust2 1/5/2012 PN1 $100 FedEx No
ORD3 Cust3 1/8/2012 PN1 $100 FedEx No
ORD3 Cust3 1/8/2012 PN2 $200 FedEx No
ORD3 Cust3 1/8/2012 PN3 $100 FedEx 2/20/2012
..
To be
ORDNUM | CUST | ORDDATE | LINEITEM1 | LINEITEM2 | LINEITEM3 | LINEITEM4 |
ORD1 Cust1 1/1/2012 PN1 PN2
ORD2 Cust2 1/5/2012 PN1
ORD3 Cust3 1/8/2012 PN1 PN2 PN3
and still caputer the rest of the data somehow. Can it be done? My management want to see progress report on every order, how much % shipped. The line items are always arranged in order.
LikeLike
Hi Raouf,
This is probably doable, but a LOT more difficult to code. Part of the problem is that you don’t know how many line items you will have on an order. Another problem would be that GP does not assign line numbers like 1, 2, 3, 4…the line numbers can differ based on whether items are inserted on the order, etc. I would consider using a reporting tool (maybe Crystal) to do the cross-tab, but even with that, I think it would be difficult to get this to show how you want. If they just want to see percentage shipped, maybe another option could be to add a calculation for that on every order?
-Victoria
LikeLike
Victoria,
I am new to sql and trying to do something I am struggling with…I have combined the code in this post with your reference to the open and historical data to accomplish what I am trying to do. The only funny thing I am trying to do is combine the totals of an Alternate Part with the master Item Number and only display the Master. For example, I have part number 2000 with an alternate 2000P, I am trying to have the report sum the totaly qty sold in the months by displaying part 2000 with the 2000P total as well.
Hopefully you understand what I am trying to accomplish….
LikeLike
Jeffrey,
Try this – replace lines 54 through 76 above with the following:
(select SH.DOCDATE, SD.ITEMNMBR, coalesce(I.ITEMDESC, SD.ITEMDESC) Item_Description, coalesce(I.ITMGEDSC, '') Generic_Description, coalesce(I.ITMCLSCD,'') Item_Class, coalesce(I.USCATVLS_1,'') User_Category_1, 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%' and year(SH.DOCDATE) = 2017 --change year as needed and i.ITEMNMBR not in (select ALTITEM1 from IV00101) and i.ITEMNMBR not in (select ALTITEM2 from IV00101) union select SH.DOCDATE, A1.ITEMNMBR, coalesce(I.ITEMDESC, SD.ITEMDESC) Item_Description, coalesce(I.ITMGEDSC, '') Generic_Description, coalesce(I.ITMCLSCD,'') Item_Class, coalesce(I.USCATVLS_1,'') User_Category_1, 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 inner join IV00101 A1 on A1.ALTITEM1 = SD.ITEMNMBR where SH.VOIDSTTS = 0 and SH.SOPTYPE IN (3,4) and SD.XTNDPRCE <> 0 and SD.ITEMNMBR not like 'XXXXXXXXXXXXXXX%' and year(SH.DOCDATE) = 2017 --change year as needed union select SH.DOCDATE, A2.ITEMNMBR, coalesce(I.ITEMDESC, SD.ITEMDESC) Item_Description, coalesce(I.ITMGEDSC, '') Generic_Description, coalesce(I.ITMCLSCD,'') Item_Class, coalesce(I.USCATVLS_1,'') User_Category_1, 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 inner join IV00101 A2 on A2.ALTITEM2 = SD.ITEMNMBR where SH.VOIDSTTS = 0 and SH.SOPTYPE IN (3,4) and SD.XTNDPRCE <> 0 and SD.ITEMNMBR not like 'XXXXXXXXXXXXXXX%' and year(SH.DOCDATE) = 2017 --change year as needed ) DPlease make sure to test this. This logic will break if you have more than one item with the same alternate item(s).
-Victoria
LikeLike
Hey Victoria
I have been trying to change the year in your view to this
WHERE SH.VOIDSTTS = 0
AND SH.SOPTYPE IN (3,4)
AND SD.XTNDPRCE 0
AND SD.ITEMNMBR not like ‘XXXXXXXXXXXXXXX%’
AND year(SH.DOCDATE) = datediff(month, SH.DOCDATE, getdate()) <= 12 AND SH.DOCDATE <= getdate() — 2011 –change year as needed
) D
so it would be a rolling 12 month. But i am getting an error
Msg 102, Level 15, State 1, Procedure view_Sales_by_Item_by_Month, Line 60
Incorrect syntax near '<'.
Any ideas where i am going wrong?
Thanks as always for all your insitefull work.
Vic
LikeLike
Hi Vic,
Unless something is being stripped out by the browser, your formula looks like it is saying…give me all the data where the year of the DOCDATE = the difference between current month and the month of the DOCDATE <= 12…so if we use DOCDATE of 8/5/2011 as an example, it would be saying "year of DOCDATE = 5. You could use something like
DATEADD(yy,-1,getdate())to get the rolling year, however, this really won’t work with how I have the month columns coded because Jan 2011 and Jan 2012 would then be combined in the same column if you were running it today.Also, I think that when you’re doing a 12 month columnar report, making it a ‘rolling’ report would really require the column names and order to change. Otherwise, let’s say the rolling 12 months were from March 2011 through Feb 2012, you would have the following columns (in order): Jan 2012, Feb 2012, Mar 2011, April 2011…etc. Since the year is not part of the column name, that gets difficult to understand for anyone looking at the report. And even if the year was part of it, it’s still in the wrong order, making it counter-intuitive. What I have done for requirements like this in the past is code each column generically as ‘current – 11’, ‘current – 10’, ‘current – 9’, etc….then used formulas in Crystal to name the columns properly.
LikeLike
Victoria
I also tried this view using Crystal Reports and got an error reading invalid column name for each month. It seems likely that Crystal has reserved the month names but can you confirm that is what is causing it? The query runs fine in my sql analyzer.
Patrick
LikeLike
Hi Patrick,
Actually the issue I would expect in Crystal is the spaces in the field names. Every time I forget to code with no spaces this happens to me. 🙂 I just updated the view to take all the spaces out of the field names. In my testing this works with no issue in Crystal. Please let me know if you are still getting errors.
-Victoria
LikeLike
Victoria
I need to modify this to show by Part, Month and Country is there an easy (short of a rewrite) modification that a beginner like myself can do to make this work? At this point I am copying your views gratefully while using them to teach myself. I have tried a few of my own ideas but they haven’t worked yet
Patrick
LikeLike
Hi Patrick,
Is Part the same as Item Number? And Month is already there, right? So the only thing you need to add is Country? Country of what, specifically?
-Victoria
LikeLike
The country I need is the from the sop30200 address field. My management want to report against these three fields over a years time
LikeLike
Hi Patrick,
Here is what you can do to add the COUNTRY field from SOP30200:
D.User_Category_1, D.COUNTRY,(SELECT SH.DOCDATE, SD.ITEMNMBR, SH.COUNTRY,D.User_Category_1, D.COUNTRYThat will give you a separate line for each unique combination of item, country and month.
-Victoria
LikeLike
Thank you very much. I have a lot to learn so I am grateful for your help.
Patrick
LikeLike
Hi Victoria
Do you perhaps have a view that shows the total quantity of the item instead of Sales amount.I would prefer to use this as a reorder report for items
Thanks
LikeLike
Hi Logan,
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. However, this will not take different units of measure into consideration, so if that is needed, you will need additional code for that.-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 mistakenly posted this under the one that is sales by year)
My sales department is very excited about this. They wanted me to see if a couple things were possible
Can we add ITMGEDSC,ITMCLSCD,USCATVLS_1 from the IV00101 table
Also can it be like a rolling 12 month report?
Also do you have anythiing like this that could be filtered by customer?
Thank you so much for all you do for us mere mortals!
Vic
LikeLike
Hi Vic,
I just added the 3 additional fields for you. I have created rolling reports before, it’s certainly possible, but requires some additional coding for figuring out each month and gets even more complicated because you would cross years every month except one…not sure if this is something I would be posting on the blog any time soon. If you’re looking to do this yourself, you can take a look at some of the formulas in my Coding specific dates in SQL Server post to help get you started. For restricting by customers, you could do that for one customer at a time by adding the following in the WHERE clause, before the “) D”:
AND SH.CUSTNMBR = 'YourCustomerID'If you wanted to do something more generic, where each line is a unique customer/item combination, you’d need to add the Customer ID similarly to how I added the 3 fields from IV00101. If that’s what you’re looking for, let me know, maybe I will post another view showing that in a few days.
-Victoria
LikeLike
THANK YOU SO MUCH!!!! You are terriffic! Once sales decides what they want as far as the dates I will let you know what I come up with !!!
Vic
LikeLike
Hi Victoria I tried adding CUSTNMBR and I am gettin multi part identifier SH.CUSTNMBR could not be bound,
SELECT
D.ITEMNMBR [Item_Number],
D.Item_Description,
D.Generic_Description,
D.Item_Class,
D.User_Category_1,
SH.CUSTNMBR,
sum(case when month(D.DOCDATE) = 1
then D.SALES else 0 end) as [Jan Sales],
sum(case when month(D.DOCDATE) = 2
then D.SALES else 0 end) as [Feb Sales],
sum(case when month(D.DOCDATE) = 3
then D.SALES else 0 end) as [Mar Sales],
sum(case when month(D.DOCDATE) = 4
then D.SALES else 0 end) as [Apr Sales],
sum(case when month(D.DOCDATE) = 5
then D.SALES else 0 end) as [May Sales],
sum(case when month(D.DOCDATE) = 6
then D.SALES else 0 end) as [Jun Sales],
sum(case when month(D.DOCDATE) = 7
then D.SALES else 0 end) as [Jul Sales],
sum(case when month(D.DOCDATE) = 8
then D.SALES else 0 end) as [Aug Sales],
sum(case when month(D.DOCDATE) = 9
then D.SALES else 0 end) as [Sep Sales],
sum(case when month(D.DOCDATE) = 10
then D.SALES else 0 end) as [Oct Sales],
sum(case when month(D.DOCDATE) = 11
then D.SALES else 0 end) as [Nov Sales],
sum(case when month(D.DOCDATE) = 12
then D.SALES else 0 end) as [Dec Sales]
FROM
(SELECT SH.DOCDATE, SD.ITEMNMBR,
coalesce(I.ITEMDESC, SD.ITEMDESC) Item_Description,
coalesce(I.ITMGEDSC, ”) Generic_Description,
coalesce(I.ITMCLSCD,”) Item_Class,
coalesce(I.USCATVLS_1,”) User_Category_1,
coalesce(SH.CUSTNMBR,”) CUSTNMBR,
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%’
AND year(SH.DOCDATE) = 2011
–change year as needed
) D
GROUP BY D.ITEMNMBR, D.Item_Description, D.Generic_Description,
D.Item_Class, D.User_Category_1,SH.CUSTNMBR
LikeLike
Hi Vic,
Try changing the SH to D in the GROUP BY at the bottom and the SELECT at the top (leave it as it is in the middle).
Also, no big deal, but you don’t need a coalesce there, you can just use SH.CUSTNMBR – the coalesces are needed for the items with links to the IV00101 table in case there is a non-inventory item on your SOP transactions. That way, if there is no data for an item in the IV00101 table, it will show blanks instead of NULLs. But if there is a transaction with no customer number, that is a problem. 🙂
-Victoria
LikeLike
Thanks Victoria! Success as usual after your help. This version returns 9505 rows and your original returns 1102 i am not sure why.
You are so kind for taking the time to help me out with this.
Vic
LikeLike
Vic,
Glad that worked for you. As to why there are more rows, my original query was only showing one row per item. You’ve now changed it to show one row per item/customer combination, so I would expect it to show a lot more rows.
-Victoria
LikeLike
Hi Victoria,
I just need to have the open invoices on SOP10100, can you advice how to do that.
LikeLike
Ray,
Do you mean you want the same thing that is in this view, but only for unposted invoices? If so, change SOP30200 to SOP10100 on line 63 and change SOP30300 to SOP10200 on line 65.
-Victoria
LikeLike