Forum Stats

  • 3,827,538 Users
  • 2,260,790 Discussions


Lookup - Currency Conversion

User_YXKGO Member Posts: 49 Blue Ribbon
Hi All,

I have a fact table where the price is stored in USD. It also has a column for Qty.
Now the requirement is that the user should be able to select a particular currency in the dashboard and I need to display the values in that currency by using a conversion table.

My approach to achieve this:

• Create a session (non system) variable in rpd.
• Capture the user selected Currency from the dashboard using the above variable
• Have the conversion table as a lookup in rpd. (keys are : source currency which is USD in my case, Dest Currency and Rate)
• Create a logical column in the fact table and use SPARSE function to join with the conversion (lookup table).But here I need to refer to the user selected value (Session variable).
• Add another logical column to the fact, to perform Qty * <Above mentioned logical column which has the rate>

• Is it possible to refer to a session variable with SPARSE function?
• Can I populate the session variable with user selection value from dashboard prompt?

Any other approach / solution is most welcome . Please help


  • User_YXKGO
    User_YXKGO Member Posts: 49 Blue Ribbon
    Guys, Any solution?
  • Why are you doing this? With lookup table and SPARSE join, it looks rather complicated.

    Just bring the currency table in physical layer, create alias, define join with fact table, bring this currency table in the logical table source of the fact, bring the "Display currency" and "conversion factor" from currency table, create a measure to multiply dollar with "conversion factor" and put new measure as well as "Display currency" in presentation table.

    In the report, define prompt on "Display currency" and include new measure in the report. That would do it.
This discussion has been closed.