Specify connection error in Management Reporter 2012 FRx Migration wizard

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:

http://support.microsoft.com/kb/2425087

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.

 

Posted in Management Reporter | 3 Comments

eConnect error when adding a new Dynamics GP company

During the creation of companies in Dynamics GP version 10 I came across the following error:

The following SQL statement produced an error:
set nocount on declare @NAME varchar(100) declare @TEXT varchar(8000) declare
taCreateRequesterProcs insensitive cursor for select DOCTYPE from
eConnect_Out_Setup (nolock) where MAIN = 1 and DOCTYPE not like ’%GetList’
open taCreateRequesterProcs fetch next from taCreateRequesterProcs into @NAME
while (@@fetch_status <> -1) begin if (@@fetch_status <> -2) begin select
@TEXT = ’exec eConnectOutCreate ‘+ @NAME + ‘, 0’ exec (@TEXT) select @TEXT =
‘exec eConnectOutCreate ’+ @NAME + ’, 1’ exec (@TEXT) select @TEXT = ’exec
eConnectOutCreate ’+ @NAME + ’, 2’ exec (@TEXT) end fetch next from
taCreateRequesterProcs into @NAME end deallocate taCreateRequesterProcs

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

Posted in Dynamics GP | 1 Comment

New features – Dynamics GP 2012

Here are some of the new features slated for Dynamics GP 2012 which is scheduled to be released in 2012.

1. Select a printer after viewing a report onscreen.

2. Print SSRS reports from a GP window (GP report writer’s not going away yet).

3. Journal entry history inquiry window

4. Fixed asset calendar setup

5. PM reprint check remittance

Posted in Dynamics GP | Leave a comment

Interview with Ryan Zweng

Ryan Zweng from accountingsoftwaredeals.com interviewed me about Dynamics GP and its usage.

Here’s the interview:

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.
Posted in Dynamics GP | Leave a comment

Microsoft’s change in Corporate Performance Management Roadmap

Microsoft has changed their Product Road Map for Corporate Performance Management which includes Management Reporter -a Financial Reporting tool (FRx replacement) and Forecaster – a financial budgeting tool. At Microsoft’s recent conference (Convergence), the Corporate Performance Management products show “continued support of Forecaster”. Originally, Microsoft was going to extend budgeting into Management Reporter and discontinue Forecaster, not anytime soon.

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.

Posted in Forecaster, Management Reporter | Leave a comment

What does not migrate from FRx to Management Reporter

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:

Catalog of reports:
1. Report Chains
2. Passwords
3. Web publishing settings
4. Page breaks between units
5. Allow column text overflow
6. Effective dates
7. Subtotal and filter account detail
8. Email
9. Currency translation rates

Rows:
1. Account sets
2. Account types
3. Currency format codes
4. Row linking
5. XBRL link

Columns
1. XBRL column

Trees
1. Page breaks
2. Security

For more tips and tricks on using the migration wizard from FRx to Management Reporter you can view the KB article ID 2425087

https://mbs2.microsoft.com/Knowledgebase/kbdisplay.aspx?wtntzsmnwukntmmyxpvsklryklwntmmryxkwvwxvxkowuumm

Posted in Management Reporter | Leave a comment

Export the Dynamics GP Payables or Receivables Historical Aged Trial Balance to Excel

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.

Payables:

Download report

 

Receivables:

Download report

 

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.

Posted in Dynamics GP | 111 Comments

Consolidations (of multiple currencies) in FRx

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:

http://www.fasb.org/summary/stsum52.shtml

Posted in FRx | Leave a comment

YTD amounts and statistics in one column in FRx using Dynamics GP

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:

Delete GL20000 from GL00100, GL20000 where GL00100.ACTINDX = GL20000.ACTINDX and GL00100.ACCTTYPE = 2 and GL20000.SOURCDOC = ‘BBF’

Delete GL30000 from GL00100, GL30000 where HSTYEAR = ‘XXXX’ and GL00100.ACTINDX = GL30000.ACTINDX and GL00100.ACCTTYPE = 2 and GL30000.SOURCDOC = ‘BBF’

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.

Posted in FRx | Leave a comment

Importing to Dynamics GP without integration manager or SQL

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.

# DEXVERSION=10.0.324.0 2 2
CheckActiveWin dictionary ‘default’ form ‘GL_Account_Maintenance’ window ‘GL_Account_Maintenance’

TypeTo field ‘Account Number CS’:’Account_Segment_Pool1′ , ‘000’
MoveTo field ‘Account Number CS’:’Account_Segment_Pool2′
TypeTo field ‘Account Number CS’:’Account_Segment_Pool2′ , ‘1100’
MoveTo field ‘Account Number CS’:’Account_Segment_Pool3′
TypeTo field ‘Account Number CS’:’Account_Segment_Pool3′ , ’00’
MoveTo field ‘Account Description’
TypeTo field ‘Account Description’ , ‘Cash-Wells Fargo’
MoveTo field ‘Account Alias’
MoveTo field ‘Save Button’

ClickHit field ‘Save Button’
TypeTo field ‘Account Number CS’:’Account_Segment_Pool1′ , ‘000’
MoveTo field ‘Account Number CS’:’Account_Segment_Pool2′
TypeTo field ‘Account Number CS’:’Account_Segment_Pool2′ , ‘1101’
MoveTo field ‘Account Number CS’:’Account_Segment_Pool3′
TypeTo field ‘Account Number CS’:’Account_Segment_Pool3′ , ’00’
MoveTo field ‘Account Description’
TypeTo field ‘Account Description’ , ‘Cash-Canda-Bank of America’
MoveTo field ‘Save Button’
ClickHit field ‘Save Button’

Step 3 – Create an excel file of changes…

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).

Seg1 Seg2 Seg3 Account Description
000 1100 00 000-1100-00 Cash-Wells Fargo
000 1101 00 000-1101-00 Cash-Canada-Bank of America

And I created a named range in excel and called in “Accounts” then saved the file

Step 4 – Do the mail merge…

Here’s the repeating part of the macro that should go into a new document.

TypeTo field ‘Account Number CS’:’Account_Segment_Pool1′ , ‘000’
MoveTo field ‘Account Number CS’:’Account_Segment_Pool2′
TypeTo field ‘Account Number CS’:’Account_Segment_Pool2′ , ‘1100’
MoveTo field ‘Account Number CS’:’Account_Segment_Pool3′
TypeTo field ‘Account Number CS’:’Account_Segment_Pool3′ , ’00’
MoveTo field ‘Account Description’
TypeTo field ‘Account Description’ , ‘Cash-Wells Fargo’
MoveTo field ‘Account Alias’
MoveTo field ‘Save Button’

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.

Here’s what the document should look like before the merge

TypeTo field ‘Account Number CS’:’Account_Segment_Pool1′ , ‘«Seg1»’
MoveTo field ‘Account Number CS’:’Account_Segment_Pool2′
TypeTo field ‘Account Number CS’:’Account_Segment_Pool2′ , ‘«Seg2»’
MoveTo field ‘Account Number CS’:’Account_Segment_Pool3′
TypeTo field ‘Account Number CS’:’Account_Segment_Pool3′ , ‘«Seg3»’
MoveTo field ‘Account Description’
TypeTo field ‘Account Description’ , ‘«Description»’
MoveTo field ‘Account Alias’
MoveTo field ‘Save Button’

Step 5 – Save the mail merge as new text file…

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.

Posted in Dynamics GP | 2 Comments