The Store Operations database is processing transactional data in real-time. If you need print valuation reports for a specific period you should run those reports on the dates required. The best solution is install Feature Pack 1 with the latest service pack and hot fixes. This version (2.0.0150 and above) includes an Inventory Valuation Report by date. See Manager, Reports, Items, Inventory Valuation, enter start/ending dates such as 1/1/2014 to 12/31/2014. Please note this report has problem reported handing historical data for users who update from eariler versions of RMS and issues handling adjustments. If the report results do not show valid data, use Method 1 below to extrapolate the data required instead.
If you are running an earlier release of RMS you should make a date-coded backup of your database file and use that for producing historical reports. We suggest you create an archive backup at the end of every month for accounting and record purposes.
If you have not made historical backups and are running an older version of RMS, or the Inventory Valuation Report by Date is not working, you can use the following method to extrapolate inventory value for a prior date.
The example below would compute the Inventory cost on 12/31 of last year. Use the same method for the ending date you are interested in, entering start/stop dates for the data you need to make adjustments for.
Method 1:
1. Run an Items | Value List Report. This will run the report as of the current date. Use the collapse icon to summarize data by dept.
2. Run an Items | Item Movement Report. Set your Filter Field to 'Date' and Range to 'This Year-to-Date' and generate the report. IMPORTANT: Depending on version of RMS you may need to run the Inventory Movement History Report instead listed under Custom.
3. If you need to add Department or Category columns to the report, right click on the report and select 'Hide/Unhide Columns'
4. Place a checkmark next to the column name you would like to add, most likely Department or Category, depending on which column you want to sort by and click OK.
In order to sort by this column, click and drag the column to the first column position. Next click on the Group by drop down box on the top of the report and select 'Group First Column' to make sure that you are grouping by your selected column.
5. Subtract or add the total extended inventory cost per Department (or Category) in the second report from the inventory cost on the first report to get the inventory cost as of 12/31 last year. The adjustment value may be negative or positive depending on movement type. For example, more sales than received stock would generate a negative value.
Method 2:
Make a backup of your database on specific dates, such as the last day of the year or first day of each month. Example: 12_31_yyyy.bck could be thename of the backup file created at the end of last year. Use Administrator to create/restore and connect to the historical database, then run Manager reports off this file. Make sure you to connect back to your active database when finished.
If you need to run reports like this on a monthly basis, schedule a Windows task to backup your data on a monthly basis, such as the 1st of every month. To create a batch file for making backups, search the KB for "backup".
WARNING: You should create an archive backup at the end of every month in case you need to run historical reports in the future. Example: 12-31-2012.bck This backup should be stored in safe place such as a CD-R that is kept off-premises or cloud drive storage. This also protects you in case of system crashes, fire, or theft.
To restore an archived backup, complete the following steps.
IMPORTANT: These instructions apply to a default RMS installation using MSDE (RMS 1.x) or SQL 2005 Express (RMS 2.0). If you are deploying the full version of SQL Server on your new server, the procedure will differ based on the tools available in the full version of SQL. However, you can always use the following method to restore a backup file as long as the backup file is named with the .bck extension and the version of SQL between the old and new server are compatible. Example: SQL Server 2000 to MSDE, or SQL Server 2005 to SQL 2005 Express. Also keep in mind MSDE is limited to 2G, SQL 2005 Express is limited to 4G, and SQL Express 2008 R is limited to 10G. If your database is larger than these limits, you'll need to install the full version of SQL on your new server.
On your RMS server (or any PC where SQL Server is installed)
------------------------------------------------------------------------------------------
Copy the backup file you want use to the local drive first.
Run Store Operations Administrator.
Select File, Connect, and make sure Server is set to (local).
Enter your SQL password and click OK.
Select Database, Restore, and create a new database using the backup file under whatever database name you want to use. Example: EOMJan08
Once restored, select File, Configuration, and make sureServer is setto (local) and set the Database name to the name you used for your restored database. WARNING: Write down the original values!
Click Test Database then OK, OK.
Now run Manager and run any reports you need off the historical database.
When done, exit Manager and change the server name and database name back to the original settings in Administrator, File, Configuration. Click Test Database, then OK, OK before exiting.
Dave J
Comments