1,320
Hi all! I built a smart list telling me the cost and sell price of each product and how many have been sold over the course of the item. How would I break thise down to how many were sold in a given year, 2012, for example, while still giving me the other same information? As always, thanks a lot!
Jason
*This post is locked for comments
Hi Jason,
Cost is recorded in the SOP30200 table, so if it is recorded there correctly, you should be able to get it from there - you can just change this code to look at the cost field instead of the quantity.
However, sometimes what is recorded as cost in the SOP tables is not accurate, so you may need to have a discussion with someone (maybe your GP Partner?) about how data is entered and what you are looking to report on and whether it is possible.
This worked! I believe my final question is on the item cost. ! Is there a way to get the breakdown of actual cost for items? We switched from Standard to Actual costing and it seems more difficult to get what makes up the cost (i.e. material, labor, overhead, etc.) I would need this to be available on a month by month basis so that I know what made up material, labor, and overhead costs for that month on each item. Is this too far fetched? I figured GP must be totaling the current cost from Item Price List from somewhere but I am not sure how to ask it.
Hi Jason,
You can add the line below before the FROM in the code:
,sum(case when year(D.DOCDATE) = 2013 and month(D.DOCDATE) = 1 then D.Qty else 0 end) as [Jan_2013_Qty]
Hopefully that will show you the pattern you can use to code other months. If you want every month for only 1 year, you can take a look at this view for an example of that: victoriayudin.com/.../sql-view-for-sales-by-item-by-month Basically, you would specify the year in the WHERE clause and then grab the individual months in your select statements. Hope that helps.
Hi Victoria,
I hate to bug you again about this as you have been more help than I ever could have imagined. However, can this report be broken out on a monthly basis? For January, 2013, for example. Can I get the same information but monthly instead of yearly? Thanks for everything!
Jason
You are amazing Victoria! This worked like a charm! I can't thank you enough!!!!!!!!!!
Thanks Jason,
So looks like you actually have more items than prices, however, that still does not ensure no more than 1 price per item. :-) You should be able to use the query in my previous post to update the SQL view to include what you need.
select count(*) TotalItems from IV00101 - RESULT 2,987
select count(*) TotalPrices from IV00108 - RESULT 1,554
Jason,
You can update the view you had already created using to code below. This will add the current cost and the latest added price per item (if there is more than one price added at the same exact time, it will use the first one). Then I would recommend creating a new SmartList using just this view and not adding any other GP tables.
alter view view_Sales_Qty_by_Item_by_Year
as --*********************************************************************************** --view_Sales_Qty_by_Item_by_Year --Created Jan 23, 2012 by Victoria Yudin - Flexible Solutions, Inc. --Updated Feb 14, 2013 to add --For updates see http://victoriayudin.com/gp-reports/ --Returns total sales quantities fulfilled (invoices - returns) for each item by year --Only posted invoices and returns are included --Quantity is calculated by multiplying by QTYBSUOM column in case other UofM's 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 D.ITEMNMBR Item_Number, D.Item_Description, coalesce(D.CURRCOST,0) Current_Cost, coalesce(P.UOMPRICE,0) Sell_Price, sum(case when year(D.DOCDATE) = 2000 then D.Qty else 0 end) as [2000_Qty], sum(case when year(D.DOCDATE) = 2001 then D.Qty else 0 end) as [2001_Qty], sum(case when year(D.DOCDATE) = 2002 then D.Qty else 0 end) as [2002_Qty], sum(case when year(D.DOCDATE) = 2003 then D.Qty else 0 end) as [2003_Qty], sum(case when year(D.DOCDATE) = 2004 then D.Qty else 0 end) as [2004_Qty], sum(case when year(D.DOCDATE) = 2005 then D.Qty else 0 end) as [2005_Qty], sum(case when year(D.DOCDATE) = 2006 then D.Qty else 0 end) as [2006_Qty], sum(case when year(D.DOCDATE) = 2007 then D.Qty else 0 end) as [2007_Qty], sum(case when year(D.DOCDATE) = 2008 then D.Qty else 0 end) as [2008_Qty], sum(case when year(D.DOCDATE) = 2009 then D.Qty else 0 end) as [2009_Qty], sum(case when year(D.DOCDATE) = 2010 then D.Qty else 0 end) as [2010_Qty], sum(case when year(D.DOCDATE) = 2011 then D.Qty else 0 end) as [2011_Qty], sum(case when year(D.DOCDATE) = 2012 then D.Qty else 0 end) as [2012_Qty], sum(case when year(D.DOCDATE) = 2013 then D.Qty else 0 end) as [2013_Qty], sum(D.Qty) Total_Qty FROM (SELECT SH.DOCDATE, SD.ITEMNMBR, coalesce(I.ITEMDESC, SD.ITEMDESC) Item_Description, I.CURRCOST, CASE SD.SOPTYPE WHEN 3 THEN SD.QTYFULFI*QTYBSUOM WHEN 4 THEN SD.QUANTITY*QTYBSUOM*-1 END Qty 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 SD.ITEMNMBR not like 'XXXXXXXXXXXXXXX%') D LEFT OUTER JOIN (SELECT top 1 ITEMNMBR, UOMPRICE, Max(DEX_ROW_TS) LastDate FROM IV00108 GROUP BY ITEMNMBR, UOMPRICE) P ON P.ITEMNMBR = D.ITEMNMBR GROUP BY D.ITEMNMBR, D.Item_Description, D.CURRCOST, P.UOMPRICE GO GRANT SELECT ON view_Sales_Qty_by_Item_by_Year TO DYNGRP
Jason,
Can you please let me know the results of the following SQL queries:
select count(*) TotalItems from IV00101
select count(*) TotalPrices from IV00108
I use the last seel price of the item. In our system there is only one sell price as only one customer buys each part. We set up new part #'s for each customer. I added a Dynamics GP table but I must have messed up the link becasue my smarl list now comes up blank.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (