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

The wizard then finishes and displays the Completing the Wizard summary
page. At this point we have a new Reporting Currency dimension, which has its
IsAggregatable property set to False , and contains only two members: one is USD,
which we have used as the Pivot Currency, and the other is the constant Local . The
default member for this dimension is USD, but the user can always choose to see the
original value of the measure in its Local Currency by choosing the Local member on
the Reporting Currency dimension.
If the data type of the field used to store the currency name in the
Currency attribute on the Currency dimension is less than five
characters long, the wizard creates a Reporting Currency dimension
with an attribute that is too short to hold the string "Local" . To avoid
a truncation error during dimension processing, use a field at least
five characters long to store the currency name in the relational
table on which the Currency dimension is based. Alternatively, we
can modify the Named Query that the wizard generates in the Data
Source View for the Reporting Currency dimension, for example,
using a cast in the SQL statement.
This is the MDX created by the wizard:
SCOPE ( { Measures.[Amount]} );
SCOPE( Leaves([Date]) ,
[Reporting Currency].[USD],
Leaves([Currency]));
SCOPE( { Measures.[Amount]} );
THIS = [Reporting Currency].[Local]
* Measures.[Rate];
END SCOPE;
END SCOPE;
END SCOPE; // Measures
The code here is similar to the MDX generated in the previous scenario. In this case,
we have a single Reporting Currency (USD) and the conversion formula is applied to
all the Local Currencies through a scoped assignment on LEAVES([Currency]) .