Dynamics GP sales amounts and quantities for rolling 12 months

March 14, 2017 by Victoria Yudin 17 Comments

A popular request that I have seen over the last few months is to combine the sales amount and quantities by item for a rolling 12 months. The view below does this, showing sales amounts as ‘Sales’ and quantities as ‘Qty’, using the data in the Sales Order Processing (SOP) module.

The month names in the columns are generic, but if you’re using something like SSRS or Crystal Reports for your report you can add a formula to the column names to make them more user friendly. Please check the assumptions listed in the view comments (in green below).

Here is a sample of what the result of this view look like:

Related code and table information:

create view view_Rolling_12_Mo_Sales_and_Qty_by_Item
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_Rolling_12_Mo_Sales_and_Qty_by_Item
-- Created Mar 14, 2017 by Victoria Yudin, Flexible Solutions
-- For updates see https://victoriayudin.com/gp-reports/
-- Returns total sales (amount of invoices - returns) and also
--    quantity sold for each item for the last 12 months.
--    Current month is not included, even if this is run on 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
rtrim(d.ITEMNMBR) Item_Number,
rtrim(d.ITEMDESC) Item_Description,
rtrim(d.ITMGEDSC) Generic_Description,
rtrim(d.ITMCLSCD) Item_Class,
rtrim(d.USCATVLS_1) 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, -12, getdate()))
  and year(d.DOCDATE) = year(dateadd(m, -12, getdate()))
  then d.QTY else 0 end) as [Qty_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, -11, getdate()))
  and year(d.DOCDATE) = year(dateadd(m, -11, getdate()))
  then d.QTY else 0 end) as [Qty_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, -10, getdate()))
  and year(d.DOCDATE) = year(dateadd(m, -10, getdate()))
  then d.QTY else 0 end) as [Qty_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, -9, getdate()))
  and year(d.DOCDATE) = year(dateadd(m, -9, getdate()))
  then d.QTY else 0 end) as [Qty_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, -8, getdate()))
  and year(d.DOCDATE) = year(dateadd(m, -8, getdate()))
  then d.QTY else 0 end) as [Qty_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, -7, getdate()))
  and year(d.DOCDATE) = year(dateadd(m, -7, getdate()))
  then d.QTY else 0 end) as [Qty_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, -6, getdate()))
  and year(d.DOCDATE) = year(dateadd(m, -6, getdate()))
  then d.QTY else 0 end) as [Qty_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, -5, getdate()))
  and year(d.DOCDATE) = year(dateadd(m, -5, getdate()))
  then d.QTY else 0 end) as [Qty_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, -4, getdate()))
  and year(d.DOCDATE) = year(dateadd(m, -4, getdate()))
  then d.QTY else 0 end) as [Qty_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, -3, getdate()))
  and year(d.DOCDATE) = year(dateadd(m, -3, getdate()))
  then d.QTY else 0 end) as [Qty_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, -2, getdate()))
  and year(d.DOCDATE) = year(dateadd(m, -2, getdate()))
  then d.QTY else 0 end) as [Qty_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],
sum(case when month(d.DOCDATE) =
  month(dateadd(m, -1, getdate()))
  and year(d.DOCDATE) = year(dateadd(m, -1, getdate()))
  then d.QTY else 0 end) as [Qty_1_mo_ago]

from
(select sh.DOCDATE, sd.ITEMNMBR,
 coalesce(i.ITEMDESC, sd.ITEMDESC) ITEMDESC,
 coalesce(i.ITMGEDSC, '') ITMGEDSC,
 coalesce(i.ITMCLSCD,'') ITMCLSCD,
 coalesce(i.USCATVLS_1,'') USCATVLS_1,
 case sd.SOPTYPE
     when 3 then sd.XTNDPRCE
     when 4 then sd.XTNDPRCE*-1
     end SALES,
 case sd.SOPTYPE
     when 3 then sd.QTYFULFI*QTYBSUOM
     when 4 then sd.QUANTITY*QTYBSUOM*-1
     end QTY
 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 sh.DOCDATE >= dateadd(d, -1,
		dateadd(m, datediff(m, 0,
		dateadd(m, -12, getdate())), 0))
     and sd.ITEMNMBR not like 'XXXXXXXXXXXXXXX%'
     ) d

group by
	d.ITEMNMBR,
	d.ITEMDESC,
    d.ITMGEDSC,
	d.ITMCLSCD,
	d.USCATVLS_1

-- add permissions for DYNGRP
go
grant select on view_Rolling_12_Mo_Sales_and_Qty_by_Item
	to DYNGRP

Dynamics GP, GP Reports code, GP SQL scripts, Inventory SQL code, SOP SQL code

GP Reports code, Inventory, Sales Order Processing, SQL code

Updating Dynamics GP data directly in SQL

SQL view for rolling 12 months of sales by item by site in Dynamics GP

17 Responses to “Dynamics GP sales amounts and quantities for rolling 12 months”

Andy Browning Reply September 15, 2021 at 2:36 pm

