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

FROM (
SELECT ( { [Product].[Product
Categories].[Subcategory].&[26],
[Product].[Product
Categories].[Subcategory].&[27]
}

)
ON COLUMNS
FROM [Adventure Works]
)

)
WHERE ( [Product].[Product Categories].CurrentMember,
[Date].[Fiscal].CurrentMember

)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,
FORMATTED_VALUE

The query contains SUB-SELECT clause twice. The innermost query returns
a subcube that only contains Products of SubCategory ids 26 and 27. As-
sume that this subcube is named subcube A. The second SUB-SELECT uses
subcube A and returns another subcube with the restriction of Fiscal Years
2004 and 2006 . Finally, the outermost SELECT statement retrieves the Inter-
net Sales for Customers in various countries. Here SUB-SELECT queries re-
strict the cube space to certain members on Product and Date dimensions,
thereby the outermost SELECT statement queries data from a subcube rather
than the entire cube space. If you execute the preceding query in SSMS, you
will see the results shown in the following table. You can rewrite most queries
using SUB-SELECT with the WHERE clause in Analysis Services 2005,
which accepts Sets as valid MDX expressions. There are instances where
subsets and where clause can return different results. More information is
provided in the topic MDX Solutions 2nd edition by George Spofford, et, al.
(Wiley Publishing, Inc., 2006). Analysis Services 2005 only uses
SUBSELECT syntax for queries built through the designer that creates re-
ports. You learn more about creating reports on Analysis Services UDM in
Chapter 17 .

Internet Sales Amount

Australia $16,335.00