Using Currency Translation with Management Reporter and AX
To use currency translation in Management Reporter, you must first set up your currencies and rates in AX. The following lists the items that must be set up in AX.
- Currencies (General Ledger | Setup) – currencies are created
- Exchange Rate Types (General Ledger | Setup) – exchange rate types are created (aka Exchange Rate Tables)
- Currency Exchange Rates (General Ledger | Setup) – where exchange rates are entered for the exchange rate types
-
Ledger (General Ledger | Setup) – the following translation related settings found here
- Accounting Currency (Functional Currency)
- Reporting Currency (Secondary Currency)
- Default Exchange Rate Type
-
Default Budget Exchange Rate Type – Budget data can be translated using a different rate by specifying a different exchange rate type.
-
Main Accounts (General Ledger | Common) – translation settings specified on the account
- Exchange Rate Type – Specifies which exchange rate type the account will use.
-
Currency Translation Type – specifies which translation type the account will use. You can select either Current, Average, Weighted Average or Transaction Date.
-
Current
- Typically Balance Sheet accounts
- Uses period end (report date) rate
-
Average
- Typically P&L accounts
- Uses average rate for the period; (sum of rates)/(number of rates)
-
Weighted Average
- Typically P&L accounts
- Uses weighted average rate for the period; (rate*days in effect)/(number of days in period)
-
Transaction Date (Historical)
- Typically Retained Earnings, Equity accounts and longer term fixed assets (i.e. Land)
- Uses rate in effect on the date the transaction was entered
-
MR Setup
-
No setup required but there are some settings that will change how translated amounts are viewed on reports.
-
Column Definition
- Use the Currency Display if you want to restrict the column to only display data in the currency specified
- Leave blank if you want to be able to view the data in other currencies. This can only be done in the Web Viewer.
-
Report Definition – “Include all reporting currencies”
- Determines when reports are generated for the other currencies
-
Web Viewer Currency button – to view the report in a different currency click Currency and then select a different currency
- Depending on the option “include all reporting currencies” the report will open or will need to generate before opening.
- If any column had a currency specified in the Currency Display field that column cannot display data in other currencies.
-
When a report is viewed in a different currency, that currency will show in the upper left corner to easily determine which currency is being viewed.
-
Other notes\comments
-
Consolidated reports with companies in the tree that have different accounting currencies will automatically do currency translation. The currency that the report is generated in is shown in the Report Definition
-
In the above example all companies will be displayed in USD. That currency defaults in from the company that is specified in the Report Definition. If @ANY is selected, the default currency will come from the company set as the default in MR Report Designer.
-
-
As noted above, Retained Earnings (Accounts used in General Ledger | Fiscal Year Close | Opening Transactions | “Main account for transfer of year-end result:”) is typically set to use a translation type of Transaction Date. These accounts also have the following requirements to translate as expected in Management Reporter.
-
Must be assigned to the Retained Earnings main account category in the Main Accounts window like this to translate:
- Account cannot have any transactions posted directly to it; only the system generated transactions created by the fiscal year close process.
- There is one limitation with translation of the Retained Earnings account. Most users expect each year’s adjustment to RE to be translated at the rate that exists at the end of that given year. However the entire RE balance is translated at the rate that exists at the end of the most recent year closed. In other words, each year’s data is not translated at that year’s rate.
-
-
In the Column Definition there is a field called Currency Filter. This is not related to currency translation. It can be used to filter a column to only show transactions entered in a specific currency. In AX if transactions were entered in two different currencies in the same company but data was needed for only transactions entered in one specific currency that currency could be entered as a Currency Filter. That column would then only show transactions entered in that currency.
-
MR can return a translated amount even when there is a $0 balance in the Accounting Currency. A simple example to explain this:
- An account is set to Transaction Date
- Debit amount of 1,000 posted to the account on 1/15/2016. Exchange rate on that date of 1.1 so the translated amount is 1,100
- Credit amount of 1,000 posted to the account on 1/22/2016. Exchange rate on that date of 1.2 so the translated amount is 1,200.
- Report generated on 1/31/2016. The amount in the Accounting Currency is $0. The translated amount will be a credit of -100 (1,100 – 1,200).
- This situation can occur with accounts set to Transaction Date or either of the Average translation types.
Additional information on MR and currency translation can be found here:
Using the Average translation type in Management Reporter
Using the Weighted Average translation type in Management Reporter
Using the Current translation type in Management Reporter
Using the Transaction Date (historical) translation type in Management Reporter