Recreating reports.dic in Dynamics GP

Can I come up for air yet? Talk about a busy month. January is always crazy but for some reason it seems this past month has been extra intense. Now that Year end closes are coming less frequent and 1099's, W-2's etc. should be about done maybe I'll be able to call in sick a time or two and head up to The Big.

One common issue I've seen rear it's ugly head is not being able to print reports after applying a SP or Year End update. GP will either crash before the report is done or will error out and not give you a report. Is it me or has SP's and Year End Updates continually gotten more difficult over the years? I don't even waste time with trying to update the Reports.dic file anymore. I go simply to recreating. Here is the process:

Method 1

1. Make a backup of reports.dic
2. Go to Microsoft Dynamics GP>>tools>>customize>>customization maintenance
3. Highlight all reports and choose export. Export this out to a package file
4. Have everyone get out of GP
5. Delete the reports.dic file
6. Log into GP again and go to GP>>tools>>customize>>customization maintenance
7. Choose import and select the package file created in step 3

Method 2

1. Make a backup of reports.dic
2. Delete reports.dic (or rename reports.dic path to reports2.dic in dynamics.set if you don’t want to kick everyone out of GP)
3. Log into GP and go to Microsoft Dynamics GP>>tools>>customize>>report writer
4. Choose Import on the right hand side of the reports menu
5. Browse out to the copy of the reports.dic file created in step 1
6. Insert each report (makes you do it one by one)
7. Choose import
8. If you created a reports2.dic file in step 2 rename the file back to reports.dic

Method 2 allows you to recreate without having everyone get out of GP. However, you most likely will have to get everyone out to rename the file back to reports.dic in step 8 of method 2.

Outsource your Manual Payables and Receivables Functions

This week I was given the opportunity to see the offering from Enliven Software that can help you streamline your payables and receivables functions using Dynamics GP.

Enliven Software has developed a SAAS offering that is very tightly integrated with GP.  They use Dexterity to add buttons in familiar GP screens that seamlessly extend GP functionality for common manual processes, to their hosted solution; adding well thought out and comprehensive automation.

For example:

• Provides automated workflow approvals for payables invoices
• Payables invoice capture from various delivery methods, including e-mail, fax
• Payables invoices can easily be scanned, indexed, and stored using OCR technology
• Send payments via ACH/EFT
• Provides an out sourced service to print, stuff, and mail payables checks
• Send sales invoices and statements via e-mail or fax, and automatically provides reminders to customers
• Accept payments electronically from a variety of payment types
• Provides a customer/vendor portal to improve communication

If you want to streamline your payables and receivables functions, automate current manual processes, and store documents electronically, this is a comprehensive solution.  To take a quick peek at this functionality you should check out the videos on these pages:

Accounts Payable

Accounts Receivable

Pay special attention to the integration with GP.  They’ve done a good job.

Multi-company SmartLists

Here’s a nice little option in SmartList Builder which I’m surprised few people know about.  SmartList Builder allows you to create SmartLists that can report information from multiple companies in one list.

We have several real estate companies and family office clients that have dozens of GP company databases from which they want to report on rather simple things, e.g. all bank accounts, all fixed assets.  SmartLists is perfect for this type of reporting.

To have a SmartList look at multiple companies, go to the “Options” button, check the “Multicompany SmartList” check box, and select the companies you want included.

Here’s an example of a multiple company bank account list:

The problem in this solution is to find a field in the data that identifies the company to which the results belong.  In my example, this is easy because the CM Checkbook Master table includes the Company ID field that can be linked to the Company Master table, to return the name of the company.

I found this query on CodeSnippets to find all the tables in the company database with the column name “CMPANYID”

SELECT table_name=sysobjects.name,
column_name=syscolumns.name,
datatype=systypes.name,
length=syscolumns.length
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE syscolumns.name='CMPANYID'

These are the results:

CT00001
FA49900
CT00002
SOPS113B
RM50103
CT00003
SY00600
POPS0231
JCPS0040
POP10100
PRPF0113
UPR70501
DD40400
QAPS0032
QAPU0032
AAG00906
POPF0113
POPS0132
CO010000
ECPS0000
COSS0401
ECPU0000
ENC40000
ENC40100
RM30701
POP30100
mops0100
MOPS0200
CM00100
EC090000
WCPS0100
MRP0200
BMPF0114
BMPS0114
SVC40500
CAM10210
GTM41000
SY04800
MPPS0230
CAM10200
MPPU0130
WCPF0100
TLPS0100
TLPU0100
RVP0100
RVPS0132
CPO40001
CPO40002
WPPS0114
WPPU0114
PAVW10600
RTPS0130
RTPU0130
PAVW30600

If you have SmartList Builder and several GP company databases, you should try this out.

1099 for Dynamics GP

I've been really impressed with a lot of our clients this year. I usually don't start the 1099 rush until January 30th but I have been getting a lot of calls already about the 1099 process. I did a blog last year about 1099's found here. This is a good overview of the 1099 process.

A couple of comments about the 1099 process that I've come across this year are listed below.

1. I find it easiest to look at the yearly summary (still under Cards>>purchasing>>summary>>yearly tab>>change to calendar year view) and see how much was paid during the year versus the 1099 amount
2. To edit the 1099 amount it will have to be done in the 1099 detail window
3. If you only see zero's on your edit list you need to install SP2 or higher for 10.0
4. If you have a vendor listed as 1099 and it shouldn't be, delete the amounts in the 1099 window before changing the status to be "Not a 1099 Vendor". It will still show up on your 1099's otherwise
5. If you have a vendor listed as dividend and not the usuall miscellaneous you will have to zero out all dividend amounts and replace it in the miscellaneous window in the 1099 detail window
6. If you haven't listed the vendor as a 1099 vendor you will have to edit the amounts in the 1099 detail window as it doesn't go back and calculate for anything previous to the change
7. The minimum amount setting is under payables setup (Microsoft Dyanmics GP>>tools>>setup>>purchasing>>payables>>1099 setup). This used to be in the print 1099 window.
8. If you have a vendor that was paid $600, change the minimum in payables setup to be $599.99 or no 1099 will be printed for that vendor.
9. For 9.0 and before you edit 1099 details in the period view (cards>>purchasing>>summary>>period tab)
10. GP does not produce the 1096 summary form. Go figure.

Anyone else come across 1099 process issues not listed above?

Creating Custom Word Templates using the Letter Writing Assistant

You may have used the standard Letter Writing Assistant templates that come out-of-the-box with GP, that allow you to create Word documents from SmartList data.  There are actually some nice ones that I like to show when I demo GP.

But you can also modify the standard templates, and create your own.:

• To access the Letter Writing Assistant, navigate to:  Reports  >>  Letter Writing Assistant
• Select, “Customize the letters by adding…”
• Select the letter category that corresponds to the SmartList from which you want to pull data
• Select one of the four options available
• Select the document or action in the window:

• Word will then open, with a blank document or the existing document you identified
• Select the “Add-Ins” tab from the Word menu
• Move the cursor to the spot on the Word document, at which you want the added field to appear
• Select the field

• The selected field(s) will then be added to the Word document
• Save the Word document
• Cancel the Letter Writing Assistant

You’re now ready to use the Word template with SmartList.  The template will show up with the appropriate SmartList on the Word drop-down tab.

It’s easy to do.  Try it out.

I recorded a quick video that shows this in action.

Multiple Site MRP Calculations in Dynamics GP

Dynamics GP can easily be used to manage inventory demand and supply for multiple inventory locations. By using separate sales forecasts, the MRP functionality in GP can separately analyze forecasted demand, and suggest manufacturing orders and purchase orders to fulfill the demand.

This video shows this functionality in action.

Adjusting Standard Costs in Inventory Control

Generally our clients who use the standard cost method to value inventory, use the Manufacturing module; and standard costs are easily managed in that module.

However the Inventory Control module can be used to accurately maintain and track standard cost items using the FIFO Periodic and LIFO Periodic Valuation Methods.  Unfortunately the steps to adjust standard costs is a bit quirky.  There is not a dedicated function in GP that allows you to specifically identify the standard cost items that you want to adjust.

There is a function in the year-end close routine for Inventory Control (Tools  >>  Routines  >>  Inventory  >>  Year-End Close) that allows you to automatically adjust standard cost to the current cost for all FIFO Periodic and LIFO Periodic items.  This however does not allow you to select which items to update, what the new standard costs will be, and does not create a journal entry to revalue existing inventory in the GL; so really it’s useless.

The following is my preferred process:

1)  Run a Stock Status report before any changes are made to show the inventory values with existing standard costs.

2)  Change the standard cost for every item you want to change.

3)  Run a Stock Status report after all the changes are made to show the inventory values with the new standard costs.

4)  At this point you could enter a journal entry for the difference in inventory value as reported on the Stock Status reports, and you would be done.

But what I would do, is take the alternative step of adjusting the costs by using the Inventory Adjust Costs screen.  Navigate to this screen:  Tools  >>  Utilities  >>  Inventory  >>  Adjust Costs

Select the items and enter the new standard costs for all receipts, and Process:

This will update the receipt records and print the information you need to create adjusting GL entries. 

If you use the Purchase Receipts report for any of your reporting, you will want to do this step; otherwise the new standard costs will not be in the report.

Temporarily Disable a Product in GP

From time to time I want to disable some functionality in GP in order to make a demo more streamlined (e.g. disable Analytical Accounting) or to test some functionality without a specific product in the system. 

For example, I was recently testing the Inventory Control module to find out how it handles changes in standard costs.  I wanted to disable the Manufacturing module so I could get just the Inventory Control module functionality working in my system.

It’s easy to do.  To temporarily disable a product, navigate to:  Tools  >>  Customize  >>  Customization Status.  This brings up the Customization Status screen.