HI Victoria…I assume if you want to pull data 13-24 months old (2 years), you would change the following in the where statement:

and sh.DOCDATE >= dateadd(d, -1,
dateadd(m, datediff(m, 0,
dateadd(m, -12, getdate())), 0))

TO

and sh.DOCDATE >= dateadd(d, -1,
dateadd(m, datediff(m, -13,
dateadd(m, -24, getdate())), 0))

Does that sound correct?? thanks!!

LikeLike
- Victoria Yudin Reply September 15, 2021 at 3:08 pm
  
  Hi Andy,
  
  You can either change the first “greater than” to be “less than” or it might be best to do the following:
  
  and sh.DOCDATE between
  
  dateadd(d, -1,
  dateadd(m, datediff(m, -13,
  dateadd(m, -24, getdate())), 0))
  
  and
  
  dateadd(d, -1,
  dateadd(m, datediff(m, 0,
  dateadd(m, -12, getdate())), 0))
  
  -Victoria
  
  LikeLike
Daniel Ng Reply March 3, 2020 at 12:23 pm

Hi Victoria,

Great post! Using this as designed! Very appreciated!

My question, is there any possibility that the column name like (Sales_12_mo_ago), be dynamically changed as for example “March 2020” and etc.?

LikeLike
- Victoria Yudin Reply March 4, 2020 at 6:57 am
  
  Hi Daniel,
  
  I believe there is a way to do this, but it’s complicated and I have never found anything on it except people strongly advising against doing this.
  
  The better approach to achieve this is to use a reporting product, for example SSRS or Crystal Reports, with this SQL code. When you do that, you can add formulas in the report to show the actual month names/dates.
  
  -Victoria
  
  LikeLike
Sunny Reply December 20, 2017 at 11:16 am

Thanks, Victoria for your nice posts and sharing knowledge for GP Community. I learned a lot from your blog. I really appreciate your work.

LikeLike
- ogilviedrewgmailcom Reply January 21, 2019 at 5:32 pm
  
  Hi Victoria: This twelve months rolling usage history is really fantastic. I’m new to GP and its report writer, so I’m hoping you will tell me how I can incorporate this, or the sales by month numbers, into the canned Purchase Advice Report. I’ve added a relationship to the Inventory Sales Summary Period Table for the IV Purchase Advice TEMP table, but this new relationship doesn’t show when i go to add it in the report definition. Any help, including suggestions on learning resources will be greatly appreciated!
  Thanks,
  Drew
  
  LikeLike
  - Victoria Yudin Reply January 22, 2019 at 7:54 am
    
    Hi Drew,
    
    In most cases adding something like this to an existing GP report is not going to work. You will probably need to create a new custom report that combines the logic of the 2 reports or sets of code.
    
    -Victoria
    
    LikeLike
Matt Cullen Reply May 9, 2017 at 2:08 pm

Hi Victoria,

How many hops will it be to go from GP2010 SP2 to GP2016 R2?

I know there is a hope from GP2010 SP2 to SP4, but can we go right from SP4 to GP2016 R2?

Thanks

Matt

Matt Cullen
Customer Account Manager/ Microsoft GP Solutions Architect
Dynamic Methods Inc. | Cell: 310.710.2959 | Fax: 714.464.4704
9841 Irvine Center Drive Suite 200 Irvine, CA 92618 |Microsoft President’s Club 2010
mcullen@dynamicmethods.com

LikeLike
- Victoria Yudin Reply May 9, 2017 at 2:30 pm
  
  Hi Matt,
  
  No, the most you can ever skip is one full version. I would probably go from GP 2010 to GP 2013, then to GP 2016.
  
  -Victoria
  
  LikeLike
Qadeer Ahmed Reply April 17, 2017 at 4:32 am

Excellent Support for the Dynamics GP Community from Victoria Yudin, Thanks for your nice posts and sharing knowledge, hats off to you, God Bless you for your hard work and dedication.

LikeLike
Devin McMahon Reply March 15, 2017 at 7:08 pm

Victoria, can I ask a quick question I am in a bind. Is there a trick to “unmarking” all the options on “Include in Lookup” on Account Maintenance on GP 2013 +?

Sent from Mail for Windows 10

LikeLike
- Victoria Yudin Reply March 17, 2017 at 8:59 am
  
  Hi Devin,
  
  Are you looking to do this in SQL? I don’t see any issue with that. Those are all in the GL00100 table.
  
  -Victoria
  
  LikeLike
Satyendra Reply March 14, 2017 at 10:50 am

Thanks, Victoria. You have been wonderful for the community. Personally, I have learnt a lot from your blogs, books, and comments. Your blogs have helped me to become a better GP consultant over a period of time. Even today, when I look for GP table, I search for your post. I love your work. Some day, I would like to meet you and thank you for your contribution.

LikeLike
- Victoria Yudin Reply March 14, 2017 at 10:59 am
  
  Thanks Satyendra, I really appreciate your kind words!
  
  -Victoria
  
  LikeLike