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.
What’s happening?
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.
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.