Select the product you wish to disable, and click the Disable button.  This will disable the associated functionality until you re-launch GP

If you want to disable the product for a longer period of use, check out KnowledgeBase article # 872087

Microsoft Dynamics GP - Year End Payroll Update

The time is at hand for review and consideration of the payroll year end process for Great Plains. This year (as in 2008) the year end update involves a service pack installation. In other words, this year end update will change the Microsoft Dynamics version number and more importantly, it must be installed on the server (where it will update tables and stored procedures), then it must be installed on each workstation running Great Plains so each workstation “synchronizes” with the same version as the server. Depending upon the size of the database(s) and the number of workstations, this can take a few minutes, or several hours.
I am not overly found of how Microsoft deals with the Year End Update, especially since the timing for installation is always difficult at year end (re: holidays, scheduling, vacations, parties, etc, etc). Keep in mind the basic timing required for completing the year end processes successfully.

1. Make sure all 2009 pay runs are complete. This means all payroll checks and direct deposits with a date on or before December 31, 2009 have been run. Many companies will set a date after which no payroll checks are allowed to be prepared. Another way of saying this is don’t fire anyone after the cut-off date so you don’t have to provide a final check. Also, don’t wait till the last minute to issue bonus or holiday checks. Anyone still remember those?
2. Install the Year End Update. Remember, this is a Service Pack and can take time. It will probably involve the IT Department or your friendly neighborhood Great Plains consultant (all of which have holidays, vacations, parties and such on their schedules). It is also a good idea to have your Accounting Department verify all is well after the Year End Update install (accountants don’t generally have holidays, vacations and parties as they like to work all the time and don’t have many friends).
3. Create the Year End Wage File. This is the file that your W2s are dependent on. If this file is not created successfully your W2s will not be available for distribution. Not having W2s ready for distribution to employees can get really, really ugly, not to mention how the IRS feels about missing distribution deadlines (I have heard stories about them coming to get first born children after a deadline is missed, but I am pretty sure they are just stories).
4. Setup fiscal periods for 2010. This is pretty easy and has probably been done already, in fact it can be accomplished at any time. If you have rent checks due on January 1st, you have found out you cannot enter the invoice for payment without the 2010 fiscal year in place.
5. Install the 2010 Payroll Tax Update. This is not the same as the Year End Update. The Year End Update is nasty, this one is easy (at least for now it is, maybe Microsoft will change that next year). Once you install the 2010 Payroll Tax Update, you can run your first pay run for 2010. One thing to keep in mind is that the “Calculate Checks” function is when the new 2010 tax tables are accessed. You must install the 2010 Payroll Tax Update before pushing the button for Calculate Checks. If you run a 2010 pay run before creating the year end file, you are screwed (unless you have a backup and then all you have to do is restore the backup, reinstall the last 2009 payroll tax updates, create the year end wage file, reinstall the 2010 payroll tax update and then re-run your 2010 pay run). You really, really don’t want to do this, so make sure someone that can at least walk and chew gum at the same time is keeping track of things.

If you read the Payroll year-end checklists from Microsoft (and I highly recommend that you do so) you will find that they have 17 steps compared to my 5. Some of their stuff is optional (such as closing the 2009 fiscal year or deleting inactive employees). Other items are just plain not possible like “Step 12 Prepare and submit W-2 information in the federal EFW2 format, if required.” No one submits this stuff until it is nearly due and it has been reviewed, scrubbed and reviewed again, that is unless you are not fearful of discussing non reconciling items with the IRS, but then again maybe you don’t much like your first born child.

If you haven’t guessed by now, the payroll year end close can be somewhat intimidating if this is your first time. There are several backups listed in Microsoft’s checklists. Don’t try to save time by not doing the backups. If you have backups, your “you know what” can be saved. Without backups, I am not sure. Your boss just may shoot you.

Simple Dashboards Using GL Summaries and Excel Reports for Dynamics GP

This video shows how to quickly create a dashboard that pulls information from the Dynamics GP general ledger and displays it as three separate graphs on one worksheet.

This is a good example of how to take a large amount of transactional data, summarize it, and present it in a graphical manner.

SmartList Export Solutions in Dynamics GP

If you’re using SmartLists on a regular basis to report on information in Dynamics GP, you’ll want to learn how to use the Export Solutions functionality within SmartLists to add charts, pivot tables, calculated fields, and additional formatting to your data.

Export Solutions allows you to run an Excel spreadsheet macro on the data that SmartLists exports to Excel.

These are the basic steps to set up the macro:

• Export a SmartList to Excel
• Start and name a new macro in Excel
• Perform the formatting you want in the Excel spreadsheet
• Stop the macro
• Delete any worksheets you may have added during formatting
• Delete the data that was initially exported to Excel
• Save the Excel spreadsheet as a template

These are the steps to attach the spreadsheet with macro to SmartLists:

• Navigate from SmartList:  SmartList  >>  Export Solutions
• Document: Select the Excel spreadsheet that contains the macro you wish to execute
• Works for Favorites: Select the SmartList(s) you want the Export Solution to be available with.

Once the Export Solution has been attached to the SmartList(s), you can use it when you wish.  Just make sure to select it from the menu drop down, and not “Quick Export”:

I’ve created a companion video to this on YouTube.

Technorati Tags: Export Solutions,Dynamics GP,SmartList

Make Use of the Source Document Field on Journal Entries

The Source Document field is a required field on a General Ledger journal entry but people rarely pay any attention to the field because it defaults to “GJ” for “General Journal”, which meets the basic criteria.  GJ is certainly acceptable for a General Ledger journal entry but you could expand on that code to make it more  meaningful and therefore provide another option for reporting transaction detail.  The Source Document field is a field you can place a print/search restriction on for standard GL reports, GL transaction inquiries, and smartlists.  Therefore, if you do a number of journal entries for the same purpose/event, you could assign them the same Source Document and then later do a search/print restriction on that specific Source Document code.  For example, if you have to do some post year-end close journal entries, you could create/assign a source document of “GJ-PCADJ” for “General Journal – Post Close Adjustment” to those journal entries.  Subsequently, you could easily run a smartlist of all the post closing adjustments by doing a search of Source Document = GJ-PCADJ.  You can create as many Source Document codes as you like.  I recommend that you start each new code with “GJ” in case you should want to run a report/smartlist on all journals that originated in the GL, you could easily pull all those that start with “GJ”.

 

To create a new Source Document code, you can type it into the Source Code field on the Transaction Entry window:

Dynamics GP will then prompt you to add it:

Enter the appropriate title and click the Save button:

 

Please note:  If you select/view an un-posted journal entry that has any Source Document other than GJ (for example, CMTRX, PMTRX, PMCHK, RMSLS, etc), do NOT change the source document.  These source document codes indicate that the journal entry originated from a subsidiary module and from which subsidiary module.  Also, when you are entering a General Ledger journal entry, do NOT use these pre-defined source documents, because that would make your journal entry misleading as to where it originated from.  If you are unsure which Source Documents are pre-defined and which ones are custom, you can view the pre-defined ones on the Audit Trails Setup window (Tools>Setup>Posting>Audit Trails).

The Confusing Name/Title of the Distribution Reference Field

Every now and then, I find that a Dynamics GP user wants to show the Distribution Reference field on a smartlist or on a modified/custom report but they weren’t able to because they couldn’t find the field to add it.  For some strange reason, the title of this field is, “Description”.  So the next time you want a smartlist of GL account transactions with the Distribution Reference field displayed, add “Description” as a column.  And, you can change the column header to prevent any further confusion by typing in “Distribution Reference” in the display name area:

Removing Discontinued Items during Inventory Year-end in Dynamics GP

As a part of the Inventory year-end process in Dynamics GP, there is an optional feature to remove discontinued items.  Although this option may sound like a nice “house-cleaning” feature, proceed with caution!  Not only is the item removed from the Inventory Master file, all of the inventory historical transactions for that item will be gone too.

If this box is marked when the Inventory year-end process is performed, any item that meets the following criteria will be removed from the Inventory Item Master file:

1.  Item Type = Discontinued

2.  0 quantity on hand

3.  Not on any un-posted inventory, PO, or sales transaction

The reports and windows within the Inventory module will show as if the items removed never existed.  You will be able to see the items on the historical sales and purchase order transactions but you won’t be able to see them anywhere in the Inventory Module.  If you recently discontinued and item, you may still want to see the inventory activity for that item for a while.  In which case, you would NOT want to select this remove option.  A better/safer method would be to manually delete a discontinued item sometime well after you stopped selling/using it and you are sure you no longer need/want to view it in the Inventory module.  (When you are sure you’re ready, you would bring up the discontinued item on the Item Maintenance window and click the Delete button.)

I discuss this closing feature along with many other Dynamics GP year-end closing features in our “Prepare for Year-End Processing” class.  The class is a half day, information-packed class held numerous times in December.  Checkout the training schedule on our website at http://www.rosebizinc.com/training/classes.asp for exact dates and times.  Register soon, year-end will be here before you know it!

When to unmark the Account Entry Checkbox

A few versions ago, Microsoft added a little checkbox to the the Account Maintenance window in Dynamics GP called “Allow Account Entry”.  Even though this checkbox has been around for a while, I’ve found that many Dynamics GP users do not know what it is for and how to make use of it.  If this checkbox is marked, users can manually select/enter the account on any transaction in Dynamics GP.  If this checkbox is NOT marked, users CANNOT enter/select the account on any transaction throughout Dynamics GP; the account can only be used on a default basis.  Therefore, you can use this feature to prevent users from entering accounts that never should be manually updated.  By default, the checkbox is marked.  I recommend that the box be unmarked for Accounts Payable, Account Receivable, Accrued Purchases accounts and possibly inventory. 

If you’ve ever had the unfortunate experience of spending hours reconciling a General Ledger control account to the corresponding subsidiary and finding that the difference in the balances was due to transactions on which the control account was manually entered, you will welcome the opportunity to stop users from entering such transactions.  If there is an unusual circumstance in which the subsidiary is right and the GL is wrong, you can temporarily mark the checkbox, enter/post the correcting entry, then unmark the checkbox. 

 

Happy Reconciling!

Dynamics GP 10.0 Year end update

I see the year end update has been released for Dynamics 10.0.

Is it that time of year already?

Looks like SP 4 will be installed with the year end update if that hasn't been installed already. Probably be a good time to make sure you are up to date on SP's to speed up the year end update process.

Dynamics GP User Group Meeting

On October 28 we held a Dynamics GP User Group meeting at the Microsoft office in San Diego. And on November 10 we held a Dynamics GP User Group meeting at the Microsoft office in Irvine. I thought I'd share with you some of the highlights.

Dan Youngers, a Partner Technology Specialist at Microsoft, presented us with a brief look at GP 11; specifically a demo of the new Word forms functionality. Looks a lot better than Report Writer (not that there's anything wrong with Report Writer).

Dan also shared with us the GP Roadmap.


The consulting staff at Rose Business Solutions presented six sessions that covered various areas:

Tech Talk with Tom

Tom Celvi discussed several issues related to customization and integration development:

RBS uses a defined, two-phase development cycle

Add customization to fill Great Plains gaps when process changes are not appropriate or efficient

Customization and Integration Tools for Great Plains

• Modifier w/VBA - A GP Product Requiring a GP Instance
• eConnect - Microsoft’s Integration Engine
• Web Services - For web, distributed, and interoperable solutions
• Visual Studio GP Toolkit - For nearly any client functionality
• Dexterity Platform - The platform tool requiring specialized knowledge with superior access to resources

Analysis Cubes with Excel (Analysis Cubes Video)

 

SmartList Builder & Smartlist Tips & Tricks

Mike Feori covered these subjects:

SmartList
SmartList Options- Change Basic Defaults
SmartList Searches- Match All, Match One or More
SmartList Export Solutions- Adding Macros

SmartList Builder
Functionality – Joins, Calculations, Restrictions
SQL Views- Existing
SQL Views- Adding New Ones
Imports & Security – Necessary for Use of New Views

Attendees were particularly intersted in:

How to locate SmartList defaults to change/set default "Go To" screens and default record count (it doesn't always have to be 1,000). Navigate: Microsoft Dynamics GP >> Tools >> Setup >> System >> SmartList Options

How to use the “match 1 or more” feature in SmartList.

 

Distribution Series - Inventory, SOP, POP Tips & Tricks

Steve Johnston covered a lot in a short time:

Creating PO’s from Sales Orders
Printing Customer Items on SOP Documents
Back Orders - Document vs. Line Item
Purchase Order Approvals
Returns vs. Credit Memos
Inventory Valuation Methods
How Item Types affect inventory
In-Transit Transfers
Expensing purchases of inventory items
Do Account distributions matter?
Reconciling Inventory to the GL
Historical Inventory Trial Balance
Historical Stock Status Report
Smart Lists

The most discussed items were:

How to create PO's from Sales Orders

In-Transit Inventory functionality


Reconciling to the GL

Liane McIlraith walked through how to reconcile A/R and A/P to the General Ledger. Good stuff:

Use Historical Aged Trial Balance report with Print/Age as of date = period-end date and based on G/L post date to get the subsidiary balance.

Get the G/L balance of all A/P or A/R accounts on Summary Inquiry window or Summary Trial Balance Report. (If there are multiple accounts, use the report.)

Navigation:

To access Payables Historical Trial Balance report:
Reports>Purchasing>Trial Balance>Historical Aged Trial Balance

To access Receivables Historical Trial Balance report:
Reports>Sales>Trial Balance>Historical Aged Trial Balance

To access SmartList of G/L account transactions:
Microsoft Dynamics GP>Smartlist>Financial>Account Transactions


Finding Differences:

Check for un-posted transactions in the G/L.
Determine when the accounts were last in balance.
Use the Financial Reconcile tool (v10 only).
Search for the difference amount in SmartLists.
Search G/L SmartList for unusual transactions.
Compare SmartList of G/L account transactions to the Payables/Receivables Distribution Detail report (print the report to a tab delimited file).

These are the attendees feelings:

1) The Reconcile to GL tool in v10 – saves users lots of time because it compares GL distribution detail to the Payables distribution detail
2) Unmarking the “Allow Account Entry” box for the A/P, A/R, and Cash accounts helps prevent differences between the subsidiaries
3) Dynamics GP doesn’t allow you to post subsidiary transactions through GL without a batch


SSRS & Excel Refreshable Reports


Tom Celvi and I covered implementation and use of two strong reporting tools:

So what is SQL Server Reporting Services with Dynamics GP?

A free product that ships with Microsoft SQL Server to provide enterprise reporting capabilities on ANY SQL Server database(s)
SQL Server 2000, 2005, and 2008
GP10 SSRS product only compatible with SQL 2005 SP2 or later and SQL 2008

SSRS Includes the following components:
Report Manager (The user interface)
Website used to run, deploy, and manage reports
Report Server (The services)
Accesses the Microsoft reporting engine to generate reports

Provides necessary logic for administering and managing the report site

We showed some of the reports available. A complete list.

We also did a quick demo of how to use the Report Builder.

So what are refreshable Excel Reports?

Only available in GP version 10.0
Over 200 standard reports
Based on SmartList favorites
Report Builder included with SmartList builder
Creates and stores connection to GP database
Allows for easy publishing and sharing

We demoed the implementation process and how to create quick reports and dashboards.

I'm surprised at how many customers have not deployed the standard SSRS or Refreshable Excel Reports. It's really quite easy, and I know you'll like the results. So go ahead and try it.


Upgrades / Backups / SQL Traces and Sharepoint Performance

Doug White and Jason Young discussed issues related to managing Dynamics GP systems.



And finally, I know you're all dying to know how to make martinis with Dynamics GP.

Dynamics GP Payroll Tax Update Round 9

I recently helped a client load the Payroll Tax Round 9 Update – both the tax table update and the tax code update.  As indicated the Round 9 documentation, there are only a couple reasons why you would need to do the program update, one of which is if the Round 4 tax code update was not done.  The date of the last code update, as well as last tax table update, are shown on the Payroll Tax Update window.  If the Round 4 code update was loaded, the last code update date will be 2/26/2009.  If the Round 6 code update was loaded (necessary for Louisiana & Missouri employees), the last code update should be 6/22/2009.  When I first checked this client’s Payroll Tax Setup window, their last code update was a 2008 date so I determined they needed to load the code update as well as tax table update. 

A few odd/expected things came up during the process.  First, the program code file that you download from Partner/CustomerSource (KB971014-V10-ENU.msp) is titled “Canadian Payroll 2009 Tax Update for Microsoft Dynamics GP 10.0 (Round 4)” however, it is for US Payroll as well.  So keep that in mind (won’t say how much time I spent searching for the US Payroll version!)  Second, after I carefully performed each step to load the payroll code and tax update, only the Last Tax Table Update Date had changed, the Last Code Update Date had not changed.  I checked the Payroll Setup window and the Cobra field was there (which was a part of the code update).  I checked the version of Dynamics and HR and they both were up to v10 service pack 3 (which was also a part of the code update).  So everything looked like the code had been loaded, except the last code update field, strange!  I checked with Microsoft Tech Support and they agreed that the code had been successfully installed.  They said that they had seen this happen a few times in the past.  This client is on a 64bit system which might have something to do with it, Microsoft wasn’t really sure.  To update the Last Tax Code Update date to the correct date, Microsoft showed me that I had to manually change it in the Dex.ini file.  Once we did that, the Payroll Tax Setup window looked correct.  Of course, you should do this ONLY if you have confirmed the code did actually load.

Everything else with the update went fine.  The code was loaded on the client workstations without issue.  We didn’t even have to rebuild the reports dictionaries and this client had many modified reports in core GP and HR.  But I was ready with my backups, just in case!  As Doug Pitcher as noted many times, always take the safe route and make proper backups.

Payroll update for Dynamics GP

The past couple of days have been interesting regarding payroll updates. We have a couple of clients that run an extremely large amount of payroll transactions (I.E. One client paid around 30,000 employees last year). Typically when a tax update is released you:

• Have the client check for tax updates on their own
• Have them run the update automatically or manually
• Wait a second or two then go your merry way feeling happy you remembered to remind clients there was a tax update release

Well when you are dealing with such a large amount of transactions we felt it prudent to do some testing before giving the All OK sign. Here is the process we initiated:

1. Have all Dynamics users get out of the system
2. Backup Dynamics and company databases. Tax updates will effect the system/Dynamics tables so make sure that database is backed up
3. Enter payroll transactions in a batch. Make sure transactions cover a variety of situations (I.E. Multiple states, dependants, withholdings, deductions, benefits, etc.)
4. Build batch and run process through until you get calculation report. Save report, print it out, etc.
5. Remove batch from build
6. Install tax update. Can do automatically or manually. See MBS payroll site
7. Build batch again as in Step 2 and run until you get calculation report
8. Verify tax amounts have changed from Step 2
9. Verify tax amounts are correct. We did the below calculations to verify the numbers were as they should be. These numbers matched the calculation report in Step 6
10. We then had the client verify these numbers were correct as well
11. Remove build and delete transaction batch used in Step 2 and step 6
12. Signed off on payroll tax update

We started this process on one of the clients test server but after it took 4 hours to restore the backup and after opening Dynamics we still received several errors on the test system we decided the test server was really just there to waste electricity. We ended up doing this on the production server.

