Currency Conversion

[riaarora]

Dear All,

Just want to know if anyone has implemented Currency Conversion in Cognos FM.

We need to implement the Currency Conversion as follows:

Data stored in Database is in Base Currency of the country.

We have created FX table which will have:
Period:
Scenario: Budget, Actual
Currency From: EUR, CAD, JPY, GBP, CHF, USD
Currency To: This is the parameter user will pass and according to selected parameter conversion needs to be done dynamically.
FX Rate Type: Monthly Fixed, Monthly Avg, Qtrly Avg, YTD Avg
FX Rate: Rate

We have another requirement on top of this. Constant Rate, Historical Rate.

So above FX Rate Type can have Constant Rate, Historical Rate as well.

Please share your experience about the implementation.

Thanks
Ria

[k2]

Hi Ria,

Did you managed to solve your currency conversion issue as am in the similar situation as yours... Any help would be higly appreciated. Thanks

Kind Regards,
¬K2

[Lynn]

I did something similar a few years ago. I don't remember all the little details but I believe I edited the relationship to include a prompt macro for the target (To) currency and just a straight join for the source (From) currency based on the base currency associated with the fact data. This ensured that only one From/To combination could be used. Something like:

Code Select Expand

[FactData].[CCYCode] = [FX].[FromCCYCode] and [FX].[ToCCYCode] = #prompt('Select Target Currency', string, 'USD')#


As for different time frames, I needed to model for the FX rate in effect at the time the transaction occurred ("as of") as well as for the current FX rate.

I handled the "as of" by including dates in the join expression. In my case there was a start and end date for each FX rate record (the most current rate had an end date far in the future). My fact data had a transaction date so an additional condition something like:

Code Select Expand

and [FactData].[DataDate] between the [FX].[FXStart] and [FX].[FXEnd]

All of this ensured that only one FX rate record could be used and the expression was in the relationship (clicked the ellipsis to edit the relationship expression directly).

I included the rate in the model query subject named something like "As Of FX Rate".

For the current FX rate, I used an alias shortcut of the FX rate table and altered the join expression to use current date instead of the fact data date so I could create the "Current FX Rate" query item.

I should mention that the entire financial industry fell into the toilet and the economy plunged into a recession shortly after my assignment there ended, but I'm fairly certain it wasn't caused by my FX rate modeling  :o