So, there is a question that comes up with the intercompany matching report. We load data and consolidate. We have entities with multiple currencies and all of the parent entities are a single currency. Why then is there a need to also run a translation step in order to compare all of the multiple currency entities in the same common currency as all of the parents?
Looking at the intercompany matching report, the solution looks easy. Change the Currency dimension to Parent Currency. It works for regular reports, right? Here's what happens when the report is executed.
You can change the Currency dimension to a reporting currency, but then the translation process is required. If the Currency dimension is left at Entity Currency, then each entity will be compared in its local currency, which is not helpful at all.
So, what can be done?
Create a member in one of the custom dimensions but not under any hierarchy, like a sibling of Total Custom or whatever. Tag it as dynamic calc. Then, create a member formula as shown below.
This formula pulls the parent currency data of the top level custom dimension member into entity currency for this calculated member. Change "Total Custom" to be the top of the hierarchy.
Last, in the intercompany matching report setup, use this calculated member for that custom dimension and set the Currency dimension in the POV to Entity Currency. Running the report with this configuration, the parent currency data will be shown, which is perfect and the translation rule process can be skipped.
The report header will still say "Entity Currency" although the header will also list the new custom member as well. A small price to pay to skip the translation step!