Disadvantage on testing on production server

• All users had to be out of the system for a couple of hours

Advantages

• The production server was much faster
• After the tax update was installed and tested we didn’t have to reapply tax update as we would have if we did this first on the test server.

So you probably think I’m a fairly big chicken when it comes to trusting MBS on SP’s and tax updates. It may have cost the client a few hours of testing and time but when payroll is involved I’d rather be called names than be totally naive.

WOW them with FRx Monthly & Year-end Forecast Report

One of my favorite FRx reports is a monthly and year-end forecast report that is a snap to create. Here’s a sample of the report:

 

 

The reason it is so easy to create is that the column layout has already been created and exists in the FRx sample company, Fabrikam Works. You can open the report in Fabrikam Works and then do a file>save as to save the column layout in your company’s spec set. The reason that you’ll wow the report recipients is because it provides actual and forecast information all on one page and because you’ll be able to create the report so quickly. Once you’ve saved the column layout to your company’s spec set, all you’ll have to do is change the Budget ID to the appropriate one for your company in each budget column (a simple copy/paste, paste, paste, etc. will do the trick). Then the report will generate properly each period, pulling actual amounts for the appropriate months and budget amounts for the other months, just with the click of the generate button!

Here are the detailed steps:

1. Launch FRx and switch the Default Company to “Fabrikam Works”
2. Open the catalog ID “IS_Dynamic_GL”
3. Open the Column ID for that report, “Dynamic_GL” (you could skip step #2 and go directly to this column layout, if you prefer). This is what the column layout looks like:

As you can see, there are 2 columns for each period, one for actual and one for budget. The key factor here is the Print Control line. Notice that the Actual columns have “P<=B” which means, print this column if the period of this column is less than or equal to the Base period. The Budget columns have “P>B” meaning, print this column if the period of this column is greater than the base period. Since these settings are mutually exclusive, only one column will print for each period. For example, if the report is run for the Base Period 4, then the Actual amount will print for Period 1 to Period 4 columns and the Budget amount will print for Period 5 to Period 12 columns (pretty nice!)

Where it gets really fancy is in the column header section. Look back at the report sample up above. Notice that the Actual and Budget headers stretch over the appropriate columns. FRx does this automatically for you. No matter what period you run the report for, FRx figures it out! Take a look at the details for each header. The Actual header looks as follows:

By telling FRx to Spread the header from B to BASE, it will always stretch over only the actual columns!

 

The Budget header looks as follows:

By telling FRx to Spread the header from BASE+1 to Y, it will always stretch over only the budget columns! And, Font Style = Column Shade makes the distinction even better.

Anyway, close the Header Options window and move on to step 4.

1. Click File>Save as. This window will open:

The key here is to click on the drop-down button for Specification set and select your company’s spec set. (Normally your company’s spec set will be “Default”. If you have more choices than FRxDemo and Default, and you’re not sure which one to pick, cancel out and go to Company>Information. Click on your company on the left and view the spec set displayed on the right. Do NOT do anything else on this window! Click Cancel and go back to the column layout.) You can then change the name and/or description of the column layout if you want and click OK. You will then get this message:

It did what you asked it to do so, just click OK and move on to step 5.

1. Close the column layout
2. Close the report catalog
3. Switch the Default Company back to your company
4. Open the column layout you saved in step #4 and change the Budget ID in each budget column to the appropriate Budget ID for your company
5. Create a report catalog (or modify an existing one, if you prefer) with your desired row format, the new column layout, and your desired reporting tree (if applicable)
6. Wow your boss and others with this informative report!

Electronic Document Delivery in Business Portal 4.0 with Dynamics GP

This video shows an example of how to use the Electronic Document Delivery function in Business Portal. This is an easy way to send large amounts of documents to your customers electronically.

SSRS Reports Available in Dynamics GP 10.0

This is a list of SQL Server Reporting Services reports that are available for Dynamics GP 10.0. You should also check out the KnowledgeBase article #954242

SSRS reports:

Field Service

Contract Information
SVC RTV Hard Copy

Financial

Additions Report
Bank Transaction History Report
Checkbook Register
Fixed Asset Depreciation Detail
Fixed Assets Depreciation Ledger
Fixed Assets to General Ledger Reconciliation Report
Journal Entry Report
Period Projection Report
Retirements Report
Source Cross Reference
Trial Balance Detail
Trial Balance Summary
Undeposited Receipts

Human Resources

Employee Attendance Detail
Employee Attendance Summary
Enrollment by Benefit
Enrollment by Employee

Inventory

Purchase Advice Report
Purchase Receipts
Sales Summary
Stock Status

Manufacturing

BOM Detail Report
Item Standard Cost Changes Report
Job Detail
Manufacturing BOM Report Standard Costs
MO PO Links Report Sort By Vendor
Picking Report – Item Number
Picking Report Multibin – Item Number
Traveler Graphics Reports

Payroll

Check History
Check Registry
Department Wage and Hour Report
Earnings Summary
Employee Pay History
Employee Wage and Hour
Payroll Summary
State Wage Report
Vacation Sick Time List

Project

Detail Trial Balance
Monthly Employee Utilization
PA PBW Fee
PA PBW T and M
Pre-Billing Worksheet CPFP
Pre-Billing Worksheet T and M
Project Cost Breakdown
Projects in Progress

Purchasing

Aged Trial Balance – by Document Date
Aged Trial Balance Details Subreport – by Document Date
Back-ordered Items Received
Cash Requirements
Expected Shipments
Historical Aged Trial Balance
Purchase Order History
Purchase Order Status
Received Not Invoiced
Receivings Trx History
Transaction Detail
Vendor Summary

Sales

Accounts Due
Aged Trial Balance – Detail
Historical Aged Trial Balance
Receivables Sales Analysis
Sales Distribution History
Sales Document Status
Sales Transaction History
Sales Transaction History Payment Details Subreport
Sales Transaction History Tax Details Subreport
SOP Document Analysis
SOP Document Analysis by Customer
SOP Inventory Sales Report

Once you've got these loaded, take a look at my quick video about using the Report Builder function in SSRS.

Payroll update for Dynamics GP round 9

Round 9 payroll update was released for Dynamics GP today. This affects only California so if it's above 60 degrees and you are living close to the Sharks or Kings and west of the Coyotes chances are you will need to do this. (Sorry, Canadian's mostly use temperature and hockey town's for figuring out geographic locations. For example, I live 5 hours south of the Calgary Flames, 8 hours from the stinky Oilers, not far enough from the Canadiens. I digress.)

It's just a tax table update so no code is installed (assuming you have already applied round 4 where the code was changed.) Back up the Dynamics database then on the server or any workstation, log onto Microsoft Dynamics GP with the System Administrator user, and open the Payroll Tax Update window. (Microsoft Dynamics GP menu>> Maintenance>> U.S. Payroll Updates>> Check for Tax Updates) and you're good to go.

If you are still on Great Plains 8.0, shame on you. But you can probably just download this file and run it on your sql backend. Save you some time from updating this all manually. The file is the TX.cab file and can be ran instead of doing the automatic payroll update. Make sure you back up the Dynamics database before so executing.

Important, Please read
Another side note for payroll. I received word today that MBS is starting their GP 12 planning as they think they've done about all they can with GP 11. (Hope that's a good thing.) I was asked for any wish lists items for HR and Payroll. If you have anything you would like added to Dynamics payroll and HR, NOW is the time to voice your opinion. If you don't have a MBS super secret contact to get you in on the planning leave a comment here and I'll be sure to voice your opinion. (I'm talking to you Chris.)

Collections Management

The Collections Management module in GP includes features that assist you in managing and collecting your accounts receivable. The module is included in the Advanced Management BRL license. And though many organizations own the license to use this module, I rarely see it implemented.

There are five features that I think really make it useful for controlling your outstanding accounts:

1.) Most of the information that you would want access to in order to review a specific client's account is accessible from one screen; the Collections Main screen

2.) You can create various queries to select customer accounts that allow you to focus your attention on the accounts that need the most attention

3.) You can create notes that document activities and conversations regarding specific customer accounts.

4.) You can easily re-print selected invoices, print a current account statement, select an appropriate cover letter, and distribute to the customer via fax or e-mail.

5.) You can print a standard aging report that includes the notes relevant to each customer's account.

If you haven't implemented Collections Management yet, you should take a look at it. I recorded a short video to show you my favorite features.

Have You Used Any of These?

I've used several of these machines in the past; how about you?

David Musgrave suggested the following two additions; the Amiga, and the Sinclair ZX80:

New Roseling

Just came back from having a week off due to the birth of our third child. It was a great experience and both baby girl and mother are doing well.

Still haven't named her yet. I've suggested Rose, Steve-anie, Mariana, Davita, Markita, Victoria, and many other homages to my blogging and Dynamics buddies....but my wife just rolls her eyes and says "To bad I didn't know what a geek you were before we got married." ha.

Any suggestions for names would be welcome.

Extended Pricing

The Extended Pricing module allows you at add date sensitive pricing in your system. The module is also more customer oriented than the basic pricing.

This is a basic set up diagram of the module; with some key points listed:


The price sheet is the basis for pricing. Price sheets will include all basic pricing and promotional pricing. You will want to set up a basic price sheet that contains basic pricing for all you items. Additional price sheets should be set up for different promotions or special pricing.

Price groups can be set up to include items with the same pricing. These are especially helpful for promotions or special pricing. You likely will not want to use them for basic pricing.

Price sheets are all activated for specific time periods; even the basic/default price sheet.

Price books should be used to assign default pricing to your entire customer base, and to assign pricing to individual customers, or groups of customers.

There is a special feature that allows you to set up a promotion that gives the customer a free item when they purchase a specific quantity of an item. Here is an example below that shows the quantity brackets for which the free item is offered:

