So this comes from the "why bother but hey, why not" file. A financial report listing the currency exchange rates should look like this (actually, this is a screenshot of the final result).
So, what's so hard about this? In this case, the exchange rates come from the GL and have more than 9 decimal places - in this case, up to 12. Fine, HFM works with it. But, in Financial Reporting, you're limited to 9 decimal places. So, how to build a financial report showing the full resolution like above?
Step 1 - build the report as normal, including showing 9 decimal places
Step 2 - Format the data cell(s) for the rates to scale the data by .001 - this will not be on the dropdown so just type it.
Step 3 - Format the data cell(s) for the rates to show numbers with a period for the thousands separator and nothing for the decimal separator. If a period isn't desired, then use whatever character - the idea is we're going to make this look like a decimal even though it's really thousands.
Step 4 - Use a conditional format on the data cell(s)to check the current cell value. If the value is less than 1, format the positive number prefix to be 0. - see screenshot below. Also keep the other formatting options (9 decimal places, scale by .001).
At this point run the report and you should be all set with the result at the top. In this case I was lucky and the exchange rates only went out 12 places. If they went out more, then the fake decimal would not be where the thousands are. I could add more zeroes to the scaling, but then I'd have the fake decimal in the wrong place or worse, two decimals in my amount. At that point you may be better off with a simple data extract.
Necessity is the mother of invention.
