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

[Currency].[Currency])) ;
END SCOPE;
END SCOPE; // Leaves of time and non pivot currency
END SCOPE; // Measures
This script is interesting as it shows off some of the capabilities of MDX. The nested
SCOPE statements define the area within the multidimensional space of the cube that
the assignment affects. In the first and third SCOPE statement, the space is restricted
to the Amount measure. The second SCOPE is the most important one because it
defines the granularity of the assignment we are going to do. The LEAVES function
returns the most detailed level of the Date hierarchy (which is an attribute hierarchy
in our sample cube) that corresponds to the day level. The remaining part of the
SCOPE excludes the Pivot Currency from this calculation. Thus, the assignment will
change the value of the Amount measure only at the day level. If we will query any
part of the cube above the day level (for example, a month or a year), the calculations
will take place at the day level and the result will be aggregated up to the level we're
querying at. The important concept here is that a scoped assignment at a low level of
granularity on a real measure leads to the results of the calculations being aggregated
up using the natural aggregation behavior of the measure (which is SUM in our case).
The assignment inside the SCOPE statements converts the Pivot Currency value
(which is in USD) into the selected currency in the Reporting Currency dimension.
The LinkMember function transfers the selection made on Reporting Currency
dimension to the Currency dimension. Since this is the case, the user might not ever
need to select anything directly on the Currency dimension, so it could be made
invisible to them to avoid confusion.
In the following screenshot, we can see the result of a query which has the Reporting
Currency dimension on rows and two dates on columns. The original data contained
sales of 210.00 USD for both the selected days. The conversion to the other two
currencies has been made using the daily exchange rate applicable for each day. Thus,
values above the day granularity cannot be pre-calculated using cube aggregations,
because MDX Script will always aggregate the daily calculated data at runtime.