August 27, 2012 
Rolling twelve month reports are not always very easy to create, and I have been seeing more requests for them, so I thought I would show an example of one way to do this. The view below is a variation of my sales by item by month view, however instead of specifying all months in a year, this will return the last 12 months, not including the current month. Note that this will be using the current date on the SQL server, not the user date specified in GP. As usual, I am making some assumptions which are listed in the view comments.
Related code and table information:
- Sales Order Processing (SOP) tables
- Inventory tables
- Sales Order Processing (SOP) SQL views
- Inventory SQL views
- GP Reports (there is a section for General Ledger reports under Dynamics GP SQL Scripts)
create view view_Rolling_12_Mo_Sales_by_Item
as
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_Rolling_12_Mo_Sales_by_Item
-- Created Aug 27, 2012 by Victoria Yudin, Flexible Solutions
-- For updates see https://victoriayudin.com/gp-reports/
-- Returns total sales (invoices - returns) for each item for
-- the last 12 months. Current month is not included,
-- even if it is the last day of the month.
-- 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).
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
SELECT
D.ITEMNMBR Item_Number,
D.Item_Description,
D.Generic_Description,
D.Item_Class,
D.User_Category_1,
sum(case when month(D.DOCDATE) =
month(DATEADD(m, -12, GETDATE()))
and year(D.DOCDATE) = year(DATEADD(m, -12, GETDATE()))
then D.SALES else 0 end) as [Sales_12_mo_ago],
sum(case when month(D.DOCDATE) =
month(DATEADD(m, -11, GETDATE()))
and year(D.DOCDATE) = year(DATEADD(m, -11, GETDATE()))
then D.SALES else 0 end) as [Sales_11_mo_ago],
sum(case when month(D.DOCDATE) =
month(DATEADD(m, -10, GETDATE()))
and year(D.DOCDATE) = year(DATEADD(m, -10, GETDATE()))
then D.SALES else 0 end) as [Sales_10_mo_ago],
sum(case when month(D.DOCDATE) =
month(DATEADD(m, -9, GETDATE()))
and year(D.DOCDATE) = year(DATEADD(m, -9, GETDATE()))
then D.SALES else 0 end) as [Sales_9_mo_ago],
sum(case when month(D.DOCDATE) =
month(DATEADD(m, -8, GETDATE()))
and year(D.DOCDATE) = year(DATEADD(m, -8, GETDATE()))
then D.SALES else 0 end) as [Sales_8_mo_ago],
sum(case when month(D.DOCDATE) =
month(DATEADD(m, -7, GETDATE()))
and year(D.DOCDATE) = year(DATEADD(m, -7, GETDATE()))
then D.SALES else 0 end) as [Sales_7_mo_ago],
sum(case when month(D.DOCDATE) =
month(DATEADD(m, -6, GETDATE()))
and year(D.DOCDATE) = year(DATEADD(m, -6, GETDATE()))
then D.SALES else 0 end) as [Sales_6_mo_ago],
sum(case when month(D.DOCDATE) =
month(DATEADD(m, -5, GETDATE()))
and year(D.DOCDATE) = year(DATEADD(m, -5, GETDATE()))
then D.SALES else 0 end) as [Sales_5_mo_ago],
sum(case when month(D.DOCDATE) =
month(DATEADD(m, -4, GETDATE()))
and year(D.DOCDATE) = year(DATEADD(m, -4, GETDATE()))
then D.SALES else 0 end) as [Sales_4_mo_ago],
sum(case when month(D.DOCDATE) =
month(DATEADD(m, -3, GETDATE()))
and year(D.DOCDATE) = year(DATEADD(m, -3, GETDATE()))
then D.SALES else 0 end) as [Sales_3_mo_ago],
sum(case when month(D.DOCDATE) =
month(DATEADD(m, -2, GETDATE()))
and year(D.DOCDATE) = year(DATEADD(m, -2, GETDATE()))
then D.SALES else 0 end) as [Sales_2_mo_ago],
sum(case when month(D.DOCDATE) =
month(DATEADD(m, -1, GETDATE()))
and YEAR(D.DOCDATE) = year(DATEADD(m, -1, GETDATE()))
then D.SALES else 0 end) as [Sales_1_mo_ago]
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 -- SOP header
INNER JOIN
SOP30300 SD -- SOP lines
ON SD.SOPNUMBE = SH.SOPNUMBE
AND SD.SOPTYPE = SH.SOPTYPE
LEFT OUTER JOIN
IV00101 I -- item master
ON I.ITEMNMBR = SD.ITEMNMBR
WHERE SH.VOIDSTTS = 0 -- not voided
AND SH.SOPTYPE IN (3,4) -- only invoices and returns
AND SD.XTNDPRCE <> 0 -- excludes zero price
AND SD.ITEMNMBR not like 'XXXXXXXXXXXXXXX%'
) 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_Rolling_12_Mo_Sales_by_Item 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 have been a big Fan of your views for several years now.
This one is the only that I haven’t been able to tweak to my needs.
Is there a way to generate the same report, but split by location code?
LikeLike
Hi Stephane,
I just published a new blog post this morning that does this: Rolling 12 months of sales by item by site.
-Victoria
LikeLike
Hey Victoria,
First off fantastic code. I have been working on tweaking this for my purchasing team to get a custom column to show the average # of items sold per month over the last 6 months. Using your code I can display the number of items sold per month… This information however is already available in the item maintenance > history window. I wanted to save them some time by creating a column that will take the 6 other columns and average them. Any idea on how I could accomplish this? I’ve tried using a longer sum statement
sum(case when
month(D.DOCDATE) = month(DATEADD(m,-2,GETDATE()))
OR month(D.DOCDATE) = month(DATEADD(m,-3,GETDATE()))
OR month(D.DOCDATE) = month(DATEADD(m,-4,GETDATE()))
OR month(D.DOCDATE) = month(DATEADD(m,-5,GETDATE()))
OR month(D.DOCDATE) = month(DATEADD(m,-6,GETDATE()))
OR month(D.DOCDATE) = month(DATEADD(m,-7,GETDATE()))
AND year(D.DOCDATE) = year(DATEADD(m, -2,GETDATE()))
THEN D.SALES else 0 end) /6 AS [Monthly AVG],
This however pulls a number far larger than I would expect. I think the year constraint is having issues but I just can’t pin it down. Thank you for any help you can provide
LikeLike
Nevermind! I finished it finally, for anybody interested here is the working version.
sum(case when
OR
(month(D.DOCDATE) = month(DATEADD(m,-3,GETDATE()))
AND year(D.DOCDATE)= year(DATEADD(m,-3,GETDATE())))
OR
(month(D.DOCDATE) = month(DATEADD(m,-4,GETDATE()))
AND year(D.DOCDATE)= year(DATEADD(m,-4,GETDATE())))
OR
(month(D.DOCDATE) = month(DATEADD(m,-5,GETDATE()))
AND year(D.DOCDATE)= year(DATEADD(m,-5,GETDATE())))
OR
(month(D.DOCDATE) = month(DATEADD(m,-6,GETDATE()))
AND year(D.DOCDATE)= year(DATEADD(m,-6,GETDATE())))
OR (month(D.DOCDATE) = month(DATEADD(m,-7,GETDATE()))
AND year(D.DOCDATE)= year(DATEADD(m,-7,GETDATE())))
THEN D.SALES else 0 end) /6 AS [Monthly AVG],
LikeLike
Hi Victoria,
I hope you are well.
I am once again plagued by our various unit of measures and am trying to make a report that summarizes the item sales by item quantity and unit of measure (from SOP) for the past year.
I can’t seem to get the sale quantity summarized for the past year and appreciate any insight you can provide. I probably should have started with your Sales Quantities by Items by Year view however I was trying to make a solution that wouldn’t require someone with access to SQL to modify it for additional years as necessary.
SELECT
D.ITEMNMBR Item_Number,
D.Item_Description,
D.Generic_Description,
D.Item_Class,
D.UOFM,
sum(case when year(D.DOCDATE) = year(DATEADD(y, -1, GETDATE()))
then D.SALES else 0 end) as [SalesLast12Months] –I was thinking this would get the sales in the past year summarized
FROM
(SELECT SH.DOCDATE, SD.ITEMNMBR,
coalesce(I.ITEMDESC, SD.ITEMDESC) Item_Description,
coalesce(I.ITMGEDSC, ”) Generic_Description,
coalesce(I.ITMCLSCD,”) Item_Class,
coalesce(SD.UOFM,”) UOFM, –Changed from UserCategory
case SD.SOPTYPE
WHEN 3 THEN SD.QUANTITY –Changed from extended price
WHEN 4 THEN SD.QUANTITY*-1
END SALES
FROM SOP30200 SH — SOP header
INNER JOIN
SOP30300 SD — SOP lines
ON SD.SOPNUMBE = SH.SOPNUMBE
AND SD.SOPTYPE = SH.SOPTYPE
LEFT OUTER JOIN
IV00101 I — item master
ON I.ITEMNMBR = SD.ITEMNMBR
WHERE SH.VOIDSTTS = 0 — not voided
AND SH.SOPTYPE IN (3,4) — only invoices and returns
AND SD.ITEMNMBR = ‘P214’ — just a good item for me to test against remove before creating view
AND SD.ITEMNMBR not like ‘XXXXXXXXXXXXXXX%’
) D
GROUP BY D.ITEMNMBR, D.Item_Description,
D.Generic_Description, D.Item_Class, D.UOFM
Thank you,
Kirk
LikeLike
Hi Kirk,
I would recommend putting the date selection into the where clause in D, that way, instead of getting ALL the data first, then summarizing based on the dates you want, you’re only getting the data you need, then summarizing it. If you don’t have a huge amount of data, it should not be a big difference, but once you have a large dataset you’re reporting against, you will get much better performance this way.
I wasn’t sure if you want the sales for the past year or a rolling 12 months of sales? Your formula seems to suggest the former, but your description suggests the latter.
If you’re looking to get all sales for last year (ie, it’s now 2015, show all of 2014), try this:
SELECT
D.ITEMNMBR Item_Number,
D.Item_Description,
D.Generic_Description,
D.Item_Class,
D.UOFM,
sum(D.SALES) as [SalesLastYear] --I was thinking this would get the sales in the past year summarized
FROM
(SELECT SH.DOCDATE, SD.ITEMNMBR,
coalesce(I.ITEMDESC, SD.ITEMDESC) Item_Description,
coalesce(I.ITMGEDSC, '') Generic_Description,
coalesce(I.ITMCLSCD,'') Item_Class,
coalesce(SD.UOFM,'') UOFM, --Changed from UserCategory
case SD.SOPTYPE
WHEN 3 THEN SD.QUANTITY --Changed from extended price
WHEN 4 THEN SD.QUANTITY*-1
END SALES
FROM SOP30200 SH -- SOP header
INNER JOIN
SOP30300 SD -- SOP lines
ON SD.SOPNUMBE = SH.SOPNUMBE
AND SD.SOPTYPE = SH.SOPTYPE
LEFT OUTER JOIN
IV00101 I -- item master
ON I.ITEMNMBR = SD.ITEMNMBR
WHERE SH.VOIDSTTS = 0 -- not voided
AND SH.SOPTYPE IN (3,4) -- only invoices and returns
AND SD.ITEMNMBR = 'P214' -- just a good item for me to test against remove before creating view
AND SD.ITEMNMBR not like 'XXXXXXXXXXXXXXX%'
and year(SH.DOCDATE) = year(getdate() -1) --last year
) D
GROUP BY D.ITEMNMBR, D.Item_Description,
D.Generic_Description, D.Item_Class, D.UOFM
If you want the last 12 months (so if today is 2/25/2015, you’d get all sales AFTER 2/25/2014), change the line with the comment ‘–last year’ to the following:
and SH.DOCDATE >= DATEADD(yy, -1, GETDATE()) --rolling 12 months
Hope that helps,
-Victoria
LikeLike
Sorry, my first attempt did not format the comments in SQL correctly…I just updated it, so it will be correct on the blog if you want to copy from there.
-Victoria
LikeLike
Thank you so much Victoria. This will work great for us.
In looking at your original view I am puzzled why this piece is necessary.
AND SD.ITEMNMBR not like ‘XXXXXXXXXXXXXXX%’
I tested it without this section and didn’t have any unexpected results. Care to enlighten me?
Thanks again,
Kirk
LikeLike
Hi Kirk,
Good catch! Sometimes when alignment forms are printed in GP and not correctly cleared out, you get transactions in the system with X’s for all the fields. It does not happen often, but I have one customer that I’ve done a lot of reporting for that has these, so I got used to putting that clause in my code. I don’t think it would hurt anything for most companies to take that out. 🙂
-Victoria
LikeLike
Greetings… Love the query – thanks! I tweaked it by moving most of the date logic to the inner query. It does require SQL 2008 or above because of the CAST(ABC AS DATE) but that can be replaced with CAST(DATE, FLOOR(CAST(FLOAT, ABC))) for SQL 2005 and prior.
You end up with a much smaller query… I just hope the formatting stays in tack 🙂
Enjoy!
John
CREATE VIEW view_Rolling_12_Mo_Sales_by_Item
AS
SELECT
Item_Number,
Item_Description,
Generic_Description,
Item_Class,
User_Category_1,
SUM(CASE WHEN Months = 11 THEN SALES ELSE 0 END) AS Sales_12_mo_ago,
SUM(CASE WHEN Months = 10 THEN SALES ELSE 0 END) AS Sales_11_mo_ago,
SUM(CASE WHEN Months = 9 THEN SALES ELSE 0 END) AS Sales_10_mo_ago,
SUM(CASE WHEN Months = 8 THEN SALES ELSE 0 END) AS Sales_9_mo_ago,
SUM(CASE WHEN Months = 7 THEN SALES ELSE 0 END) AS Sales_8_mo_ago,
SUM(CASE WHEN Months = 6 THEN SALES ELSE 0 END) AS Sales_7_mo_ago,
SUM(CASE WHEN Months = 5 THEN SALES ELSE 0 END) AS Sales_6_mo_ago,
SUM(CASE WHEN Months = 4 THEN SALES ELSE 0 END) AS Sales_5_mo_ago,
SUM(CASE WHEN Months = 3 THEN SALES ELSE 0 END) AS Sales_4_mo_ago,
SUM(CASE WHEN Months = 2 THEN SALES ELSE 0 END) AS Sales_3_mo_ago,
SUM(CASE WHEN Months = 1 THEN SALES ELSE 0 END) AS Sales_2_mo_ago,
SUM(CASE WHEN Months = 0 THEN SALES ELSE 0 END) AS Sales_1_mo_ago
FROM
(
SELECT DATEDIFF(“mm”, SOP30200.DOCDATE, CAST(DATEADD(“dd”, -DATEPART(“dd”,GETDATE()),GETDATE()) AS DATE)) AS Months,
SOP30300.ITEMNMBR AS Item_Number,
ISNULL(IV00101.ITEMDESC, SOP30300.ITEMDESC) Item_Description,
ISNULL(IV00101.ITMGEDSC, ”) Generic_Description,
ISNULL(IV00101.ITMCLSCD,”) Item_Class,
ISNULL(IV00101.USCATVLS_1,”) User_Category_1,
CASE SOP30300.SOPTYPE
WHEN 3 THEN SOP30300.XTNDPRCE
WHEN 4 THEN SOP30300.XTNDPRCE*-1
END SALES
FROM SOP30200
JOIN SOP30300 ON SOP30300.SOPNUMBE = SOP30200.SOPNUMBE
AND SOP30300.SOPTYPE = SOP30200.SOPTYPE
LEFT JOIN IV00101 ON IV00101.ITEMNMBR = SOP30300.ITEMNMBR
WHERE SOP30200.VOIDSTTS = 0 — not voided
AND SOP30200.SOPTYPE IN (3,4) — only invoices and returns
AND SOP30300.XTNDPRCE 0 — excludes zero price
AND SOP30300.ITEMNMBR NOT LIKE ‘XXXXXXXXXXXXXXX%’
— Only look at orders from the first day of this month minus one year, through the last day of the previous
AND SOP30200.DOCDATE BETWEEN CAST(DATEADD(“yy”, -1, DATEADD(“dd”, 1-DATEPART(“dd”,GETDATE()),GETDATE())) AS DATE)
AND CAST(DATEADD(“dd”, -DATEPART(“dd”,GETDATE()),GETDATE()) AS DATE)
) LastYearOfSalesData
GROUP BY
Item_Number,
Item_Description,
Generic_Description,
Item_Class,
User_Category_1
GO
GRANT SELECT ON view_Rolling_12_Mo_Sales_by_Item TO DYNGRP
LikeLike
It is helpful,Thanks.
LikeLike