Querying Your Cube - Getting Started with SQL Server 2012 Cube Development - page 137

BACK_COLOR, FORE_COLOR, FONT_FLAGS
--Query 5.26
SELECT NON EMPTY
Hierarchize({DrilldownLevel({[Order
Date].[Hierarchy].[All]},,,INCLUDE_CALC_MEMBERS)})
DIMENSION PROPERTIES
PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON
COLUMNS ,
NON EMPTY
Hierarchize(DrilldownMember({{DrilldownLevel({[Product].[Product
Hierarchy].[All]},,,INCLUDE_CALC_MEMBERS)}},
{[Product].[Product Hierarchy].[Product
Category].&[1]},,,INCLUDE_CALC_MEMBERS))
DIMENSION PROPERTIES
PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,
[Product].[Product Hierarchy].[Product
Subcategory].[Product Category] ON ROWS
FROM [Adventure Works DW2012]
WHERE ([Measures].[Sales Amount]) CELL
PROPERTIES VALUE, FORMAT_STRING, LANGUAGE,
BACK_COLOR, FORE_COLOR, FONT_FLAGS
The HIERARCHIZE function is used to organize the result set in order of the hier-
archy to get a nice looking PivotTable.
Note
If you want to see what code is sent down to the server, you can use SQL Server
Profiler to catch the queries. Another option is to use the add-in that is available
on the following link: http://olappivottableextend.codeplex.com/ . It has an option
to view the MDX generated by the PivotTable.