So today I wanted to share with you how Management Reporter (MR) handles currency translation.
MR R2 CU6 and CU7/CU8 (the latest) are handling currency conversion much differently.
The CU6 version does currency conversion in AX using the reporting currency as opposed to CU7 or CU8 which does the currency conversion in MR directly, I am going to explain both setup:
Management Reporter MR CU6 and prior:
In order to generate reports in a different currency than the one setup as accounting currency in the company in AX, you have to setup a reporting currency or create a consolidation company in the currency you want to report on.
- The reporting currency has to be defined when the company is created and cannot be changed once transactions are posted. To change it you will need to use the periodic function located under GL > Periodic > Currency conversions > Ledger reporting currency conversion.
Once transactions are posted, you can create reports in Management Reporter and force the currency you want to display using the currency display drop down on the column definition of your report. You will have the option to pick the accounting currency or the reporting currency.
- The second option is to create a consolidation company in AX in the currency you want to report on. Consolidate all your transactions by specifying which exchange rates to use (refer to previous post on this blog on how to execute consolidation). Then in Management Reporter you can create a report and run it for the consolidation company (no need to specify the currency to display as it will take the accounting currency of the company by default)
Management Reporter MR CU7 and after:
In the newest versions of Management Reporter, the currency translation happens "on the fly" when the report gets generated. It is now possible to display a report in any currency as long as a rate is setup in AX.
The first step to have this work is to setup each main account with the currency conversion method MR will use. Go to General Ledger > Main Account and under the Management Reporter fields group.
The exchange rate type will point to the correct set of rates that must be used. Remember that you can save several sets of rates (daily rates, historical rates, spot rates...). Refer to previous post on this blog for this. Then the currency translation type will specify how to use this rate
The currency transaction types are:
- Average: This calculates an average rate based on all the rates in effect for the period. The formula used is (Total of exchange rates/Number of exchange rates)
- Weighted average: This also calculates an average rate based on all the rates in effect for the period and in addition takes into account the number of days each rate was effective. The formula is as follows (Exchange rate * Days in effect)/Days in period
- Current: This will use the rate that is effective on or before the date of the report or column in Management Reporter
- Transaction date: This method is used for fixed assets accounts. The exchange rate used is the rate on the day the asset was acquired. If a rate is not entered for that date, the rate used is the previously entered rate closest to the asset acquisition date.
This setup has to be made on each account of your COA. Main account templates could be used in order to mass update your COA (General Ledger > Setup > Chart of Accounts > Main Account Templates)
Also, retained earnings account should be setup in order for the balance to be calculated correctly. The main account category of the retained earnings account must be set as "RETEARN". If manual entries are posted to the retained earnings account, Management Reported will not calculate the balances correctly. A separate account must be created to handle manual adjustments to the retained earnings balance.
Upon execution of the report in Management Reporter, the balances will be displayed in the currency of the company by default. Then you can switch to any currency you want by using the web viewer (change your options in MR to make sure you use the web viewer, the "Use Management Reporter Report Viewer as default viewer" should be unchecked).
In the web viewer click the Currency button and select the currency to show. It might take few minutes to display the data as MR has to translate those numbers based on the setup.
I hope this post will help you and as usual if you need more information or have any questions, I will be more than happy to help :) Enjoy!