The free item offered in the promotion can be the same item ordered, or a different item. The free item is specified in the screen below:

I created a short video to show you this in action. Take a look at it.

Lessons Learned: CRM 4.0 Adapter for GP 10.0

We are excited about this new integration, so we jumped right in, and installed it on our production systems. Our systems engineer is a genius, and what follows are his comments related to his experience. Consider this an experiential addendum:

1. Must have GP 10 SP4 applied. (There are caveats to sp4 so be aware)

2. Must have at least rollup5 for CRM applied. (Rollup6 was out in Oct 2009 so I used rollup6)

3. Must have GPwebservices for GP10 sp4 and Management tools sp4 installed.

• Had issues with the GPwebSerice not showing up in Dynamics Security console.
• Ran a repair on the GPWebServices and the management tools and it resolved the issue.

4. Must have Microsoft Visual C++ 2005 Service Pack 1 Redistributable Package ATL Security Update applied to same server GPwebservices is installed. http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=766a6af7-ec73-40ff-b072-9112bab119c2#filelist

5. Must have domain service accounts created. You can determine how many you need. Recommend at least 3 svc_gpweb, svc_gpcrmadmin, svc_gpcrm.

6. GPWebservice Account - Permissions granted during install.

7. Installation account – The account that installs the Microsoft Dynamics CRM Adapter for Microsoft Dynamics GP.

a. Be a domain user and a member of the Local Administrators group on the server where Microsoft Dynamics CRM Adapter for Microsoft Dynamics GP is to be installed.
b. Be assigned at least the dbcreator and securityadmin server roles on the SQL server where the MSDI database is to be stored

8. Integration account – The account that the Microsoft Dynamics CRM Adapter Service runs as. Permissions granted during install of Dynamics GP CRM adapter.

9. Configuration account – The account that runs the Microsoft Dynamics CRM Configuration Utility.

a. This account must be able to modify the membership of the CRM PrivUserGroup Security Group in the domain that the Microsoft Dynamics CRM Servers are joined to.
b. If this account is not in the db_datareader and db_datawriter roles for the Microsoft Dynamics CRM organization databases, you must use a SQL Server user name and password to update the Microsoft Dynamics CRM organization databases.

10. Integration GP account – The account that the Microsoft Dynamics CRM Adapter for Microsoft Dynamics GP uses to connect to Microsoft Dynamics GP Web Services.
a. Permissions granted in the Dynamics Security Console

11. Integration CRM account – The account that the Microsoft Dynamics CRM Adapter for Microsoft Dynamics GP uses to connect to Microsoft Dynamics CRM Web Services.
a. This account must belong to a Microsoft Dynamics CRM User that has organizational-level read privileges for system user on the CRM Organization that you plan to integrate.

12. CRM Deployment Administrator account – The account that the Microsoft Dynamics CRM Configuration Utility uses to retrieve a list of Microsoft Dynamics CRM organizations and apply customizations to the Microsoft Dynamics CRM organizations.

13. GP Adapter configuration - Had an error: application domain ...... Only way to fix it was uninstall adapter and remove MSDI database. (just removing database may have fixed it but just uninstall did not)

Fulfillment Workflow in Sales Order Processing

The Advanced Distribution module in Dynamics GP allows you to setup a workflow process for order fulfillment. You can select up to six steps, and rename them to fit your organization. This functionality gives you more control over the order fulfilment process and helps ensure that orders are processed accurately.

These are the setup steps from the contextual "Help" documentation:

If you’re using sales fulfillment workflow, use the Sales Fulfillment Order/Invoice Setup window to activate workflow for sales. Use the Sales Fulfillment Document Workflow Setup window to select the workflow statuses to track for a fulfillment order/invoice type ID with the Enable Fulfillment Workflow option marked. You must mark Status 6 and at least one other status to track workflow information.

To set up fulfillment workflow for sales:

Open the Sales Fulfillment Order/Invoice Setup window. (Microsoft Dynamics GP menu >> Tools >> Setup >> Sales >> Sales Order Processing >> Sales Document Setup button >> select the Fulfillment Order/Invoice option)

Enter or select a fulfillment order ID/invoice ID for the workflow you’re setting up.

Mark the Enable Fulfillment Workflow option.

Choose Workflow to open the Sales Fulfillment Document Workflow Setup window.

To exclude a document status from the workflow process, unmark the Active option for the status.

Choose OK in the Sales Fulfillment Document Workflow Setup window. Choose Save in the Sales Fulfillment Order/Invoice Setup window.

This is the window in whch you can modify the description for the different steps. (Microsoft Dynamics GP menu >> Tools >> Setup >> Sales >> Fulfillment Workflow Setup)


This is the window referenced above that allows you to setup workflows for specific sales document ID's:

I created this short video to show you how the fulfillment workflow process works for an order.

Publishing Refreshable Excel Reports with Dynamics GP

This video shows you how to generate refreshable Excel reports from Dynamics GP, and then publish them to SharePoint and make them accessible in Outlook. Refreshable Excel reports allows you to format an Excel spreadsheet as you wish, and then at anytime refresh the data in the spreadsheet from the GP database.

45 Day Trial Offer of Microsoft Dynamics GP Hosting

I can't believe Linda is making this offer. She actually wants to give away 45 days of hosted Dynamics GP, with no commitment, no deposit, no money at all.

Linda is making this offer to partners through November 30, 2009. Here is the opening of the offer:

RoseASP is now offering free hosting to partners on behalf of their prospects and customers. This is a great opportunity to get your customers on board with Dynamics GP by having them simply try it out. Whether they host, purchase, or eventually decide to go “on-premise”. RoseASP will assist you in closing deals.

Bring us your prospect and we will create a separate company, with up to 5 users, on our servers, so that Partners can begin working with their prospect on the ease of use and quick up-time with Microsoft Dynamics GP. Prospect/Customer data will be stored in a secure, managed environment with 24/7 support and monitoring. We will guarantee a quick turnaround – less than a day – in getting your prospect up and running!!! Microsoft Dynamics Partners will maintain complete control of their customers with our Hosting Partnership Agreements.

This is a great opportunity to start clients out with no software licence commitment.

Key Performance Indicators (KPI) Types in Business Portal

There are three types of KPI's you can create in Business Portal 4.0. There are sample KPI's to start with. And there is functionality to create your own from scratch, using the Business Entities in Business Portal.


I recorded three videos to show a sample of how each type can be created and published to Business Portal:


List KPI


Alert KPI


Bar Chart KPI


So, kick back, grab some popcorn, and watch the KPI Trilogy.

Key Performance Indicators for Business Portal

Business Portal 4.0 includes the ability to create your own KPI's and display them on your BP site. There are three types of KPI's you can create:


1.) List - A List KPI display multiple KPI's together. The example below is a Lists KPI that displays four individual KPI's. The displayed row, "Ian's Sales" is a single KPI.


Alert ranges can be set up to indicate "Normal", "Warning", and "Critical" values. The colored text indicates the different alert levels.




2.) Alert - An Alert KPI displays alerts using icons.

3.) Bar Chart - A Bar Chart KPI graphically displays a single KPI:

A good way to get started is to copy one of the sample KPI's that comes with Business Portal and edit it to work with your company.

Navigate to the KPI Management page to view, edit, copy, and create KPI's: Business Portal Home Page >> Site Actions >> Site Settings >> KPI Management

Select "Organize KPI's" to create a new folder for your own KPI's, and then select one of the sample KPI's and copy it to the new folder.

Edit the KPI by selecting it and going to the "Query Details" tab or the "Equation" tab. KPI's use queries or other KPI's to serve up the data. You will want to check the queries or source KPI's to make sure that they reference your comapny and that the selection criteria are relevant to your company (some of the sample KPI's select data based on account numbers).

To display the KPI in Business Portal, you will need to add a KPI web part to a page in you business portal. There are separate web parts for each of the three types of KPI's mentiuoned above.

You will want to download from CustomerSource, the Key Performance Indicators user manual, and possibly the Common KPIs Guide to get more details.

I recorded this video to show you these concepts in action.

Dynamics life cycle

I attended a MBS presentation a couple of days ago regarding MBS's life cycle policy. Pretty straight forward but thought I'd share my notes.

In summary, Microsoft will support a major releases for 5 years. This is called main stream support. The support will end the second Tuesday in the following quarter after SP1 is released. See screen shot below for graphic of that convoluted timeline.

Service packs will be supported for 2 years (until the second Tuesday in the following quarter after the next service pack is released.) If you are on an unsupported service pack level MBS will not support you unless you have extended or custom support.

Regulatory updates will be provided during the mainstream support phase. E.g. Payroll updates will be released for 9.0 for 2009 and 2010. GP 8.0 payroll year end update not being supported last year was a one-time thing. 9.0 payroll release will have yearend support for the next 2 years.

Descriptions of terms:
Microsoft Life cycle policy – 5 years from general availability release (GA date) shortly after RTM
Extended support – 5 years after life cycle is gone. Have to purchase. Pricing from your Microsoft representitive
Custom support – After SP support is gone, can purchase custom support. Sounds pricy but available. Same with after mainstream support.
Service packs – supported for 24 months after next service pack is released (second Tuesday, next quarter)

What kind of training is most beneficial for Dynamics GP?

I have a confession to make. In my 10 years working with Dynamics GP I have only been to one formalized training class and the class was on Crystal reports. When I started out in the GP world I was working as an intern at a small start-up company in El Segundo, CA. My fancy title was Financial Analyst which really meant I was cheap labor hired to do whatever anyone else important didn't want to do.

My project for 5 months centered around implementing a new accounting system called Microsoft Dynamics. The company was moving off a smaller system (MYOB) as they had high hopes (and VC funding needing to be spent) the company would take off and need a complex financial system to get them to the next level of efficiency...and funding.

