Dynamics GP sales amounts and quantities for rolling 12 months


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:

sales and qty

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

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

  1. 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

    Like

  2. 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.

    Like

Trackbacks/Pingbacks

  1. Dynamics GP sales amounts and quantities for rolling 12 months | Victoria Yudin - DynamicAccounting.net - Dynamics GP Users - DUG - March 14, 2017

    […] Dynamics GP sales amounts and quantities for rolling 12 months | Victoria Yudin […]

    Like

  2. Dynamics GP sales amounts and quantities for rolling 12 months | Victoria Yudin - Microsoft Dynamics GP Community - March 14, 2017

    […] Dynamics GP sales amounts and quantities for rolling 12 months | Victoria Yudin […]

    Like

  3. DynamicAccounting.net | Dynamics GP sales amounts and quantities for rolling 12 months | Victoria YudinDynamics GP sales amounts and quantities for rolling 12 months | Victoria Yudin - DynamicAccounting.net - March 14, 2017

    […] Dynamics GP sales amounts and quantities for rolling 12 months | Victoria Yudin […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: