Management Reporter – Currency Translation with Microsoft Dynamics GP 2010


As mentioned in the announcement for Management Reporter Feature Pack 1, currency translation support has been added to Management Reporter.   The currency translation feature works in conjunction with the currency enhancements added in Microsoft Dynamics GP 2010 SP1, giving customers the ability to create financial statements in a specific reporting currency.

However, before discussing the new currency translation feature, I would like to give a brief overview of existing currency functionality.

In Management Reporter there are two currency related fields that can be selected in the column definition:   Currency Source and Currency Filter

Currency Source- This specifies the type of currency (Natural, Functional, or Reporting) that the transactions will be displayed in.

Currency Filter- When selecting a currency filter, only transactions entered in the selected currency will be displayed in the report.

 

So for example, the user chooses the following:

Currency Source:  Functional (US Dollars)

Currency Filter:  Canadian Dollars

Results:  The column will print only transactions that were entered in Canadian Dollars, but will display those transactions in the Functional Currency (US Dollars).

As I noted above, with Management Reporter FP1, there is the option of selecting a reporting currency within the Currency Source.   This functionality is sometimes referred to currency translation, which is the ability to report general ledger amounts in a currency that may not be the functional currency of the company and it may not be the currency the transaction was entered in. 

Within Dynamics GP, multiple reporting currencies can be setup.  Therefore, when the user selects the Currency Source, they will see 1 to many reporting currencies, depending upon the Dynamics GP Multi-currency setup.   This allows for users to report on transactions, in a currency other than the one it was entered in.   

For example, if the company’s functional currency is U.S. Dollar and all transactions were entered in US dollars, but the user simply wants to report those amounts in Japanese Yen, the user would select the Japanese YEN reporting currency.   This would translate all functional currencies to YEN. 

Also, the Currency Filter will work in conjunction with the Reporting Currency. 

For example, the company may want to see all the transactions that were entered in Euro for the company, but would like to see these reported in Japanese YEN (even though the Functional is US dollars).  The user would simply select the Japanese YEN Reporting Currency in the Currency Source (this tells Management Reporter to display the amounts in YEN) and then select the Euro in the Currency Filter (this limits the transactions displayed to only transactions entered in the Euro currency). 

You might be asking yourself, how do I get started?  First you will need Management Reporter FP1.   Then you will need to:

  •  Load Dynamics GP 2010, Service Pack 1
  •  Update Multicurrency Setup
    •  Select the currencies to translate
    •  Choose the exchange table ID’s for each currency translation type
    •  Choose the exchange table ID for budget translation
  •  Update currency translation type for accounts
    •  Set currency translation type for each account
    •  Mass update accounts for currency translation type
  •  Enter translated beginning balance information
    •  Translated beginning balance information for average and historical types
    •  Enter translated beginning balance amounts for a new translation currency
    •  Correct translated beginning balance amounts

 

Click this link to find information on how to perform the necessary currency setup in Dynamics GP to utilize the currency translation in Management Reporter.  NOTE:  This link will bring you to the ‘Service Pack, Hotfix, and Compliance Update Patch Releases for Microsoft Dynamics GP 2010’ page.  Scroll down to the ‘Current Release Downloads’ section.  In the NOTES section, select the hyperlink in bullet number three.

For information on Currency Translation in AX and SL, look for a future blog post.

