Adding Currency Conversion - Expert Cube Development with SSAS Multidimensional Models - page 196

SCOPE( Leaves([Date]) ,
Except(
[Reporting Currency].[Currency].[Currency].Members,
{[Reporting Currency].[Currency].[Currency].[USD],
[Reporting Currency].[Currency].[Currency].[Local]}));
SCOPE( { Measures.[Amount]} );
THIS = [Reporting Currency].[Currency].[USD] /
(Measures.[Rate], LinkMember(
[Reporting Currency].[Currency].CurrentMember,
[Currency].[Currency])) ;
END SCOPE;
END SCOPE; // Leaves of time and non pivot currency
END SCOPE; // Measures
The first assignment converts the Local Currency into the Pivot Currency, USD,
as in the previous scenario. The second assignment converts the Pivot Currency we
just calculated into the Reporting Currency. This second statement has a slightly
different SCOPE to the one we saw in the first scenario, because it has to exclude both
the USD and Local members from the Local Currency dimension (which is simply
named Currency).
After running the wizard, we can combine any Local Currency with any Reporting
Currency, as in the following screenshot:
Notice that, once again, a special Local member has been added to the Reporting
Currency dimension. And once again, an exchange rate with the value 1 for the
currency used as Pivot Currency has to be present in the Exchange Rate measure group.