SQL view for Dynamics GP sales quantities and amounts by item by site by month

Here is a new view created by request for one of my blog readers. This view combines my Sales by Item by Month and Sales Quantities by Item by Site by Month views. It will return columns for each month with the quantity and sales amount side by side. The year is hard-coded to be the current year (when the view is run), but can be changed as needed. I am making a number of assumptions (listed in the view comments in green), and there are also overall yearly total columns at the end.

Some additional resources:

• Sales Order Processing (SOP) SQL views
• Sales Order Processing (SOP) commonly used tables
• Other GP Reporting links

create view view_Item_Qtys_and_Amounts_by_Month
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- Created October 15, 2013 by Victoria Yudin
--     Flexible Solutions, Inc
-- For updates see https://victoriayudin.com/gp-reports/
-- Returns total sales fulfilled (SOP invoices less
--     returns) for each item by month
-- Calendar months and Document Dates are used
-- Only posted invoices and returns are included
-- Quantity is calculated by multiplying by QTYBSUOM column
--     in case other UofMs are used on transactions
-- 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
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

select
rtrim(d.ITEMNMBR) Item_Number,
rtrim(d.Item_Description) Item_Description,
rtrim(d.Generic_Description) Generic_Description,
rtrim(d.Item_Class) Item_Class,
rtrim(d.User_Category_1) User_Category_1,
rtrim(d.LOCNCODE) Site_ID,
sum(case when month(d.DOCDATE) = 1
    then d.Qty else 0 end) as Jan_Qty,
sum(case when month(d.DOCDATE) = 1
   then d.Sales else 0 end) as Jan_Amount,
sum(case when month(d.DOCDATE) = 2
    then d.Qty else 0 end) as Feb_Qty,
sum(case when month(d.DOCDATE) = 2
   then d.Sales else 0 end) as Feb_Amount,
sum(case when month(d.DOCDATE) = 3
    then d.Qty else 0 end) as Mar_Qty,
sum(case when month(d.DOCDATE) = 3
   then d.Sales else 0 end) as Mar_Amount,
sum(case when month(d.DOCDATE) = 4
    then d.Qty else 0 end) as Apr_Qty,
sum(case when month(d.DOCDATE) = 4
   then d.Sales else 0 end) as Apr_Amount,
sum(case when month(d.DOCDATE) = 5
    then d.Qty else 0 end) as May_Qty,
sum(case when month(d.DOCDATE) = 5
   then d.Sales else 0 end) as May_Amount,
sum(case when month(d.DOCDATE) = 6
    then d.Qty else 0 end) as Jun_Qty,
sum(case when month(d.DOCDATE) = 6
   then d.Sales else 0 end) as Jun_Amount,
sum(case when month(d.DOCDATE) = 7
    then d.Qty else 0 end) as Jul_Qty,
sum(case when month(d.DOCDATE) = 7
   then d.Sales else 0 end) as Jul_Amount,
sum(case when month(d.DOCDATE) = 8
    then d.Qty else 0 end) as Aug_Qty,
sum(case when month(d.DOCDATE) = 8
   then d.Sales else 0 end) as Aug_Amount,
sum(case when month(d.DOCDATE) = 9
    then d.Qty else 0 end) as Sep_Qty,
sum(case when month(d.DOCDATE) = 9
   then d.Sales else 0 end) as Sep_Amount,
sum(case when month(d.DOCDATE) = 10
    then d.Qty else 0 end) as Oct_Qty,
sum(case when month(d.DOCDATE) = 10
   then d.Sales else 0 end) as Oct_Amount,
sum(case when month(d.DOCDATE) = 11
    then d.Qty else 0 end) as Nov_Qty,
sum(case when month(d.DOCDATE) = 11
   then d.Sales else 0 end) as Nov_Amount,
sum(case when month(d.DOCDATE) = 12
    then d.Qty else 0 end) as Dec_Qty,
sum(case when month(d.DOCDATE) = 12
   then d.Sales else 0 end) as Dec_Amount,
sum(d.Qty) Total_Qty,
sum(d.Sales) Total_Amount

from
(select sh.DOCDATE, sd.ITEMNMBR, sd.LOCNCODE,
 coalesce(i.ITMGEDSC,'') Generic_Description,
 coalesce(i.ITMCLSCD,'') Item_Class,
 coalesce(i.USCATVLS_1,'') User_Category_1,
 coalesce(i.ITEMDESC, sd.ITEMDESC) Item_Description,
 case sd.SOPTYPE
     when 3 then sd.QTYFULFI*QTYBSUOM
     when 4 then sd.QUANTITY*QTYBSUOM*-1
     end Qty,
 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 year(sh.DOCDATE) = year(getdate()) --change as needed
     and sd.ITEMNMBR not like 'XXXXXXXXXXXXXXX%') d

group by d.ITEMNMBR, d.Item_Description, d.LOCNCODE,
 d.Generic_Description, d.Item_Class, d.User_Category_1

go
grant select on view_Item_Qtys_and_Amounts_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.