Comments (25)

  1. Teri Michaels says:

    How does it handle Average, Spot and Historical?

  2. Shiela Sogge says:

    The translation type (Average, Spot and Historical) is defined at the account level.  Management Reporter makes a call back to GP, and looks at each account in the report, determines the translation type, and grabs the appropriate rate based on the accounts translation type.  

  3. Sandeep Nair says:

    Hi Shiela – how does the translation work when a range of periods is provided in the column format?

    e.g. Converting from Euro to USD. Period Covered = 1:BASE, and assume base period is April. The Accounts in GP are setup as Average, and i store daily rates in GP (EUR-USD)). Does MR get ONE average rate for the 4 months, and use that for the translation?

    Thanks,

    Sandeep.

  4. Dynamics AX says:

    Hello Shiela,

    How does it work with Dynamics AX?

    I follwed the steps described above but could not manage to generate a report in another currency than the ones used for posting purposes.

    Hope you can help.

    Cheers,

    Ludwig

  5. Shiela Sogge says:

    Hi Ludwig,

    The blog posting above pertains to Dynamics GP.  We will be following up with a posting on Dynamics AX.  However, with Dynamics AX we simply pull in the reporting currency amounts stored in the GL tables.  

    Shiela

  6. Shiela Sogge says:

    Hi Sandeep,

    The Average rate is per month, and then those monthly amounts are summed together.

    Shiela

  7. Robert says:

    hi

    this very nice and good quality based blog

  8. David Siegel says:

    Hi Shiela –

    Can you please advise regarding reporting in foreign currencies for Dynamics SL 2011?

  9. Is the Currency Filter field indicated optional or required?

  10. Susie Hadley says:

    Do you have a similar posting for Dynamix AX 2009? I would like to know how the GL tables are updated in AX so as to provide a translated report using Month End rate for Balance Sheet and Average for P&L.

  11. Jill Carter says:

    Steve – Currency Filter is not required.

    David and Susie – We will be following up the posts regarding currency translation for Dynamics AX and Dynamics SL.  Stay tuned.

  12. Robert O'Donnell says:

    Any more details on how AX 2009 and MR currency translation will work?

  13. Joe Guyton says:

    The hyperlink that you refer to in the Notes section for bullet three has been removed from the page that you are referencing.  Can you please provide an updated link?

  14. CharG says:

    Joe — can you be more specific on which link you need? Thanks

  15. Robert O'Donnell says:

    When will MR support reports with currency translation with more than one month? For example, we have  P&L reports with the 12 months as seperate columns. I understand that MR 2012 will not use the correct AVG rate for each column for each month. It uses the AVG rate for the base period for each column.

  16. CharG says:

    With the Management Reporter 2012 Rollup 2 that was posted today, Management Reporter 2012 will return individual exchange rates for each column of data for General Ledger accounts, using the Current or Spot rate translation type in Dynamics GP. This allows for comparative balance sheet reporting.

  17. Victor Alvarado says:

    Hello

    I follow the instructons cliking in the link as you mention.

    "Click this link to find information on how to perform the necessary currency setup in Dynamics GP to utilize the currency translation in Management Reporter.  NOTE:  This link will bring you to the ‘Service Pack, Hotfix, and Compliance Update Patch Releases for Microsoft Dynamics GP 2010’ page.  Scroll down to the ‘Current Release Downloads’ section.  In the NOTES section, select the hyperlink in bullet number three."  

    but i didn't see bullet three!!!

  18. Norma Soto says:

    Hi Shiela, do you know how the report gets the exchange rate for each period? I'm testing a report with accounts with no transactions in original currency and it gives me an amount with a different exchange rate  for each account.

  19. CharG says:

    Check the Dynamics GP documentation for details on how the specific exchange rate for each different account type is determined.

  20. Doug says:

    Hi,

    I have set up MR on a test server to create a translated P&L and Balance Sheet. I have it working for SPOT and Average rate translation, but can;t get it to work for Historic.

    For Historic I have it set up the same as Spot and Average. My test transaction is from Jan 1, 2011 so I have a Rate table set up for a rate on Jan 1,2011. It will not translate.

    if the amount are from prior years do I need to set up opening translated balances?

    thx,

    Doug

  21. L Phillips says:

    We are looking to transfer from FRx to MR but I am struggling with the historic rate translations.  If we have several accounts that use different historic rates in the same period, how does MR handle this?

  22. Jahangir says:

    Kindly help me to fix my issue. I post company budget in Saudi Riyal, but our reporting currency is USD, so I am unable to see data in management reporter. I use each and every element in currency filter and currency display.

    Thank you very much.

  23. CraigF says:

    So if I understand properly the Currency Translation Type (Average, Current, Historical)that is chosen for a GL account directly relates to the Exchange Table ID assigned in the Multicurrency Setup column labeled Average, Current and Historical.

    Ex- I designate 'Current' for my Cash account translation type. I setup a Management Report column and choose GBP as my Currency Display. When the report is generated, the translation to GBP will look at the Multicurrency Setup screen in GP under the 'Current' column for the Exchange Table ID that was chosen for GBP and use the appropriate rate from this Exchange Table?

    The 'Historical' or 'Average' columns on the Multicurrency Setup screen don't come into play unless the GL account setup for Currency Translation Type has been designated as 'Historic' or 'Average'?

    Is this correct?

  24. Nikita says:

    I am trying to set this up with dynamics GP 2013 sp1 and it is not giving me any values.