How to override SSAS Business Intelligence 'Currency Conversion' for Liability calculation

Question

I'm setting up a new cube in Analysis Services, and used the Business Intelligence wizard to work out the currency conversion issues. Now this all works perfectly, money is converted at the leaf level and summed up for display in the user's choice of reporting currency.

My problem now is the calculation of liability. For liability, I need to sum up the money in each currency, then convert it using the most recent 'End of Day Rate'. I have 'End of Day Rate' as a LastNonEmpty measure, but I can't see how to avoid the leaf-level conversion as shown below:

// This is the Many to One section  
// All currency conversion formulas are calculated for the pivot currency and at leaf of the time dimension 
Scope ({ Measures.[Money] } ); 
  Scope( Leaves([Date]) ,[Reporting Currency].[GBP], Leaves([Currency])); 

    // Convert Local value into Pivot currency for selected Measures that must be         converted with Measure rate [End Of Day Rate] 
        Scope( { Measures.[Money] } )
            This = [Reporting Currency].[Local] * Measures.[End Of Day Rate]; 
        End Scope; 
  End Scope;    

  // This is the One to Many section
  // All currency conversion formulas are calculated for the non pivot currency and at leaf of the time dimension   
  Scope( Leaves([Date]) , Except([Reporting Currency].[Currency].[Currency].Members, {[Reporting Currency].[Currency].[Currency].[GBP], [Reporting Currency].[Currency].[Currency].[Local]})); 

    // This section overrides the local values with the Converted value for each selected measures needing to be converted with Measure rate [End Of Day Rate]… 
    // LinkMember is used to reference the currency from the source currency dimension in the rate cube. 
    Scope( { Measures.[Money] } ); 
        This = [Reporting Currency].[Currency].[GBP] / (Measures.[End Of Day Rate], LinkMember([Reporting Currency].[Currency].CurrentMember, [Currency].[Currency])); 
    End Scope;

  End Scope;  // Leaves of time, all reporting currencies but local and pivot currency  
End Scope;  // Measures

The [Money] measure is paid in in different currencies, and each currency is keyed to a currency dimension and an 'End of Day Rate.

What is my best plan for calculating the liability? I'm considering replicating the [Money] measure, but it seems wasteful to have extra measures in just to avoid the currency conversion - Plus in the real cube there are several more measures that require the calculation so it won't just be the extra one.

Anyone else faced something similar?

Meff Meff · Accepted Answer · 2010-02-19T08:46:40

OK so I ended up creating an invisible [Measures].[Money - Liability] which wasn't auto-converted by the above code, and I ended up with the following calculation in the script:

[Measures].[Liability] = 
(
    SUM 
    ( [Currency].[Currency].[Currency], 
        SUM 
        ( 
            { NULL : [Date].[Date Key].CurrentMember }, 
           (   
                [Money - Liability]
            )
      ) 
      / [Measures].[End Of Day Rate] 
    ) 
    * (Measures.[End Of Day Rate], LinkMember([Reporting Currency].[Currency].CurrentMember, [Currency].[Currency]))
);

How to override SSAS Business Intelligence 'Currency Conversion' for Liability calculation

1 Answers