none
Currency Conversion and Role Playing Dimensions RRS feed

  • Question

  • All,

    I have a applied a many-to-one currency conversion solution in my Cube, to convert currencies depending on a currency type (day of booking and current day), along a Booking Date Role Playing Dimension :-

    I need to be able to convert currencies along several other Role Playing Date Dimensions (Invoice Date for example), BUT use the day of booking to do the actual conversion.

    For example, I have several bookings that are not yet invoiced, and have been given a default Invoice Date of 01/01/1900. Of course, there is no Currency Conversion Audit for 01/01/1900, so I will need to use the Booking Date to apply the Conversion, despite seeing, say, Revenue, at Invoice Date.

    It will be very difficult for me to do the conversions in the fact Table due to the Numbers of Measures and "Conversion Types" that need to take place. It will mean updating and re-procesing the whole fact table each day

    Here is the Conversion Code :-

    SCOPE 
      {
        [Measures].[Revenue]  
      };               
      SCOPE 
        (
          Leaves([Booking Date])
         ,[Reporting Currency].[Great British Pounds]
         ,Leaves([Currency])
        );               
        // Convert Local value into Pivot currency for selected Measures that must be converted with Measure rate [Inverse] 
        SCOPE 
          [Currency Type].[Currency Type].&[1];            // Day of Booking
          THIS = 
            [Reporting Currency].[Local] * Measures.[Inverse];            
        END SCOPE;            
        SCOPE 
          [Currency Type].[Currency Type].&[2];            // Currenct Day
          THIS = 
            [Reporting Currency].[Local] * Measures.[Current Day Inverse];            
        END SCOPE;                         
      END SCOPE;               
    END SCOPE;          

    Thanks

    Paul

    Wednesday, April 4, 2012 12:52 PM

Answers

All replies