Archive for May, 2009
Historical Inventory Trial Balance Report
Microsoft Dynamics GP recently came out with a new feature that would provide GP users that are utilizing the Inventory Module the ability to create a Historical Inventory Trial Balance report. It goes without saying that any company maintaining an inventory has had reconciliation issues between the valuation in the Inventory Module and the General Ledger control account or accounts. The HITB features will allow the following:
• The Historical Inventory Trial Balance report enables users to tie the Inventory Sub-ledger to the General Ledger, regardless of Inventory Valuation Method.
• This report will show the inventory value as of a specific date or date range, and the value of the five different quantity types (On Hand, In Use, Returned, In Service and Damaged). Keep in mind that the HITB report will only report on inventory balances from the installation date forward. So if you want to use it in the future, install it as soon as you can.
• To utilize the HITB Report, existing customers will need to apply Dynamics GP 10.0 SP2 and run a wizard-driven IV HITB Reset Tool. The IV HITB Reset tool and the Historical Inventory Trial Balance Report are both components of Dynamics GP 10.0 SP2. The wizard-driven IV HITB Reset Tool and the initial reconciliation is complex, and you may want help with this from your Microsoft Dynamics GP VAR.
• New Customers who begin utilizing Dynamics GP 10.0 after SP2 releases or existing customers who create new companies AFTER SP2 has been applied, will not have to run the HITB IV Reset Tool. They will be able to utilize the Historical Inventory Trial Balance report immediately. If you had inventory prior to SP2, you must run the wizard.
You can obtain more information about the HITB and the install utility at:
In presenting demos of Microsoft Forecaster over the past years, I have always avoided using the import tools to get data into Forecaster. However for the demo I presented last week I wanted to import actual data from Dynamics GP and comma delimted data from MS Project, so I could show our prospect how Forecaster could be used to compre actual and plan data for a project oriented developer.
As it turned out, it was easy to do.
For Dynamics GP data, navigate to: Tools >> Import >> ExpressLink. This will start the ExpressLink Wizard that will direct you to login into FRx and Dynamics GP. The next process is to set up a mapping of the accounts and time periods from Dynamics GP, to Forecaster. This map can be reused. Then you just start the import and the data rolls into your column layouts. Easy.
For the MS Project data, I used the Data Import screen which one gets to by navigating to: Tools >> Import >> Data. This will create a batch of “transactions” that will then be “posted” by Forecaster. The process will have you create a mapping of accounts and time periods; that can be saved for future use.
These two processes were easy to learn and ran without problems.
When I get more time, I’m going to mess around with the “Import Detail Data” import to see how that works.
The client thanked me profusely and said they never heard of Microsoft giving anything away for free. I agreed but told them it’s not exactly free as I would charge them for my time which ended up being 3 hours when all was said and done.
Here is the process I used to update the client from MSDE to SQL Server Express Edition 2005:
- Call MBS Sales Ops ((800) 456-0025) and get SQL keys for GP. These should be free but if you don’t have them GP will give you nasty error messages. Do this first. I actually did this last and it took a day to get the stupid keys. When you have the keys go to step 2.
- Download SQL Server Express Management Studio 2005
- Install the SQL Server Express Management Studio (Do this first if you want to use it to create your backups.)
- Make a backup of the company and Dynamics databases
- Run Capture login script on current install of MSDE. See Tech doc 878449. Script is found here.
- Download SQL Server Express Edition 2005
- Install SQL Server Express Edition. Mixed mode. (You may need to allow remote connection in SQL setup if it doesn’t default). You can install SQL in one of 2 ways. Upgrade the existing instance of MSDE. (I tried this and it failed on the upgrade). Or install a new instance (Default instance is SQLEXPRESS, call it whatever you like). You will then need to restore the Dynamics and Company databases to the new instance.
- Run the results of step 5 against the new instance of SQL to recreate your users.
- Put in new reg keys into Dynamics that you got in Step 1. Dyn utilities on any version pre 10.0. In version 10.0 you will need to run this script (delete sy003500) then enter reg keys in application. See tech doc 943965
- Update ODBC to point to new instance of SQL
This is similar to the procedure of moving SQL to a new server.