How to fix error when running AX 2012 reports: “An exchange rate cannot be found for exchange rate type ZZZ between currencies XXX and YYY on exchange date”

I have worked with several customers whom have experienced errors when running AX 2012 reports involving Multicurrency transactions where the error message reflects:

“An exchange rate cannot be found for exchange rate type ZZZ between currencies XXX and YYY on exchange date.”

At initial glance, the error message does not appear to be appropriate and it is difficult to determine what is causing the error message. What typically happens when this error message occurs is that the SSRS report uses the ExchangeRateHelper class and the ExchangeRateHelper class expects there to be a record in the EXCHANGERATE table with a blank VALIDFROM date as the following type of query will be executed:

SELECT TOP 1 T1.EXCHANGERATECURRENCYPAIR,T1.EXCHANGERATE,T1.VALIDTO,T1.VALIDFROM,T1.MODIFIEDDATETIME,T1.RECVERSION,T1.RECID FROM EXCHANGERATE T1

WHERE

 ((EXCHANGERATECURRENCYPAIR=‘5637144576’)AND((VALIDFROM<=‘1900-01-01 00:00:00’)AND(VALIDTO>=‘1900-01-01 00:00:00’)))

The report will typically error out in a scenario where this query does not return a valid record from the EXCHANGERATE table. What this means is that from the Currency exchange rates form in AX 2012 for the Exchange rate type that is assigned to the Legal Entity’s Ledger that is having the problem, you need to have a Exchange rate defined where the Start date is Blank. For example, to fix the problem, take the following steps:

1.  Go to General ledger | Setup | Currency | Currency exchange rates to open the Currency exchange rates form.

2.  In the Currency exchange rates form, select the Exchange rate type that is assigned to your Legal Entity’s Ledger, and then select the appropriate From currency\To currency pairing.

3.  In the Display valid exchange rates for the date range area of the Currency exchange rates form, enter a Blank From Date, and enter today’s date in the To date field.

4.  Scroll to the top of the exchange rates listed. Click on the first exchange rate, click the Add button, and then add the following line:

 

Now that this Exchange Rate line is added to the Exchange Rate type, the AX 2012 Report that leverages the ExchangeRateHelper class will now find a record in the EXCHANGERATE table having a blank VALIDFROM date field. You should now find that the AX 2012 Report will run without the error message occurring. It should be considered ‘Best practices’ to have an initial Blank line assigned to your Currency pairs in your Exchange Rates Types so that AX 2012 can always find an exchange rate.