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>
Concerns:
• 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