Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Incorrect Item Costs o...
Microsoft Dynamics RMS (Archived)

Incorrect Item Costs on Reports Compared to Database Item Costs

(0) ShareShare
ReportReport
Posted on by Christopher.Taylor 215

Hello,

I use the Inventory Validation Report in Headquarters Manager in Dynamics Retail Management System to display sales and results for given days. However, the costs of many of my items shown on the report are not the actual costs of these items stored in the database as viewed in the items list. I have also ran a SQL query in order to validate what is shown in the items list, and the actual contents of the database are correct. This means that something is probably wrong with the Inventory Validation Report.

Would you know how to solve this problem? I have been looking at the source code (.qrp file) in the reports folder. Maybe there is some kind of pointer to the cost field that is grabbing the wrong value. There is no resemblance between the cost in the report and the database; completely random. (I realize this is outdated software but a client of mine still uses it)

I also realize that this issue happens for the cost summary report as well, not just the inventory validation.

Thanks so much!

Chris

P.S. I accidentally posted this on the CRM forum before, so ignore it if you see this on there, thanks!

*This post is locked for comments

  • Christopher.Taylor Profile Picture
    Christopher.Taylor 215 on at
    RE: Incorrect Item Costs on Reports Compared to Database Item Costs

    Spencer, I applied your tips and the SnapShot values are now correct, thanks so much! However, it is able only to apply to the records from this point forward, not previously. I would like to be able to make them update for previous dates as well, since the old costs are incorrect. When I did the worksheets, I set the effective date to back before the items were incorrect so that it would change things from that day forward. Unfortunately, the items for previous dates are still incorrect... Any ideas?

    Thanks so much again!

    Chris

  • Christopher.Taylor Profile Picture
    Christopher.Taylor 215 on at
    RE: Incorrect Item Costs on Reports Compared to Database Item Costs

    Spencer,

    I realized that I didn't properly sync my databases after I did these worksheets. Getting somewhere now. Thanks a bunch!

  • Christopher.Taylor Profile Picture
    Christopher.Taylor 215 on at
    RE: Incorrect Item Costs on Reports Compared to Database Item Costs

    Spencer,

    I tried this, and the SnapShot Costs still did not update for the store I selected. I ran 302 and then set the cost for the specific items group to what I wanted (Granted it said that the old cost and the new cost were the same correct value) and hit Approve. I then did the 401 and made sure to hit Approve. When I ran the Inventory Validation again, the item in the category I updated still reflected the incorrect value, different from what I had just updated it to.

    Is there another place I should look to update this cost value?

    Thanks so much for your help I really appreciate it!

    Chris

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,085 on at
    RE: Incorrect Item Costs on Reports Compared to Database Item Costs

    Sure. I believe the Snapshot fields are updated every time a store connects and processes a Worksheet 401: Data Upload, so the value you are seeing in the snapshotcost field is the cost that is set at the store level, which in general should be more reflective of item valuation. If changing the cost at the store level was unauthorized, or you would otherwise like to overwrite store cost information with cost information from Headquarters, you can do this by processing a Worksheet 302 for all items without any cost adjustments entered (which may take a while depending on the size of your database). Once this is done, I believe the Snapshot field should populate with the cost value the next time each store processes a 401.

  • Christopher.Taylor Profile Picture
    Christopher.Taylor 215 on at
    RE: Incorrect Item Costs on Reports Compared to Database Item Costs

    Spencer,

    Thanks for the reply! After some more research yesterday, I found that these types of reports pull the Cost value from the SnapShotCost field in the ItemDynamic Table. I went into SQL and found that the SnapShotCosts are different from the "Cost" field, which actually has the correct values. Now I need to either figure out how to update the SnapShotCost so it reflects the "Cost" field, or have the report pull its values from the "Cost" field instead of SnapShotCost. Any recommendations?

    Thanks!

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,085 on at
    RE: Incorrect Item Costs on Reports Compared to Database Item Costs

    Hi Chris,

    The Inventory Valuation report pulls from a table called #TempEachDate that it builds by calling a procedure in the heading of the report. Unfortunately, this procedure doesn't seem to be stored in the database, and I think it is coded into RMS. Consequently, I'm not sure what tables the program queries to build #TempEachDate. If I had to guess based on context, though, I would think that it was pulling cost information from the transactionentry table rather than the item table, which would contain the item cost at the time of the purchase rather than the current item cost.

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

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans