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


By popular request I have created a variation on my rolling 12 months of sales by item to show a rolling 12 months of sales by item and site ID in Dynamics GP. This view will return the last 12 months, not including the current month. So if you’re running this today – April 20, 2017 – it will show you April 2016 through March 2017. The names of the monthly columns are generic – if you’re using a reporting tool with this, you can use formulas to have more meaningful column names. Please check the view comments to see other assumptions I am making.

Related code and table information:

create view view_Rolling_12_Mo_Sales_by_Item_Site
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_Rolling_12_Mo_Sales_by_Item_Site
-- Created Apr 20, 2017 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
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,
rtrim(d.LOCNCODE) Site_ID,
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, sd.LOCNCODE,
 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
 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%'
   and sh.DOCDATE > dateadd(d, -1, dateadd(m, datediff(m, 0, 
       dateadd(m, -12, getdate())), 0))
) d

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

-- add permissions for DYNGRP
go
grant select on view_Rolling_12_Mo_Sales_by_Item_Site 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.

3 Responses to “SQL view for rolling 12 months of sales by item by site in Dynamics GP”

  1. Another awesome view! I just used it as part of a data set in Power BI worked great.
    Thank you.

    Like

  2. Stephane Jodouin Reply April 20, 2017 at 3:58 pm

    It works perfectly, thank you so much for this update!
    This will drastically improve our forecast tracking

    Like

  3. Blair Christensen Reply April 20, 2017 at 1:37 pm

    Just tried this out using SSRS and am going to post it for internal comments. Thanks!

    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: