Just today, I did a test installation of Management Reporter 2012 and migrated my client’s FRx reports using the migration wizard. There are quite a few useful posts out there but I ran into an issue that hasn’t been documented very well. Before running the migration wizard, be sure to prepare your data using the following support post:

While going through the migration wizard I came to the following screen, the instructional document (MRforDynERPMigrationfromFrxGuide_ENUS.pdf) said to enter the server name as a URL with the port number then choose “Test Connection” which caused my computer to hang for 10 minutes.

Here are the steps to take to get through this “Specify Connection” step in the Migration Wizard:

1. Enter the server name in the server name box such as server1, do not enter the server name in this format http://server1:4712

2. Do not choose the test connection button, otherwise you’ll get the message as seen below “Connection attempt failed. Ensure server, login credentials, and database are correct”. Also, be sure to be logged in as the same user that you used to install Management Reporter as that’s the dbo of the MR database.

3. On the database button just choose the drop-down and you’ll see all the databases for the server.

4. Then just choose the Management Reporter database and choose next. If you don’t see the databases in the drop-down then check to see you have permissions within SQL to the server.

The client is running eConnect for Salesforce.com integrations and I found the following script that resolved the eConnect_Out_Setup table. The script is part of the GP2010 upgrade.pdf document. Before running the script I could not create a company but afterwards I was successful. Due to ongoing Salesforce integrations, I tested this out in our sandbox (test) environment.

/*This script will update the eConnect_Out_Setup table in each company database and set the DATACNT value to 72 for the DOCTYPE of ModifiedItem. The DATACNT contains an invalid value of 73 for the DOCTYPE of ModifiedItem*/

/*Script Revised: 4/21/2010*/

declare @dbname char(5), @statement char(1500)
declare eConnect_Out_Setup_Fix cursor for
select INTERID from DYNAMICS..SY01500 where INTERID in (select name from master..sysdatabases)
set nocount on
open eConnect_Out_Setup_Fix
fetch next from eConnect_Out_Setup_Fix into @dbname
while (@@fetch_status <> -1) begin
set @statement=’
if exists (select * from ‘ + rtrim(@dbname) + ‘.dbo.sysobjects where id = object_id(”dbo.eConnect_Out_Setup”) and OBJECTPROPERTY(id,”IsUserTable”) = 1)
update ‘ + rtrim(@dbname) + ‘.dbo.eConnect_Out_Setup set DATACNT = 72 where DOCTYPE = ”ModifiedItem”
print ”The eConnect_Out_Setup table has been updated for ‘+ rtrim(@dbname) + ””
exec (@statement)
fetch next from eConnect_Out_Setup_Fix into @dbname
end
deallocate eConnect_Out_Setup_Fix
set nocount off

Microsoft Dynamics GP Overview with Steve Reese

In order to expand our coverage of available accounting software options, ASD sought out Steve Reese, an expert with Microsoft Dynamics GP. Steve has consulted businesses using Dynamics GP for over two decades. He founded his company Reese Consulting in 1991, and continues to provide expert service to companies looking to utilize their financial software systems as efficiently as possible.

Due to this product change, Microsoft released (6/28/11) a Forecaster service pack 3 that has over 90 bug fixes. Plus, they released service pack 2 for Management Reporter (6/28/11) which does the following:
1. Data provider updates to Dynamics AX 2009, Dynamics, GP 2010 and Dynamics GP 2010 R2.
2. Improves support for multiple Active Directory domains
3. Better support for rounding and adjustment periods.
4. Performance improvements for larger databases.
5. Improved error handling.

When you use the Migration Wizard in Microsoft Management Reporter to migrate all your FRx data not all information is migrated. The following list of features is not part of the migration process. Many of these features exist in Management Reporter, they’re just not part of the migration tool:

This is a modified Dynamics GP report (see download) I’ve provided to my clients for years that can be exported to a text file and imported into excel that includes the customer ID, name, and the aging columns. Through GP security, you can only have one modified report for each user and company if you’re running a shared modified reports dictionary for each workstation. What this means is if you already have an existing modified historical aged trial balance detail report you may have to provide a separate login or shortcut to this report to have access to both. Here’s a screenshot of the report in excel.

You have to add totals and grand total line, be sure to exclude the “Original Amount” column.

After you download the package(s) from above by right-clicking and choose ‘save as’, you can import it in version 10, 2010, 2013 by going to Microsoft Dynamics GP>>tools>>customize>>customization maintenance and choose import. Before doing so, be sure to back up your modified reports to ensure nothing is overwritten.

If you need the summary trial balance export packages just create a comment below and I will email them to you.

The basic problem is how do I create a single set of financial reports in US dollars when I do business in multiple countries and currencies which is basically called “Consolidations”. There are quite a few alternatives to consolidations in FRx and your decision may depend on many factors such as type of assets held, how many foreign currencies are held, materiality of foreign currencies held, even the fluctuation of those foreign currencies to your reporting currency as well as various requirements to do business in foreign countries. Here are a few methods of how consolidations can be done in FRx:

1. Soft consolidations – this is where no transactions are recorded to do the consolidations, it’s all done at the time each report is created. FRx provides a module called the “FRx Currency Translator” that converts all foreign currency companies to US dollars as the report is generated. This is tool is limited in use since it “plugs” differences into a currency translation adjustment account.