I was given a couple of Dynamics CD's before I showed up to my new job so I could learn the system but really had no idea what to do next. I arranged a phone call with my companies new VAR/Partner and was shown how to install the software then was left to my own devices. The following are some of the things I remember about that experience:

• It sucks when your computer crashes and you have to uninstall GP as your user is locked in the system (hope you are laughing with me)
• Creating a test company doesn't get you too far as there is nothing setup, no chart of accounts, vendors, etc. Which means you can't really do anything in the system
• What in the world is "The World Online"? Must be some kind of Microsoft promotional propaganda
• The tutorials in GP are on things I generally have no need for
• The user guides are a pretty good resource

I arrived at my new company full of apprehension and very little Dynamics knowledge. In the course of 5 months we implemented INV, SOP, POP, PM, RM, GL, and connected Dynamics to pull in orders from our website (our companies developers basically wrote eConnect before there was eConnect.)

I was known as the Dynamics guru, super user, and GP go to guy. In all reality, I spent a lot of time reading those blessed user guides and then tying up the loose ends with our Dynamics Partner in question and answer sessions. CustomerSource was horid and the online Dynamics community was still in infancy.

Did I wish I had formal training for the software? Yes

But what kind of training is most beneficial? I will discuss my opinion in my next post.

I will look at the following potential training methods for Dynamics GP:

• Formal MBS Classroom training
• Onsite training from a Dynamics consultant, typically sent from your Partner
• Training from your Partner or other Dynamics Partners
• Dynamics GP system manuals and user guides
• Conferences
• Partnersource/customersource
• Online training
• Online resources
• Internet search
• Trial and error

Multicurrency reporting exchange rate for Dynamics GP

Mike Feori is as good as it gets regarding documenting and detailing descriptions of business processes. At few weeks ago he put together a response to the question:

How in the heck does the reporting exchange rate work?

I only have a fuzzy mug shot of Mike but hope you can tell he's wise and experienced beyond his years.

I've summarized his response below for your reading pleasure.

***********************

Generally, the functional and reporting currencies are the same. However, if you have a situation where the functional currency is USD, but the reporting currency is Z-$C implies that you have a Canadian company that reports in Canadian dollars (generally for financial reporting), but conducts most and/or all of its business in the US dollar.

This setup is most evident in the ability to toggle between the functional, originating and the reporting currencies when viewing inquiries and reports. Additionally, you can set the conversion factor to the current exchange rate difference between the functional and reporting currencies. This gives the user the ability to very quickly view their accounts and transactions in their reporting currency even though all or most of the transactions are in the functional currency.

Reporting Currency (Canadian dollar) at .9 conversion

Functional currency same as originating in US dollar:

You can select functional, originating and reporting as well as modify reporting rate by selecting the dollar icon in the upper
Right hand corner.

Thanks for the explaination Mike.

How to void partially applied invoices in Dynamics GP

I should have put this in my top technical support issues blog as this is about as common of an issue as it gets. I can't tell you how many times I've heard ^$%#@$! Great Plains. I can't void out a payment because it's applied to an invoice that still has amount outstanding. I also can't void out the invoice as it has a payment applied to it.

This is one of the most rinky dink processes but this is the resolution for both receivables or payables:

1. You have a payables or recievables invoice that has a payment or credit memo applied to it. The credit document is fully applied so is in history
2. You cannot void the credit document or invoice
3. You must create a dummy credit document for the amount outstanding on the invoice
4. Post the credit document
5. Apply the credit document under the apply window
6. You can then void out the payment, dummy credit document and invoice if you desire

The same would be true if the invoice is fully applied and there is still an outstanding amount on the payment. Create a dummy invoice and apply it to the payment. Then you can void the payment and the dummy invoice.

As Mariano says, "Don't shoot the messanger".

Creating a Simple Dashboard Using Excel Reports

Excel is increasingly being used as a reporting tool. I recently created a simple dashboard for a client using the refreshable Excel Reports that come with GP version 10.0:

I started by using three of the Excel Reports to pull the data from Dynamics GP. I used the Customers Past Due, Customer Balance, and Vendor Balance reports to get the data. I have the results from all three reports on one spreadsheet. This allows me to refresh the data in one place.

I created a video to show you how I did it. Check it out.

Should you backup your database before processing payroll?

A client called yesterday and were in a bit of a tizzy. They processed payroll last month and in the middle of things GP crashed. They said a few cuss words, logged back into GP then continued processing. When they went to do their period end reports they were a little taken back because gross wages was off by $13,000,000. I had to write that out just to see how many zero's that is.
A few questions came out of the CFO's mouth such as:

• How could this be?
• Did they really send a check out to an employee for that amount?
• Is our bank reconciliation off?
• Is our GL amounts off?

We discovered an employee with the below information:

My first question was - "Are you sure this employee is still in the country because if I received a check like that, I'm heading straight to Samoa for an indefinite period of time."

They chuckled a bit but I don't think they found my comments to helpful.

The payrun that caused this was of course two weeks ago so restoring from a backup was not really an option. This made me pull out my SQL skills and go to work. I used tech doc 948268 to resolve the issue but here are the basic steps:

1. Backup Company databases
2. Make sure you backed up the correct company database
3. Delete the payrun in question from the following tables using these scripts. Replace XX with the correct Audit trail code. delete UPR30100 where AUCTRLCD='XX' delete UPR30200 where AUCTRLCD='XX' delete UPR30300 where AUCTRLCD='XX' delete UPR30400 where AUCTRLCD='XX' delete UPR30401 where AUCTRLCD='XX'
4. Delete the employee's summary information by using this script, replacing YY with the employee ID and ZZZZ with the year in question. delete UPR30301 where EMPLOYID = 'YY' and YEAR1 = 'ZZZZ'
5. Run Reconcile in payroll - Point to Tools on the Microsoft Dynamics GP menu, point to Utilities, point to Payroll, and then click Reconcile.
6. Deal with bank reconciliation module and GL as necessary

I ended up deleting one table that had 450,000 rows duplicated for one transaction. Thus the 13 million dollar employee card.

Now the real question is should a backup be done before processing a payroll batch? The answer is a resounding YYYYYYYEEEEEEESSSSSSS!!!!! Does that mean everytime? Let me be clear. Everytime you process payroll you are at risk of having your payroll module blow up. The above was a fairly easy fix compared to what can happen. I wish I could be more upbeat about how great GP is and how solid payroll is as a module.

Another thought. We have a couple of clients that all they do is payroll. They process thousands of transactions a week and in any given day they have several people posting payroll batches. To back up their database takes 10 to 20 mins. To do a backup each time is not really feasible. So after explaining the situation clearly they have decided to risk a days worth of work for irregular event of having the payroll module messed up with a posting interruption.

Talking with Mike from our office about this event and he told me how he fired someone when he was a controller when they processed payroll without doing a backup. The one and only time they didn't have a backup resulted in heartache, gnashing of teeth, Mike flying off the top ropes with a 10 key....you get the drift.

Here is a link to backing up your GP system.

Please consider making a backup before processing your payroll. One time in a thousand you'll actually need it but you'll thank your lucky stars when you do.

DynamicsGP Version 10 on Windows 7?

Will DynamicsGP Version 10 run on Windows 7?
Why yes it will, thanks for asking.

Actually, all the pain we experienced with Vista has made this a non-event. There are even printer drivers that are labeled for use with Vista that work with Windows 7. Even the 64 bit version.

I’ve loaded GPV10 on 2 Windows 7 machines with success. The first install was on an old workhorse Dell Optiplx 760 with an Intel core duo and 1 GB RAM. This machine was loaded with Windows 7 Professional Edition. The machine was slow to launch GPV10, (as should be expected) but once the app was running, performance was pretty good.

The second machine was a new Optiplex 960 with 8GB RAM running 2 X 160 GB 10K RPM HDD in a RAID 1 array configuration. This machine is running Windows 7 Ultimate Edition 64 bit. Naturally this machine launches into GPV10 a little quicker.

The only issues I ran into was the dreaded 'please verify your ODBC settings and that BCP has been correctly installed. Utilities will exit'. This is an easy fix and is caused by UAC (User Account Control) being turned on by default. Tech Support gave me this:

In Windows 7 the UAC functions a little differently as it is now a slider rather than a distinct on or off feature. Below are directions to locate and turn off this feature.Start Control Panel User Accounts User Accounts Change User Account Control SettingsWhen you open this you will see a slider, slide it to the bottom to turn it off. This should allow you to continue with your service pack installation.

The only other issue I ran into was with ODBC DSN on the Windows 7 Ultimate machine. Utilities wouldn’t recognize the DSN when it was configured on the ‘System DSN’ tab. No problem when configured on the ‘User DSN’. Permissions no doubt.

Net result. Fear not Windows 7. Go forward and dog-food this much improved OS.

Service Pack 4 for Dynamics GP 10.0

Just a follow up from yesterdays post. I received an email from a MBS escalation engineer this morning (a week and a few days after the last SP was released) which says:

"not to alarm anyone, but we are seeing a few more issues around SP 4, than we expected."

Made me chuckle a bit as it ties in perfectly with yesterdays post.

Unless you have a reason to update to SP 4. I'd suggest waiting for a few more weeks before taking the plunge.

How long....before applying new SP's?

As MBS has just released SP 4 for Dynamics GP 10.0 I've had a Elevation of calls come in about updating. I'm more than happy to point clients in the direction of the new SP but I usually pose the question How Long do you usually Walk On before applying a new SP from MBS (Lots of U2 references, what more can you want in a blog).

I've heard anything from "as soon as it's released" to "we Stay a full SP release behind."

I usually recommend waiting for a month before applying a new SP for a couple of obvious reasons. Number One and two:

• SP releases fix issues but occasionally cause problems as well


• Installation issues Mysteriously occur. See Mariano's from the trenches post

So why would you Desire to update as soon as the next Sweetest, Magnificant SP is released? I would suggest only after fully testing on a test system and only if you need a fix that is resolved in the SP release. 10.0's fix list is here in the installation guide. Other than that why not wait a short while to let everyone else have a Bloody Sunday.

So How Long (older version of song) do you wait before installing a new SP?

Still haven't found what you're looking for? Hope you don't have Vertigo.

Have a Beautiful Day. I know I will....With or without you.

Great Plains Crashes

I recently experienced the worst of all possible scenarios for a consultant. I had a client that was experiencing crashes of Great Plain at random intervals that could not be reproduced with any combination of commands or operational sequences. Crashes could occur during posting, customer and/or vendor card lookups or with the Home page presented and no activity in Great Plains. The message presented was: “Microsoft Dynamics GP has encountered a problem and needs to close. We are sorry for the inconvenience.” At that point, Great Plains would close and the user would be required to log back into the application to continue.

Working with Microsoft Support, we provided detailed information about the environment, versions, and other software installs. Due to the random nature of the problem, we could not use a DEXSQL.log as it could be running for days sometimes before a crash would occur. We removed Great Plains from the machine and re-installed, but the crashes continued. We installed Great Plains on a new computer workstation with IE and Microsoft Office, and the crashes continued. We removed all software from the new computer workstation and re-installed the operating system (XP) and Great Plains only. This seemed to stop the random crashes, but was a pain for the user as they had no internet access or use of applications such as Word or Excel. We tried adding back IE and Microsoft Office once more and the crashes resumed.

Microsoft Support then began looking at the Applications, Security and Event Logs. Review of these logs indicated error events occurring in conjunction with the Google Updater Service and the Windows Search Service. We manually turned off both of these services. At this point, the interruptions with Great Plains ceased. We subsequently added back the Windows Search Service without any return of interruptions. Microsoft Support indicated they had no history of the Google Updater Service causing Great Plains to crash, but for this customer, the Google Updater Service is not allowed to run.

If you want more information on the Google Updater Service, just Google it (sorry for the bad pun). The list returned from this search is long and interesting. I know if I ever see this error message again, the first thing I am going to try is to turn off the Google Updater Service.

Analysis Cubes with Dynamics GP

This video shows you an example of how to use the Analysis Cubes function in Dynamics GP to provide enhanced analysis and reporting on your GP data.

Analysis Cubes provides a quick way to present data and allow users to manipulated the report and analyze the data using simple OLAP techniques.

SSRS Report Builder with Dynamics GP

This video shows you how to easily use the SSRS Report Builder with Dynamics GP to create quick reports.

Report Builder uses report models that come ready to use with GP. You can also create your own report models using Visual Studio.

This is a good way to get your feet wet with SQL Server Reporting Services.

Commission Calculation for GP from Ethotech

When our clients are looking for sales commission calculation functionality in GP, we usually specify the Commission Plan module from Ethotech. We have found it to be quite flexible and capable of handling most of the commission calculation issues we find.

One of the key features that makes it so powerful is that the basis for the commission calculation is the detail sales record in the SOP module. Below is an example of a commission calculation from a sales invoice for two different products. In this example, the commission is split among three different levels of sales people. Each level has a different commission rate.

The calculation is driven by a matrix of customer, item, and salespeople values, that allows for a high level of complexity in the commission structure.

Here's a drill-down from the above screen that shows what product is generating the commission, and the drop down shows the type of commissions that can be calculated.

If that's not enough detail, look at this:

This is a screen shot of the setup screen that shows you various configuration options that are available:

Ethotech is a strong developer, and continues to add functionality to this product. If you want to use GP to calculate your sales commissions, you should take a close look at this. Because the calculations can be managed at the item level, you could also use this product to calculate royalties due on products and service sold.

To upgrade or not to upgrade Dynamics GP

A client of ours uses Encore's Recurring invoicing in conjunction with Nodus's Credit Card Advantage. They are still on Great Plains 8.0 and are using EFT transactions as well. The need has arisen for customers to use multiple EFT bank accounts for different contracts in Recurring Invoicing. Can't be done in 8.0 but in 10.0 you can have EFT accounts attached to different address ID which can then be used as a billing address on the contract.

My answer to them is you need to upgrade to get that functionality. The accountants response was "What the heck. Why haven't we updated yet?" I would have liked to say "I've tried to get you guys to upgrade for a year or more now" but instead I was diplomatic and said "I think your IT staff is in the process of following some of our recommendations to move up to the new system. You may need to check with them."

Coming from the Partners viewpoint I usually say after one or two service packs you can upgrade whenever you like. That's not always necessary/feasible. The real issue comes as upgrading Dynamics GP (or any software for that matter) comes with certain costs. A few are listed below:

• Annual maintenance costs from Software vendor- typically a percentage of you system list price


• New hardware to meet new software recommendations


• Consulting or in house time to do test upgrade - includes time spent verifying data upgraded successfully, 3rd party application testing, interfacing with end user to verify everything looks ok, etc.


• Consulting or in house time to do production upgrade software, install clients, update reports, install 3rd party applications, upgrade 3rd party applications, and fix modified reports that don't update, etc.


• Downtime for current employees during upgrade


• Training users on new features or procedures


• Ineffiencies while using/navigating through a new system


• Increased support costs with Partner after upgrade specifically related to new functions and procedures

So why in the world would anyone volunteer to update their system as it seems like a new version is released every 6 months? I've listed a few reasons to upgrade below:

• Increased effiencies with new hardware, system performance with updated software


• Older software is not supported and you have to find a dinosaur that charges $400 per incident to fix anything. (I know the two last dinosaurs that support GPA, man are they old and ornery)


• New technology only available with new versions of software - Business Portal, eConnect, Sharepoint, SSRS, etc.


• New versions are being fixed, developed, enhanced so in theory you should have less support calls regarding system bugs


• Upgrade costs are minimized when system moves to the next version up instead of making a 2, 3, or more step move. See Mariano's blog for upgrade paths.


• Training usually is less involved as there are not 2 or 3 sets of "What's New" crammed into one training session


• Newer version of software is typically more developed. If you are still using the first version of CRM, RMS, Analytical Accounting etc., I'd suggest you are akin to gnawing your foot off to get out of a bear trap (Montana metaphors, got to love it).

So what do you think? How do you determine when to upgrade?

What are some of your reasons To Upgrade or Not to Upgrade?

Here are a couple of links to formulate my thinking:

• MBS Marketing blog (of course they want you to upgrade)


• David Musgrave better not convince MBS to extend 8.0's life cycle policy again


• Andrew Karasev

It Takes Forever to Look up a Journal Entry

For years I have avoided demonstrating the very nice feature in GP that allows you to correct or copy a posted general journal entry, because the look up to find the subject journal entry took forever. But I was recently presenting a demo, and I felt lucky; so I thought I'd try it. No luck.

However, this morning John Crahan, one of our consultants, steered me toward KnowledgeBase article #925326. Within that article is a SQL script that creates indexes in two tables that speed things up tremendously.

I added the indexes to my demo system in less than a minute. Works great!

Inventory Cycle Counts in Dynamics GP

This video shows you how easy it is to process inventory cycle counts in Dynamics GP. Set up a Stock Count Schedule, start the count, and enter the results.

Excel Report Builder in Dynamics GP

Excel Report Builder is an easy to use tool in Dynamics GP that will let you easily report on information in GP.

This video shows a simple example of how to use it.

Setup New Fiscal Year in Dynamics GP

It's summer time in Montana so that means fly fishing and farmers markets. Oh how I love summertime. All two months of it. (It might end up only one and a half months this year as it's barely starting to warm up now).

Usually this time of year also brings a rash of calls regarding Fiscal Year setup in GP. Fiscal year setup is a very simple thing to do but when you only do it once a year it sometimes intimidates the faint of heart.

Here's how to setup a new fiscal year:

1. Navigate to Dynamics GP>>Tools>>setup>>company>>Fiscal Periods
2. Put your cursor in the year field and type in the new fiscal year (I.E. 2010)
3. Press Tab
4. Verify the dates are correct in the date fields
5. Verify the number of periods you want in the year. Typically 12.
6. Choose calculate
7. You can then check off the periods you don't want anyone to post to yet. You can open these whenever you desire.
8. If you already have these periods setup you may just need to uncheck the modules to allow posting.

Couple of notes:

• Make sure the dates don't overlap any other year
• Make sure you have all dates covered. GP doesn't like any days missing from one year to the next

Sales Taxes, Not so Difficult

Most Dynamics GP users I talk with, complain about the sales tax functionality. They say it's difficult to set up and maintain. I don't necessarily agree.

I just updated the new tax rates effective in California as of July 1, 2009. Because I had setup the taxes using Tax Schedules that contain individual Tax Details to represent the different taxing agencies, all I had to do was update the Tax Details that changed. In my case, the one Tax Detail that changed was Los Angeles County. Once I changed the one Tax Detail, it will effect all Tax Schedules that include that Tax Detail; perhaps six.

The task was simplified because the CA Board of Equalization publishes a list of tax rates. There are maybe 130 different taxes on this. An added bonus; this report includes four character codes that can be used for the Tax Detail ID.

When I set up sales tax in GP, I start with the tax return that needs to be filed on a periodic basis, and figure out how much detail I will need to assist in reporting. I then identify the different tax details that need to be created, and then create the Tax Schedules to contain the details. The set up takes some time, but keeping the rates up to date; not so difficult.

I agree that if you have more than a few states, this can become overwhelming. For those cases, use a service like AvaTax or CCH.

Revenue / Expense Deferral in Dynamics GP

