You’re offline. This is a read only version of the page.
3,225
Hello,
I have a report that I need to build in report builder. I have the majority, but I am not sure what tables I am missing. I know I will need to link current and history so I am not sure this can be done. ( I would assume it can) I was hoping if I gave a list of the broad fields I need someone could tell me what tables I should pull in. Once I have all of that I am pretty certain I could finish it. So here it goes and thank you for any advice!!
ITEM #
ITEM CLASS
QTY ON HAND
QTY ON PO
CURRENT MONTH QTY SOLD
CURRENT MONTH COST
CURRENT MONTH SALES
LAST YEARS MONTH QTY SOLD
LAST YEARS MONTH COST
LAST YEARS MONTH SALES
CURRENT YTD QTY SOLD
CURRENT YTD COST
CURRENT YTD SALES
LAST YEARS YTD QTY SOLD
LAST YEARS YTD COST
LAST YEARS YTD SALS
LAST YEARS QTY SOLD
LAST YEARS COST
LAST YEARS SALES.
I know the basic tables for the item, class codes, etc, but I get confused when I am trying to combine history with open.
*This post is locked for comments
Katie,
This would take combining parts of a number of different views and additional tables. It's certainly doable, but I don't have anything like this created at this point. But see if the code below gets you a little closer to what you need, you should hopefully be able to use this example to add the additional columns of data you need:
select d.ITEMNMBR Item_Number,
coalesce(i.ITEMDESC, d.ITEMDESC) Item_Description,
coalesce(i.ITMCLSCD,'') Item_Class,
d.LOCNCODE Site_ID,
sum(case when month(d.DOCDATE) = month(getdate()) and year(d.DOCDATE) = year(getdate()) then d.Qty else 0 end) Current_Mo_Qty,
sum(case when year(d.DOCDATE) = year(getdate()) then d.Qty else 0 end) Current_Yr_Qty,
sum(case when month(d.DOCDATE) = month(getdate()) and year(d.DOCDATE) = year(getdate()) then d.Amt else 0 end) Current_Mo_Amt,
sum(case when year(d.DOCDATE) = year(getdate()) then d.Amt else 0 end) Current_Yr_Amt,
coalesce(q.QTYONHND,0) Qty_on_Hand,
coalesce(q.QTYONORD,0) Qty_on_Order
from
(select sh.DOCDATE, sd.ITEMNMBR, sd.LOCNCODE, sd.ITEMDESC,
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 Amt
from SOP30200 sh
inner join SOP30300 sd
on sd.SOPNUMBE = sh.SOPNUMBE and sd.SOPTYPE = sh.SOPTYPE
where sh.VOIDSTTS = 0 and sh.SOPTYPE in (3,4) and sd.ITEMNMBR not like 'XXXXXXXXXXXXXXX%') d
left outer join IV00101 i
on d.ITEMNMBR = i.ITEMNMBR
left outer join IV00102 q
on d.ITEMNMBR = q.ITEMNMBR and d.LOCNCODE = q.LOCNCODE
group by d.ITEMNMBR, d.LOCNCODE, i.ITMCLSCD, coalesce(I.ITEMDESC, d.ITEMDESC), coalesce(q.QTYONHND,0), coalesce(q.QTYONORD,0)
I had our partner import a view from Victoria called Sales Qty by item by site by month. I am having trouble figuring out what other view we would need to include all of my data above..... @ Victoria - do you have a view that already includes all the information above, or would I have to combine a few different ones? Thank you for any help provided!!
Katie
"knew"
I would if I had new how....... I will bring this to our GP partner.
Thank you,
Katie
Hi Katie,
Why start from scratch? If you have access to SQL Server Management Studio you can use one of the excellent views written by Victoria Yudin for your Excel Report.
victoriayudin.com/.../sop-sql-views
Kirk
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,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (