Management Reporter Currency Translation– Where do I start troubleshooting when currency values are wrong…

Hi All,

Over the past few months, I have had a number of cases regarding currency amounts not correct in Management Reporter. These cases have ranged from anything from just a few incorrect accounts to all incorrect accounts and even a specific column not pulling correct values. So, I really just wanted to put a little information together all in one place to help with troubleshooting currency in Management Reporter. Please keep in mind this is not a complete troubleshooting guide, but more of a starting point to hopefully help you narrow things down to a possible cause. Also, this blog is going to focus on currency translation in Data Mart.

Ensure Management Reporter Currency Translation is correctly setup:

The first thing you will want to verify is that you have correctly configured currency transaction for Management Reporter. You can double check your setup with information from this article: Currency Translation for Microsoft Management Reporter 2012 with Microsoft Dynamics GP data mart

Currency Transaction Type:

One of the first things to look at is to make sure the account(s) has the correct Currency Translation Type (Current, Average, Historical) setup. Each of these types calculate values ​​a little different. You can verify this by going to the Account Maintenance window and clicking the currency button.

If you are asking yourself, what should the Currency Translation Type be set to, we typically see the following currency transaction type use with the following accounts.

Current – Balance Sheet Accounts

Average – Income Statement (Profit/Loss) Accounts

Historical – Retained Earnings, Property, Plant & Equipment and Equity Accounts.

Below are some great blogs regarding how currency translation calculation works based on each of these types:

MR currency translation using Current:
Management Reporter currency translation using the current translation type – Microsoft Dynamics GP Community

MR currency translation using Average:
Management Reporter currency translation using the average translation type – Microsoft Dynamics GP Community

MR currency translation using Historical:

Management Reporter currency translation using the historical translation type – Microsoft Dynamics GP Community

Exchange Rate tables:

Management Reporter does not like Exchange Rate tables to have overlapping or gaps in the date ranges. Depending upon the currency type selected, your exchange table may be the issue as Management Reporter does not know what to read when there are overlaps or gaps in dates.

Missing account information in the MC00200 table:

If you have missing account information in the MC00200 table, this could also cause translation issues. To determine if you have any missing account, run the following script. If you do get results, then you will want to open up a support case.

SELECT * FROM GL00100 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM MC00200) AND ACCTTYPE = 1

Quick Example you can setup to test values:

Below is an example of a quick little test that you can very easily do to check for currency amounts to help determine what value is wrong or incorrect to focus on. My example I have an account setup with the currency transaction type of current. Also, I am just pulling some small data for one period.

First, create a test row format and enter in the account number for one incorrect account.

Now build a very simple column layout to include the functional, originating and conversion column as I have listed below. My functional currency is Z-US$ and the originating is CAD. You will want to enter in your correct currencies.

Below, you will see the values ​​in the exchange rate I am using in this example.

I created a couple of new journal entries. In the GL200 table, you will see the amounts in both the functional (DEBITAMT/CRDTAMNT) and for the originating amount (ORDBTAMNT/ORCRDAMT) at the time of posting. Journal entry JE 4967 was entered as functional currency (Z-US$). Journal entry 4968 and 4969 where entered in originating currency (ZC$) with an exchange rate of 1.5.

Now, generate the report. I did mine with the detail of Financial, Account & Transaction. When you look at the results, you will see the Functional column is pulling the functional amounts. The Natural-Originating column is only pull transactions which have currency CAD (ZC$). Then the Conversion column is doing the conversion to the account translation type (current) where it takes the functional and converts it. If you if you take 100/2.5, you get 40, If you take the total of 350/2.5 you will get 140.

Now if I were to take and change this account to an ‘Average’ currency translation type, you will see that I get ta different calculation in the conversion column based on the ‘Average’ calculation method.

I hope you find some of this information helpful when trying to troubleshoot Management Reporter Currency Translation.

Janelle Montplaisir

Microsoft Dynamics GP Support

.

Leave a Comment