Archive for November, 2009
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.
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).
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:
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!
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.
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.
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
SmartList Builder & Smartlist Tips & Tricks
Mike Feori covered these subjects:
SmartList Options- Change Basic Defaults
SmartList Searches- Match All, Match One or More
SmartList Export Solutions- Adding Macros
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
Expensing purchases of inventory items
Do Account distributions matter?
Reconciling Inventory to the GL
Historical Inventory Trial Balance
Historical Stock Status Report
The most discussed items were:
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.)
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
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
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.
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.
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:
- Have all Dynamics users get out of the system
- Backup Dynamics and company databases. Tax updates will effect the system/Dynamics tables so make sure that database is backed up
- Enter payroll transactions in a batch. Make sure transactions cover a variety of situations (I.E. Multiple states, dependants, withholdings, deductions, benefits, etc.)
- Build batch and run process through until you get calculation report. Save report, print it out, etc.
- Remove batch from build
- Install tax update. Can do automatically or manually. See MBS payroll site
- Build batch again as in Step 2 and run until you get calculation report
- Verify tax amounts have changed from Step 2
- 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
- We then had the client verify these numbers were correct as well
- Remove build and delete transaction batch used in Step 2 and step 6
- 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
- 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.
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:
- Launch FRx and switch the Default Company to “Fabrikam Works”
- Open the catalog ID “IS_Dynamic_GL”
- 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.
- 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.
- Close the column layout
- Close the report catalog
- Switch the Default Company back to your company
- 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
- 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)
- Wow your boss and others with this informative report!