web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Introducing our New Video Series: Community Spotlight
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Historical "stock ...
Microsoft Dynamics RMS (Archived)

Historical "stock on hand" report

(0) ShareShare
ReportReport
Posted on by Kate Robertson 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

I have the same question (0)
  • Gaurav Nandgaye Profile Picture
    Gaurav Nandgaye 385 on at

    Hi

    Anybody got solution for this.

     

  • lewis1585 Profile Picture
    lewis1585 60 on at

     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

    rsakry@rite.us

  • Dave Durrenberger Profile Picture
    Dave Durrenberger 1,622 on at

    Kate,

    We can create this custom report for you with about 1 days notice.  If you are still in need contact us:

    http://www.syssolutionsllc.com/

  • Community Member Profile Picture
    Community Member on at

    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.

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Introducing our New Video Series: Community Spotlight

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the January Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics RMS (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans