Convergence 2009 Booth Babe

I'm going to Convergence this year and will probably be in our Rose ASP booth frequently throughout the expo days. Don't think I get the official title of Booth Babe like Mark Polino does but will be sure to smile lots and flex my muscles if you ask me too. We are in booth 1333 and usually have some good swag for everyone to take home to the kiddies.

Would love to meet anyone that reads these blogs. I am convinced I'm writting for my own personal enjoyment and would love to be proven otherwise.

No beginning balances in FRx balance sheet

I should have included this in my last post about the most common technical support questions. Usually around this time of year I get a rash of calls about how Dynamics is broken. Seems like all the balance sheet accounts don't have beginning balances even though their P and L have correct numbers.

Dynamics has been this way since the beginning of time. You will not have beginning balances until you close out the previous year (P and L accounts start with $0 each year so they are not effected). I know what you're thinking. "I'm not ready to close out the year yet". I usually have 2 responses to that objection.

1. You can still post entries to the GL to the most recent historical year. So even if you close out the year you can still make your audit adjustments etc. If you do choose this option be aware that the entry will freak you out. It will look like it posted the entry twice. The first is the original entry, the second is the system automatically adjusting the beginning balances for the open year. The second entry is always on the last day of the fiscal year (say 12/31/20xx). If you made your entry in the historical year on 12/31/20xx it will look like it's duplicating. No worries. That's what the system is supposed to do.


2. Do some fancy formatting in FRx. I would recommend a column layout similar to the below screen shot (click on picture for a closer look). The first 2 GL columns would be non printing columns. The first GL column give you YTD numbers for the 12th period of the previous year. The second column gives you YTD numbers for the current year. Column D adds both together.

You would probably want to save this as a seperate column layout so you can adjust back the original layout after you close out the year.

Tricky way to allow you to get a BS and procrastinate your year end close.

Dynamics GP Top Technical Support issues

I was asked what issues I deal with the most often. I'm sure there are many others but these are the first ones that come to mind along with the resolutions below.

Here's my Top 13 support issues list in no particular order:
1. Batch stuck in posting, receiving, etc. status
2. Where do I find the table that holds a particular set of data?
3. My sub-module doesn’t tie to the GL
4. How do I set up a new fiscal year?
5. I can’t login to Dynamics – Unknown dictionary error
6. How do I import budgets?
7. Dynamics won’t let me post
8. I have a metrics error on my homepage
9. How do I move Dynamics to a new server?
10. After closing the GL I realized I have incorrectly listed a BS account as a P & L account, now there is not beginning balance. (or vice versa)
11. How do I adjust then print 1099’s?
12. When trying to access sales transaction entry I get the error "Your previous transaction-level posting session has not finished processing".
13. Inventory shows it is allocated when it’s not

Resolutions:

1. Doug Pitcher blog post - http://www.rosebizinc.com/gpblog/2009/01/stuck-batches-in-dynamics-gp.html. Dave Musgrave comment gives script that works well.
2. Blog post by MBS employee Dave Musgrave. http://blogs.msdn.com/developingfordynamicsgp/archive/2008/10/05/finding-table-and-field-information-in-microsoft-dynamics-gp.aspx
3. See Steve Chapman post. http://www.rosebizinc.com/gpblog/2008/02/reconcile-to-gl-easily.html
4. Tools>>setup>>system>>company>>fiscal periods. Type new fiscal period in the year box and press tab. Make sure all periods are correct then press calculate.
5. Usually permissions error when network ID doesn’t have access to reports.dic file. Find path in Tools>>setup>>system>>edit launch file>>enter system password>>Highlight dynamics GP, look to see where Reports line is pointing to. Open a browser and try to path to this location. Need
6. Cards>>financial>>budgets. Use import wizard by choosing New>>using budget wizard for Excel.
7. Print edit list of batch (printer icon at top right hand corner of batch window). This will tell you why you can’t post
8. Tech doc: 918313. Install Office Web Components link, question and answer #21.
9. Doug Pitcher blog: http://www.rosebizinc.com/gpblog/2008/08/moving-sql-to-new-server.html
10. Tech doc #864913
11. Doug Pitcher blog: http://www.rosebizinc.com/gpblog/2009/01/dynamics-gp-1099-printing-for-90-and.html
12. Tech doc #852623. Run scripts to release captured user.
13. Run inventory reconcile at Tools>>utilites>>inventory>>reconcile. Everyone needs to be out of GP (well sop, pop, inv or any module that touches inventory)

I'd be interested in seeing your most common lists of frequently tackled issues in GP. Any issues come to your mind other than the ones above?

Office Smart Tags for Dynamics GP

The other day, I presented a demo of GP to a prospective client. While showing SmartLists, and exporting to Excel, I showed the Smart Tag feature. They really liked it!

Most of our clients don't use it, but it's a good way to extend the GP data beyond the GP users group, and it's accessible through Excel and Word.


Here's a summary of the feature.

There are seven Microsoft Dynamics GP smart tags. The following table
lists the smart tags and the types of data recognized in Microsoft Dynamics
GP applications.



This is a section of the Excel spreadsheet created from the SmartList export. You can see that in Excel, the values the Smart Tag Manager has identified as Dynamics GP information are indicated with a small mark in the lower, right hand corner of the cell (Smart Tag indicator).

This is a customer list, so if you click on the Smart Tag actions button you will get these options; a selection of special "view" screens, GP inquiry screens, or SmartLists.

This is an example of a view item.

Select a View menu item to retrieve related information from a Microsoft
Dynamics GP company database and display the information in a separate
viewer window. Four categories of information can be displayed for all data
recognized and labeled by Microsoft Dynamics GP smart tags.
• Master record
• Summary
• Transaction
• Note

The other two types of information are:

Open items
Select an Open menu item to open a Microsoft Dynamics GP inquiry,
maintenance, or transaction window related to the data that has been
recognized and labeled.

Open SmartList items
Select an Open SmartList menu item to open the Microsoft Dynamics GP
SmartList window and view search results related to the data that has been
recognized and labeled.

The software and documentation are available on CustomerSource.

The Achilles' Heel of Dynamics GP

Reports.dic is the Achilles' Heel of GP. I often refer to the reports.dic file as such for two reasons:

1.) The file can become easily corrupted
2.) A corrupted reports.dic file can cause mystifying application failures and error messages

The most debilitating failure is that GP will not launch. You will get Dexterity error messages, which will likely cause you to spend 1 to 3 hours troubleshooting the problem.

The reports.dic file is one of the first files that is loaded when GP is launched. If there is a problem, the launch fails. And it doesn't inform you that the reports file is the problem.

The easiest solution is to locate and rename the reports.dic file.

The complete solution is three steps:

• Export resources from the current Reports.dic file
• Rename the existing Reports.dic file
• Import resources into the new Reports.dic file

Knowledge Base article # 850465 contains detailed instructions on how to complete these three tasks.

Deploy Excel Reports to Dynamics GP

Version 10 of Dynamics GP includes 190+ Excel reports that connect directly to GP data. They are included in the basic install, but need to be deployed. There are two steps to deploying Excel Reports:

Deploy Data Connections

1. Open the Reporting Tools Setup window.
(Microsoft Dynamics GP menu >> Tools >> Setup >> System >> Reporting
Tools Setup)

2. On the Data Connections tab, specify a shared network location where data
connections will be stored. Data connections will be organized by company and
module in the folder that you specify.

3. Mark Enable data connection deployment.
You must have access to the server to deploy reports to a shared network location.

4. Mark Deploy data connections to all existing companies if you want to deploy
data connections for all companies.
If you leave this option unmarked, you will have to log in to each company
separately to deploy data connections for those companies.

5. Choose Run Deployment to save your changes and deploy data connections to
the location that you specified.


Deploy Excel Reports

1. Open the Reporting Tools Setup window.
(Microsoft Dynamics GP menu >> Tools >> Setup >> System >> Reporting
Tools Setup)

2. On the Report Library tab, specify a shared network location where reports will
be stored. Excel reports will be organized by company and module in the folder
that you specify.

3. Mark Enable Excel report deployment.

You must have access to the server to deploy reports to a shared network location.

4. Mark Deploy Excel reports to all existing companies if you want to deploy Excel
reports for all companies at the same time.
If you leave this check-box unmarked, you will have to log in to each company
separately to deploy Excel reports for that company.

5. Choose Run Deployment to save your changes and deploy Excel reports to the
location that you specified.


The Excel reports will now show up on the Administration tab, under "Other Reports"

For a more authoritative source, check out Chapter 30 in systemsetup.pdf

Also check out KnowledgeBase article #949524

Also see my post, "Using Excel Report Builder"

And for similar treatments of the same subject see Mark Polino's article and Professional Advantage's article.

Revenue / Expense Deferral

The Revenue / Expense Deferral module in Dynamics GP is one of three options we regularly use to assist our clients in managing their deferred revenue. The other two are Project Accounting and Contract Administration.

The deferral module is the easiest to use, is quite flexible, and provides a solid report option that ties out to the balance sheet account(s) you use to hold deferred amounts.

The deferral module allows you to defer amounts from a specific GL distribution. The GL distribution can be from the GL, RM, PM,SOP, Invoicing, or POP module. Essentially what the module does, is automatically create GL entries into the future, to match the deferral schedule.

The deferral schedule can be created from scratch each time you need to create one, or you can use a Deferral Profile that can specify, the length of deferral, GL accounts used, start date, etc.

Below is the result of a deferral profile being "allocated" to a GL distribution for a $50,000 software sale. The sale is being deferred evenly out, over three years.

In this case, when the invoice is posted, the credit amount for the sale will go to a balance sheet account, '2115', instead of a sales account. At posting, the deferral module will create 36 GL transactions spanning the next 36 months, that will debit the "Deferral Account", and credit the "Revenue Recognition" account.

This is what the GL distribution entry looks like. In this case, the deferral profile is setup to match the "SALES" transaction type, and the GL account, "000-2115-00".

This is the control report that should match the deferral account(s) you have set up. What's nice about this report is that you can run it for any date, and it should match the GL amounts for that date. To match to the deferral module report, you would want to make sure that the only transactions in the account(s) were those generated by the deferral module.

The Revenue / Expense Deferral module can be used to control various deferral situations. If you're not using it, and have some deferred revenue or expense accounting issues, you will want to check it out.

Dynamics GP 10.0 year end close issue - 'GL_account_SUM_HIST_View'

Ran into this yesterday and thought I'd post it as I didn't see any tech docs anywhere on it.

A client updated from Dynamics GP 8.0 to 10.0 in October. The client was just closing out the 2008 year but ran accross the following error message near the end of the close just before the report should print.

A get/change first operation on table 'GL_account_SUM_HIST_View'. Followed by "Number of results columns doesn't match table definition" after you click OK.

All the rows had moved from GL20000 to GL30000. The fiscal year period setup was not updated to be a historical year. Neither was the last close date adjusted to the new year in the GL Year End Close window under routines.

Here is the recommended approach to fix this issue:

1. Restore backup before year end close was attempted


2. Go to Dynamics GP>>Maintenance>>SQL


3. Choose the company database


4. Highlight Account Summary Historical View


5. Check off all boxes to the left (See screen shot below)


6. Run Tools>>Utilities>>Financial>>Reconcile on all years, starting with oldest first


7. Rerun year end close routine
8. Verify all BS balances have update and all P & L accounts have closed out to RE

If you don't have a backup to restore to you should be ashamed of yourself. Here is an alternative approach I used at first until I decided I'd be more conservative and restore from a backup first.

1. Back up company database
2. Check off historical year in the fiscal period setup window
3. Update LSTCLSDT in GL40000 to be date desired (12/31/2008 in our case). Script would be something like Select * from GL40000. Should only be one row in table. If so then use this script: UPDATE GL40000 SET LSTCLSDT = '12/31/2008'.
4. Go to Dynamics GP>>Maintenance>>SQL
5. Choose the company database
6. Highlight Account Summary Historical View
7. Check off all boxes to the left
8. Run Tools>>Utilities>>Financial>>Reconcile on all years, starting with oldest first
9. Rerun year end close routine
10. Verify all BS balances have update and all P & L accounts have closed out to RE

This second approach doesn't give you the normal report after the year end close since all the rows are already moved to GL30000.

Don't know what caused the issue. Guessing it's because of the upgrade. Maybe the GL10111 table got corrupted somehow. It happened on every company until we did the above process.

Sales Forecast to MRP

This happens everytime I try to include a Sales Forecast in MRP. I cannot find the MRP Forecasting Selections screen. I know exactly what I'm looking for, but for me it has proven to be the most elusive screen in the whole system.

You need to get to this screen if you want to include one or more sales forecasts into the MRP regeneration process.

And here is the shy little screen:



So how do you get to it? This is one way, if not the only way, to get to it. Navigate to: Transactions >> Manufacturing >> Forecasting >> Sales Forecasting. Click on the "GoTo" dropdown and select, "Forecasting Selections", and voila!

This drives me crazy every time. I invested 20 minutes to it this morning while running around the house to get the "parade" moving, and could not find it. I finally had to ask our manufacturing guy.

Does anyone else have this problem?

Project Accounting - Miscellaneous Logs

Miscellaneous Logs is one of the defined transaction types in Dynamics GP -Project Accounting. When I discuss or demonstrate Misc. Logs with prospects and clients, they usually look at me in a way that indicates there is no way they would ever use that functionality. However we have clients who have used this transaction type extensively and effectively.

An example is a client that performs engineering inspections for large residential home builders. They perform inspections for roofs, foundations, windows, etc, for every home in new large housing developments. They charge their clients for each type of inspection on each new home, so there is a lot of data.

Before GP, they were collecting data in Timeslips, and manually entering into QuickBooks.

This is how they used Project Accounting:

• New projects were set up in Project Accounting so that each inspection type, on each new house was represented by a unique Cost Category. This process was automated using eConnect.
• Each Cost Category had a Miscellaneous Log budget that represented the inspection type, the quantity of inspections (usually 1), and the price, e.g. $115. The advantage of this was that they had significant detail to indicate to their clients exactly what the clients were being billed for.
• On a daily basis newly performed inspections were being imported from a field time gathering system using eConnect, and individual Miscellaneous Log transactions were being created for the specific Cost Categories.
• On a periodic basis the client would run the Cycle Billing routine to create invoices for all the inspections that had been performed.

Because the Cost Category budgets were set up for each inspection type and house, the home builders were not being billed twice for the same work, and our client now knew exactly what was not being billed. As it turned out; after using GP for several months, our client realized that they had previously been under-billing their clients by a lot. And because the whole process was automated, the effort involved in billing, decreased.

This is an example of how one client used Miscellaneous Logs to solve their business issues, and we have others who have used the Project Accounting transaction types in amazing ways.

In the case cited, eConnect was crucial to automate the project set up and transaction creation process. Otherwise the sheer volume of work would have killed the project. Because of the automation, the GP users at this client hardly interact with Project Accounting at all, for transactions and setup.