50
My accountant needs a report showing the total value of all stock on hand as at 30 June 2009. I can't figure out how to generate a report in RMS that shows me this information. Can anyone help?
*This post is locked for comments
We use a view to calculate current qoh +/- changes in sales and inventory transferlog from that date:
create view item_move as
select t.storeid as storeid, i.categoryid as catid, sum(e.quantity*e.cost) as sold, sum(0) as qoh, sum(0) as trans
from [transaction] t join transactionentry e on t.storeid=e.storeid and t.transactionnumber=e.transactionnumber
join item i on e.itemid=i.id where time>'5/30/09' group by t.storeid, i.categoryid
union all
select d.storeid as storeid, i.categoryid as catid, sum(0) as sold, sum(snapshotquantity*snapshotcost) as qoh, sum(0) as trans from itemdynamic d join item i on d.itemid=i.id group by d.storeid,i.categoryid
union all
select l.storeid as storeid, i.categoryid as catid, sum(0) as sold, sum(0) as qoh, sum(l.quantity*l.cost) as trans
from inventorytransferlog l join item i on l.itemid=i.id where datetransferred>'5/30/09' group by l.storeid,i.categoryid
Then the query to extract that data, here shown grouped by store and category
SELECT item_move.storeid, item_move.catid, Sum(qoh+sold-trans)
FROM item_move GROUP BY storeid, catid ORDER BY storeid, catid
This then is put in an excel file so it can be used for a pivot table to show category by store and totals.
Kate,
We can create this custom report for you with about 1 days notice. If you are still in need contact us:
I was needing the same thing. Ryan can do what you are needing
Tell him that Michael @ Tony Stewart Racing sent you!
Ryan Sakry
Program Manager
Hi
Anybody got solution for this.
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,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (