Budget Import & Export

Dynamics GP has a very easy to use import and export function that allows you to create budgets in Excel and update budgets in GP, based on amounts you have in Excel.

All this functionality is available from the Budget Selection screen.  Navigate to:  Cards  >>  Financial  >>  Budgets

I created this short video to show this functionality in action.

One very important caveat:  Make sure that you validate the information in GP after importing or updating budget information from Excel.  There is no error reporting on the import function if the import does not properly import the data.

I usually create a quick FRx report for this purpose.  But you could also simply export the information from GP, to a new spreadsheet (make sure not to overwrite your original spreadsheet).

Simple SSRS Reports Using Report Builder

SQL Reporting Services is functionality that's included with Microsoft SQL Server. There are many reports that are delivered out-of-the-box with GP that can be deployed to your SSRS site. But there is also an easy to use reporting tool that allows you to create custom reports.

Report Builder is accessed through the SSRS site. It allows you to use existing objects from which to report; and the objects are pretty standard. When you create a new report you have the option of creating a table style, matrix style, or chart report. You can easily drag and drop fields on to the report, and format the report to look the way your wish.

Once you have your report you can utilize the functionality in SSRS to schedule and deliver the reports. The report export options include: XML, CSV, PDF, Excel, and others.

If you have not yet tried creating a report in SSRS, you should. It's easy to get started, and I'm sure you'll like the results. Here is a report I created in five minutes. Think what you could do in 20.

Macro Mania

I discovered another mis-use of Excel this week. We have a client that receives a transaction download from their bank. They then import this into Dynamics GP to create general ledger transactions.

The problem is that several years ago someone crafted a nifty Excel spreadsheet to format the data in order to facilitate the import into GP. In the spreadsheet there is a hefty reliance on macros to organize, edit, and fill the source data. It may have worked initially, but now it does not; and is not worth the trouble to fix.

We will most likely develop a simple SQL Server / program code application to do the work. It will most likely take less time to deliver, and cost the client less.

Excel is a wonderful tool for analysis, and reporting; and for modeling business processes. It is not a good tool for reliable, well controlled business transaction processing.

Using GP Macros to Import Data

1. Determine what information is needed to complete the record in Great Plains. Create an Excel spreadsheet of that information. i.e. Vendors that you want to inactivate. Your spreadsheet would be vendors #'s that you want to inactivate.

2. Open the window in Great Plains you want to update. (Vendor Maintenance)

3. You need to record the macro you want to run. Tools->Macro -> Record. It will prompt you to name/save the macro (vendor.mac) Proceed with entering a samples transaction (Put in the vendor #, inactivate the vendor
and save) Go to Tools->Macro->Stop

4. Open your macro file using Microsoft Word. It will open the document and look similar to the attached file named bom.mac.

5. Select Tools -> MailMerge function within Word and follow the wizard.
a. .Main Document select Create ->Form Letters -> Active Window
b. Get Data - Go - Open Data and select your file you created in step 1. remember that it is an Excel file. You will get a message that no fields match - Select Edit Main Document
c. Your original .mac file will be highlighted in red. Select the fields within the single quotes i.e. ' "Vendor" ' and choose the field you want to populate the data with under Insert Merge Field button.
d. After all the fields are populated Select Merge
e. Merge to new document and select enter
f. Save your file as .txt and name it filename.mac (it needs to have the .mac extension) see example attached.

6. Open up window in Great Plains you want to update (vendor Maintenance) Put the cursor on the field that you started recording your macro on and Select Tools->Macro->Run Select the file you created in step 5f and go.

7. The macro will run automatically until finished. You can't use the computer until it finishes or it will error out. If the macro errors out it will stop on the record you will need to delete the rows prior to the error in order to continue.

Drowning in Spreadsheets

I met with a Quickbooks user today. They have at least three business functions that are being maintained exclusively in Excel. Periodically they analyze the spreadsheets and enter the information into Quickbooks. I continually meet people that maintain critical business functions in spreadsheets. They have hundreds of them. I have met people that don’t know what to do because they’ve reached the 65,000 row limit! So what’s wrong with this?

This is what’s wrong:

No enforcement of essential business rules.

No audit trail of changes.

Suboptimal security access and control.

Huge likelihood of data corruption and data loss.

Unwieldy calculations, links, and macros that render spreadsheets impossible to maintain.

Excel is a wonderful tool for analysis; not great for business transaction processing. These are common business functions that are often maintained in Excel, but more effectively performed in Dynamics GP:

Function: Revenue Deferral
GP Solution: Revenue and Expense Deferral, Project Accounting, Contract Administration

Function: Timesheets
GP Solution: Project Time and Expense

Function: Fixed Assets
GP Solution: Fixed Assets module