2.Hard consolidations – this is where you may have a separate company database to convert all foreign currency companies to US dollars then FRx does all the reporting from US dollar company. This can be done as an import or there are some 3rd party tools such as “Multi-National Consolidations (MNC) from tensoft.com. This is more work but if you have a significant portion of foreign-held investments a hard consolidation is your best solution because it will always be in balance (no plugging) and FRx handles all the reporting.

3. Excel consolidations – this is where you export all foreign currency financial statements into excel in the same format using FRx, then convert, eliminate, consolidate, and report in excel. This is do-able but if you’ve spent enough time doing financial statements you already know these pitfalls.

Each company you have in your underlying general ledger system requires you to determine your “functional currency” which is the currency of the “primary economic environment in which that entity operates” which may or may not be the US dollar. In most GL systems, you must choose your functional currency during the initial setup of a company and it cannot be changed, for more information on the FASB that determines this see below:

Problem: In one column in FRx how can I have YTD amounts and statistics where the statistics need be based on “net change”? By nature, YTD columns include any accounts with beginning balances including statistical accounts so how can I work around this issue?

P&L report – in the column layout below column B would not work because it doesn’t eliminate the beginning balance issue for statistical type (unit) accounts. However, column C would give YTD amounts for the income statement and exclude beginning balances for statistics because income statement, by nature, do not have beginning balances. A balance sheet column is trickier.

Balance sheet report – A balance sheet report requires a YTD column figure to include beginning balances but for statistical accounts we need to exclude them, so what’s the work-around? One way would be to delete the beginning balances through SQL, here are some statements, the first for the open year table and the second for the history table:

BEFORE running this script be sure to take a backup of your database, an even safer method would be to create a test company and run the script there then confirm your balance sheet reports is complete before running the script again in your live company. Also, this deletes all beginning balances for all statistical accounts so you may need an additional (SQL statement) restriction.

After running the SQL script, run reconcile (tools>>utilities>>financial>>reconcile) to recalculate the period (summary) balances table.

Not all Dynamics GP users own integration manager nor does integration manager support every field, another way of importing data is to use a macro. This feature could be used to insert new records or make updates and it could be an unlimited amount of changes. It does become useful to update records for maintenance purposes or adding cards such as vendors or customers, but does not do well for recurring integrations. However, I don’t recommend this for recurring integrations where stability, performance, and error checking is required. These types of integrations are better served through integration manager, eConnect, or a custom-built integration through Scribe Software, Boomi, or a web service. I recommend starting small by creating a macro with only a 5-10 changes before attempting larger tasks. I have used this function for many Dynamics GP implementations over many of versions in the past 5-7 years so it’s worth learning especially if you don’t own integration manager and you’re an end-user of Dynamics GP rather than a developer or database administrator.

In this example, I’m going to update the descriptions for a set of general ledger accounts. Here is a list of steps I will explain in more detail later:
1. Create a macro to update your first two records which will create a text file.
2. Open the macro in a text editor and determine what code is needed to use in the macro.
3. Create an excel file of changes along with a named range.
4. Do the mail merge in Microsoft Word
5. Save the mail merge as new text file then add the first two lines of the original macro.
6. Run the macro from the same window you recorded it from.

Step 1 – Create a macro…

For my example I’m editing GL accounts so go to the account maintenance window, then start the macro from the tools>>macro>>record or Alt+F8. It will ask you to name the macro file and choose ok. Be prepared to enter two records of data without the use of any lookup windows. Now, go through these steps:
1. Enter in the first GL account you want to edit and choose the tab key.
2. Then edit the description field and choose ‘save’.
3. Enter in the next GL account you want to edit and choose the tab key.
4. Then edit the description field and choose ‘save’.
5. Stop your macro from tools>>macro>>stop

Step 2 – Open the macro in a text editor…

The unmodified macro I created in step 1 will look like the following, the first section is part of any macro specific to the window, the second section is the first account and the last section is the second account. The difficult part of any macro is figuring out what makes up the section you’ll use in the mail merge which is why I entered two accounts in the macro, and figuring out what fields you’ll use for the mail merge. You’ll use the second section or paragraph to use for your mail merge so highlight it and paste it into a MS Word document. Also, pay close attention to what your excel file will look like, notice my example will require each account segment in its own field.

I am changing the descriptions of general ledger accounts so I enter the accounts and descriptions in excel, notice I split the segments into their own field through the use of functions (mid, left, right).

Start the macro by going to Mailings>>Start Mail Merge>>Step by Step Wizard, when you get to step 3 which is “Select Recipients” choose browse and go to the excel file.

For each field, you’ll highlight the field you want to replace, choose “More Items” highlight the field then choose insert, replacing the field on the left with the field on the right. Below is a screenshot of an almost completed insert merge.

During the merge when you get to step 6/6, choose “Edit individual letters” and choose to merge all records by choosing ok. Highlight everything by choosing Ctrl+A, copy it, then paste it into notepad, then open up your original macro and paste in the first two lines and save the file as a *.mac file.

Step 6 – Run the macro…

Go into the same window you recorded the macro from and with the cursor in the same position run the macro from the top of the window by choosing tools>>macro>>play and select the macro the merged macro. While your macro is running it will be very sensitive if you click on the mouse anywhere which will break it so I suggest you leave it alone, especially if it’s a long macro.