Advanced Topics in MDX - Professional SQL Server Analysis Services 2005 with MDX - page 284

The results of the above MDX query will only have the states that are within a
specific country instead of a regular cross-join of the members of both hier-
archies. Alberta, which is a state in Canada, does not exist in Australia and
hence you do not have a tuple containing Australia and Alberta in your result.
SCOPE Statement
If you want to perform certain operations within the scope of the entire cube,
you will typically have the calculations defined within the MDX scripts.
CREATE and DROP SUBCUBE statements (you learn later in this chapter)
are statements that can be used to restrict the cube space within the session
scope at query time. You cannot use the CREATE SUBCUBE statement with-
in MDX scripts. However, Analysis Services provides another statement
called SCOPE. The SCOPE statement restricts the cube space so that all
MDX statements and expressions specified within the SCOPE statement are
evaluated exactly once against the restricted cube space. Named Sets in the
MDX script are not affected by the SCOPE statement. The syntax of the
SCOPE statement is:
SCOPE <SubeCubeExpression>
<MDX Statement>
<MDX Statement> ...
END SCOPE
You can have one or more MDX statements within the SCOPE statement and
you can have nested SCOPE statements. Nested SCOPE statements can of-
ten be simplified as a single SCOPE statement as long as all the MDX state-
ments are within the innermost SCOPE statement. MDX statements ex-
pressed within SCOPE statements are really cell calculations, which you
learn about in the next section. An example of SCOPE statement is:
SCOPE
(
[Date].[Fiscal Year].&[2002],
[Date].[Fiscal].[Month].Members,
[Measure].[Sales Quota Amount]
) ;
This = [Date].[Fiscal].CurrentMember.Parent
/ 3 ;
End Scope ;