We have subs in Australia and the UK, with the USA being the parent company (we do have oneworld)...
Essentially I am trying to produce a report showing amount collected by customer per quarter (IN USD) across ALL SUBSIDIARIES...
It is a transaction (payment) search, and grouped by customer... I can "sum" the "Amount" field, which works the way it should... However, when trying to break this up into quarterly periods (as columns) I seem to have a major issue...
CASE WHEN TO_CHAR({trandate},'YYQ')='091'THEN {amount} ELSE 0 END
Produces the amount collected in Quarter 1 of 2009 denominated in the base currency of the sub from which it was invoiced... What gives???
How come "Amount" is denominated in USD "consolidated amounts" when using the system field (far left column in the saved search setup), but whenever I use it in a formula field on a saved search it seems to jump back to being denominated it the particular transaction's subsidiary's base currency???
I have seen this bug on a bunch of saved searches, it is infinately annoying. Does anybody know how I could fix it? If this is by design, why is there no {consolidatedamount} or similar field that can be used in formulas... Also, sometimes, the {amount} in a formula will be consolidated (INTO USD), and sometimes it will not. This seem very dangerous...
Any ideas?