wave of money

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:

create view view_Item_Qtys_and_Amounts_by_Month
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- Created October 15, 2013 by Victoria Yudin
--     Flexible Solutions, Inc
-- For updates see http://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.

4 Responses to “SQL view for Dynamics GP sales quantities and amounts by item by site by month”

  1. Could you cross the query with a view of Fiscal Periods to match up into the fiscal buckets, rather than fixed monthly periods? That would really help those of us that may run an unconventional fiscal year (i.e. 13 4-week periods).

    Like

    • Hi Micah,

      This is possible, but not something that I am going to do with this particular code on my blog. The way this is set up, I would need to know how many periods to code up front. If you have 13 periods and someone else has 12, it will not be the same code.

      If this is something you need help with, we can help you with that as a consulting project, please let me know.

      -Victoria

      Like

Trackbacks/Pingbacks

  1. SQL view for Dynamics GP sales quantities and amounts by item by site by month : Interesting Findings & Knowledge Sharing - October 15, 2013

    […] Originally posted here: SQL view for Dynamics GP sales quantities and amounts by item by site by month […]

    Like

  2. SQL view for Dynamics GP sales quantities and amounts by item by site by month | Victoria Yudin | DynamicAccounting.net - October 15, 2013

    […] Victoria offers up a SQL view for Dynamics GP sales quantities and amounts by item by site by month […]

    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

Follow

Get every new post delivered to your Inbox.

Join 1,560 other followers

%d bloggers like this: