While accessing reports in the Procurement and sourcing module, you may notice a handful of reports that allow you to choose a currency for the results to display. Specifically, reports in the “Procurement and sourcing”->”Reports”->”Statistics”->”Spend analysis”, are all based on the standard Purchasing cube. This ability to show these values in different currencies is driven by currency conversion functionality in Analysis Services.
The standard AX 2012 Analysis Services cubes use logic similar to the currency wizard native to Visual Studio. For more information about this functionality, click the following link: https://msdn.microsoft.com/en-us/library/ms175660.aspx Although this post won’t get into the actual details of the currency conversion logic used by the Analysis Service project generation wizard, it is important to understand its output. For a many-to-many currency relationship, an “Analysis currency” dimension is created. The selection of the analysis currency determines which currency the results will display in. This analysis currency dimension is what shows in the report dialog. In addition to the convertible currencies, the local currency is also an option. If a member of the analysis currency dimension isn’t selected, the local currency results will be displayed by default. The local currency is the measure in the currency on the record. For example, in a company with USD as the accounting currency and reporting currency, if a sales order is entered in the CAD currency, CAD is the local currency. If you are using the Contoso environment provided on CustomerSource or PartnerSource, the currency conversion does not work. Although this functionality is wonderful, it requires a bit of initial setup (just like the rest of the standard AX 2012 cubes) to make sure the automatic currency conversion works properly.
How to make it work
There are three pieces of data that must be set up properly for the convertible currency codes to display in these dialogs:
- All companies need an accounting currency setup in the general ledger
- A system currency exchange rate type needs to be selected
- Each currency that you would like displayed in the report dialog needs to have a valid exchange rate to EVERY exchange rate used as an accounting currency in the system
Although the first condition is pretty straightforward to verify, it is important to understand that ALL companies need to have an accounting currency chosen. This includes the DAT company and any other AX companies – even those that do not have transactions or are not otherwise used. If the company exists, it needs to have an accounting currency selected. The second piece of data is another basic requirement. Make sure there is an exchange rate type selected in the “system exchange rate type” field in “System administrator”->”Setup”->”System parameters.” This tells the Analysis Services cube which exchange rate to use when displaying different currencies. The third required piece of data is a bit more frustrating. The query used to build the analysis currency dimension table looks at a number of datasets to determine which currencies need to be created. This query adds a filter to only return currency codes where there exists a valid exchange rate to all the accounting currencies selected. In the Contoso demo data set, this data is not properly filled out. As of the demo set provided in the AX 2012 R3 CU8, there are 9 currencies selected as accounting currencies in the various demo companies. In order to get BRL, EUR, and GBP to display in the report dialog, I had to first create exchange rates between each of them and the other 8 currency codes that are selected as accounting currencies. Once you have the three criteria listed above satisfied, re-processing the Analysis Currency dimension should regenerate the currency codes and provide you with an appropriate list of convertible currencies. To find some additional information about the standard Purchase cube and the vendor country/region not displaying correctly, view my colleague’s post: http://dynamicconsulting.com/blog/procurement-and-sourcing-report-countryregion-problem/ If you’ve moved on and upgraded to Dynamics 365, this problem is no longer a concern of yours. Instead, look at this post to learn about the Power BI reports used for Procurement and sourcing.