Hi all,
I would like to know if it is possible to develop OBIEE 11g repository structure in combination with analysis formulas to calculate exchange rate on the report with the next logic:
Table FACT:
GL_DATE VALUE_ORIG CURR_ORIG COMPANY VALUE_COMP CURR_COMP
18/2/2016. 100 USD 1000 90.22 EUR
19/2/2016. 110 EUR 1000 110 EUR
19/2/2016. 150 USD 1100 135.18 EUR
Exchange rate table:
DATE FROM_CURR TO_CURR RATE
22/2/2016 USD EUR 0.9069
19/2/2016. USD EUR 0.9012
18/2/2016. USD EUR 0.9022
On the report there is a prompt for Date and Currency. Lets say 22/2/2016. is selected as default DATE and EUR as default Currency. Presentation variable PV_Date_1 has the DATE prompt value.
The report calculates the SUM of VALUE_COMP column of all the records which have DATE column less than prompt date.
The result for 22/2/2016. in EUR is 335.4.
Customer wants to see the value in USD calculated in a way explained below:
Column CURR_ORIG (if not in USD) calculated in USD, but with the exchange rate on prompt date (22/2/2016.), in our test case that would be the record in the middle in fact table. VALUE_ORIG = 110 * (1/0.9069) = 121.292
So the SUM would be 100 + 121.292 + 150 = 371.292.
Tricky thing is that the date of exchange rate is determined on the prompt. We can't have precalcualted values for running sum of all the possible dates. Second tricky thing is that we can't sum VALUE_COMP and multiply the result with exchange rate on prompt date, because VALUE_COMP value reflects the VALUE_ORIG value with exchange rate on GL_DATE.
Thank you