Currency conversion

[JeroenWork]

Dear all,

I'm looking for an elegant currency conversion that can be used in a framework that is the basis for a relational and dimensional package.

If this has already been discussed here (must be), can you please post a link? I did search but got lost in the millions of hits...

The solution I implemented so far is to join the fact table to a currency table and put a mandatory prompt on the currency_code. The fact table has a currency_id (that tells what currency the record is in), the currency table has for every currency_id multiple records with the currency it needs to convert to. The prompt then needs to be mandatory otherwise there will be a fan effect.

This solution works ok in query/report studio. As soon as you pick a value you will get prompted.

prompt:
[currency_table].[currency_code]  = #prompt('CurrencySelect','string', '''USD''' , '', '[currency_table].[currency_code]')#

I'm lost though on getting this to work in a dimensional (DMR) model.

Any help would be great.

Thanks,

Jeroen

[Lynn]

I did something very similar although not for DMR.

Did you create a filter on the query subject for the prompt macro? In my case I put the prompt in the join expression between my fact table and my currency table (in the physical layer). I'm limited regarding DMR so I don't really know if placing the prompt one place or another would make any difference....

Maybe explaining what the problem you have with DMR might help someone who actually knows what they're talking about advise you better ;)

[JeroenWork]

Hi Lynn,

Thank you for the response.

The prompt definition is on the currency table. this way the prompt is fired as soon as a measure is inserted into the report.

The real problem in the dimensional model is that the above mechanism works but only once. After the user selects the currency in the prompt there is no way of changing it (that I know of). In query studio the user can select "Run with All Data" to rerun the report and that fires the prompt again. In report studio this prompt is also fired every time a user runs the report.

Ideally I would like at least two things for the users:
- have a mandatory prompt to force the user to select a currency (avoid fan effect) or a non-mandatory prompt that defaults to for instance USD
- a mechanism to reselect a currency using a currency query object that lists all currencies or a filter

Thanks,

Jeroen

[JeroenWork]

Any input would still be great.

Also looking for a way to display the currency symbol.

Thanks!

[blom0344]

For a DMR I would take the route of using a context filter on the currency code. The downside is that this cannot be enforced the way a mandatory prompt can. On a procedural level we try to solve this by adding a folder at the top of the dimensions containing the context dimension for filtering.  (( Mandatory Filter !!!!))
Alternative is to build a template and add this as the default analysis for the package, so users will have the context filter in all cases.

[JeroenWork]

Thank you blom!!!

Ok, I did not completely understand the answer but I will try educate/readup on the contextfilter etc.  :)

I will follow up.

BTW, sometimes I can not read some threads, get errors. Anyone seeing the same or is it me?

[JeroenWork]

I see other users had the same issues with errors on this forum. Guess it will be addressed.

[MFGF]

I see other users had the same issues with errors on this forum. Guess it will be addressed.

I have spoken to the Admin guys at BSP, and the issues should all be resolved now.  If you are still seeing problems getting into posts, please drop me a personal message.

Cheers!

MF.