This video shows an example of how to use the Revenue / Expense Deferral module in Dynamics GP to defer sales revenue over a 36 month period from the SOP module, using a deferral profile.

The video shows the module setup, a sample deferral profile, and how a default deferral is applied to an invoice transaction in the SOP module.

The reporting for the module provides a simple but effective analysis of deferral transactions at a user specified date.

Quickly Printing Multiple Invoices for a Customer

If you have the need to print invoices for a customer and some of the invoices are in history (posted), there is a way to do this quickly in Microsoft Dynamics GP version 10. Follow these steps:

1. Click on the Sales page button in the lower left corner of your screen
2. A list of inquiry/report options will be shown on the left side of the screen (above the page buttons). Click on "Sales Transactions" within the list.
3. Enter the customer's name in the "Type to filter" field and then click the arrow button to the right of this field.
4. Click on the "Document Type" column header to sort all of the invoices together.
5. Check the box to the left of each invoice.
6. Click "Print Documents" near the top of the screen.

Dynamics GP will determine if the regular invoice is applicable or if the historical invoice is applicable for each invoice and print all of the marked invoices.

Use the FRx Email option to automatically send reports generated to Excel

In the past, I've rarely seen a use for the FRx output Email Option. If you email a report generated to the FRx Drilldown Viewer, the email will have the report in a Drilldown Viewer file attached or it will have a link to the Drilldown Viewer file. Unfortunately, a report recipient would need to have FRx Drilldown Viewer installed and more importantly, a Drilldown Viewer user license, to open the file and view the report. A Drilldown Viewer license is fairly pricey, so many companies do not have Drilldown user licenses for their report recipients. Instead, the reports are generated to Excel files and then those files are emailed to the recipients.

Recently, I realized that you can use the Email Output Option to automatically send out a report in an Excel file. For example, if you email to the Board of Directors the Balance Sheet, P&L, and Statement of Cash Flows in Excel files each month, you could set the Output Option on each of the reports to automatically email them to each of the Board Members. The only time this won't work is if you are using a reporting tree and only certain units of the tree are to go to only certain people. There is currently no way to do this. (If anyone has figured out a way to do this, please let me know!)

If you routinely generate FRx reports to Excel files and then email those files out, using the Email Out Option will save you some time.

SQL Magic...post script

In my previous blog I talked about how my SQL wizard (Tom Celvi) was able to help migrate masses of data when Excel and Integration Manger could not step handle it. I asked him if he would elaborate briefly as to his steps. Thanks to Tom for this contribution!

A Custom database was created

"I did this so I could keep the initial import data separate from GP and to provide a central point of access for both the GP production and test companies.

This way, I could use the same data and processes for both testing and production deployment without having to re-import the data for both test and production runs.

Additionally, I created a method for capturing the results of each employee import, so we could review failures and successes. I created a single stored procedure to process the data into a company. This stored procedure was developed in the test company, and then deployed and run in the production company once the data import was acceptable.

By placing this data into a separate database, I was able to use the actual GP table definitions and names, which provides some data quality checking at the time of data import."

A view was built in the custom database

"To grab the employee ID’s of any employees that had received checks in the past 6 months.
That view was then used for filtering employees during transaction processing."

Uploaded the flat file data in the custom database.

"I just uploaded the flat file data to the actual GP table definition, but in the custom database. This gave me confidence that the file data was usable, since the original definition was able to accept it."

The stored procedure used eConnect to insert the data into the production and test databases.

"This provided additional system integrity validation (making sure all codes existed and that all data values sent to GP where actually valid)."

"One item of note, it appears that eConnect requires the Class ID information to be rolled down manually to the employee level."

Tips for Footers on FRx Reports

There may be times when the default placement of footers is not acceptable. For example, if a report is multiple pages, the footer is printer immediately under the last line on the page. This presentation can look sloppy and confusing. Another example is when a report is very short and the footer is way down near the bottom of the page. Some users may want the footer information to print higher, more closely to the report.

To fix the problem of the footer printing too close to the last line of the report, you can add the @BLANK code to one or two of the first lines in the footer. Then enter the actual footer codes and/or text on the next lines of the footer. That way, there will always be a line or two between the report data and the footer data.

To fix the problem of the footer printing too low/too far away from the report, you can increase the bottom margin (on the report catalog under Report Options, Page Setup) which will cause the footer to print much higher on the page. I found that 9.0 was the largest bottom footer FRx would except but that was sufficient for a report that had only 6 lines of data.

Row Format Codes to Create Lines and Boxes on FRx Reports

When you are building an FRx report, don't forget that often the layout/presentation of the report can be very effective in making the report easier to read/understand. One of the layout features you might want to consider is the LNE format code. This feature is helpful for separating sections of a report. If you put this format code on a row in your row format, a thin line will be printed across the page. In addition to a thin line, you have other options if you add text in the Description field on the LNE row as follows:
TYPE=2 will print a bold line across the page
TYPE=3 will print a dotted line across the page
TYPE=4 will print two lines across the page, the top one thick and bottom one thin
TYPE=5 will print two lines across the page, the top one thin and bottom one thick

You can also place a box around a row or rows by using the BXB (box begin) and BXC (box complete) format codes. For example, if you wanted to put a box around the Net Income amount, you would put BXB in the format code of the row above the Net Income row and put BXC in the format code of the line below the net income row. If you don't want the box to be around all the columns, enter the desired columns in Column G. If you want the box to be drawn in something other than a thin line, use one of the TYPE= descriptions noted above on the BXB and BXC rows. In this example, a box with a bold outline will be around columns B and C on the Net Income row.

It Pays to Know a SQL Expert, Especially if you are not!

As consultants, we all have aspects of our work that are more frustrating than others. Migration of data is one of those tasks that I prefer to do alone...I tend to swear a lot and it isn't pretty! Last week I had a payroll migration that was one of the most hairy to date and I have been doing this for years.

It started out very vanilla. After all, it doesn't matter the quantity of data...it is all about the quality, right? I only needed current year employee payroll transactions. My methodology is very tried and true. I Download the data into Excel, clean it up, save it, map it to Integration Manager and after a few hit and miss tries...the data has been successfully integrated. I can move on to something more exciting.

My client runs about 2,500 payroll checks a week and there is a massive amount of employee turnover. In the legacy system (which is housed in a separate facility from the new implementation) employees were not inactivated and, after several years, there were literally hundreds of thousands of employee master records.

As in Dynamics GP, the data that I needed was in several different tables, and had to be exported. No problem I think, and dump out the historical payroll transactions via SQL table export. I will do the same thing with the entire employee master record table, Do a VLook-up to parse out the employees that haven't been paid this year...and there you go... I have what I need to import into my new GP database.

Who knew there was a limit to how much data Excel could handle. Yes, I know, there is a max. amount of rows - and Excel 2007 can hold a lot! Apparently, not enough for my employee master and state tax file. After several attempts (and a lot of swearing) I have the data in separate workbooks. Now to get rid of the employees I do not need. Not so easy. The calculation speed was painfully slow and while I could import data to all 1M rows, Excel couldn't run the formula due to memory constraints (so I am told.) 12 hours later I am completely frustrated and at my witts end.

This is where the story gets good. My fairy godmother (a.k.a. my boss) tells me to quit pulling my hair out and call Tom Celvi. He is a SQL wizard! I am a bean-counter and application consultant. I have always thought of SQL as something to be respected and a bit feared. After all, it is the "house" for my GP data.

Silly me! There is a whole other world out there that I owe myself to learn more about. Tom has shown me that SQL is a very powerful tool and can be used for other things besides housing my precious GP databases.

Tom created a database within the clients SQL 2008 environment, imported all my raw, ugly data and through a series events that I can only explain as pure alchemy...he managed to move that cleaned up data into my GP database and I had my data in all the correct payroll tables along with only the 12,000 current employees!

This took Tom 10 hours in total and I bet he didn't swear once! I was then able to successfully integrate the current year payroll transactions via Integration Manager and my my client is live and processing payroll.

Under lessons learned:

1. SQL doesn't have to be feared, it can be your friend (but should be respected)


2. Quality over Quantity is over rated - strive for both because Excel does have its limits.


3. hang out with a SQL guru, it will save on hair dye

Hosting model for Dynamics GP

As you may know Rose Business Solutions has a sister company called Rose ASP. This is a company that hosts all things Dynamics GP and SL and products that tie into those systems. The company works with Microsoft Partners to provide a solution for those customers that don't want to have the huge outlay in buying servers, finding IT resources to maintain the GP install etc. (For a good overview of hosting here's a demo from our own Rock/Movie Star Linda Rose.)



Hosting is a pretty big buzz word now a days. While at Convergence this year I heard several people say "We host" or "We'd like to host". I heard several approaches as to how people host as well.

Here are some of the wide ranging approaches to hosting that I've heard:

• "We provide the hardware, the partner does everything else"
  
• "We only host for our GP clients"
  
• "We can host anything you want. Any module, any application"
  
• "We only host Dynamics products no 3rd party products"
  
• "We offer SPLA pricing"

It will be interesting to see where hosting Dynamics evolves to in the long term. I personally think things are going to get easier with GP and hosting. Right now there are particular challenges with hosting an application that is not necessarily built for an in the cloud delivery model. I'm assuming MBS will catch up with technology and make it easier to host the application.

The advantages of hosting are great for most customers. As my good buddy Jason Young of RoseASP put it:


"What you really get out of hosting is an infrastructure and connectivity that is out of reach for most companies. It's like buying an airplane ticket. Owning a 747 is waaaay beyond most people. But buying one seat and using its facilities is well within reach."


I see hosting or more specific how Dynamics is deployed in the future effecting many Dynamics partners and customers.

Where do you thing hosting is going for the Dynamics product line?