Analyzing Cubes using Office Client Components - Professional SQL Server Analysis Services 2005 with MDX - page 787

select AddCalculatedMembers ( measures.[Sales
Amount]) on 0
from [Adventure Works] //Calculated
measures are returned in result
In the code illustration the first MDX query only returns the result for the Sales
Amount measure; however, the second MDX query returns the Sales Amount
measure and all the calculated members in the measures dimension.
Finally, the result of the AddCalculateMembers function, a Set of members, is
passed as an argument to the Hierarchize function. Hierarchize is an MDX
function used for sorting the members in a set. The syntax is as follows:
Hierarchize (Set, [POST])
The Hierarchize function takes a Set and returns the members in the set after
a sort. If the second parameter POST is not specified, the default sort order-
ing of the hierarchy is used to sort the members in the set. If the parameter
POST is specified, the members are sorted based on the default ordering but
the parent member will be at the end. This is illustrated by the following two
MDX queries:
select Hierarchize (AddCalculatedMembers (
{DrillDownLevel ({[Customer].[Customer
Geography].[Country]})})) on 0,
Measures.[Internet Sales Amount] on 1
from [Adventure Works] //MDX Query 1 -
Default sort ordering
select Hierarchize (AddCalculatedMembers (
{DrillDownLevel ({[Customer].[Customer
Geography].[Country]})}),POST) on 0,
Measures.[Internet Sales Amount] on 1
from [Adventure Works] //MDX Query 2 -
Sort based on POST
The first MDX query uses the default sort ordering of the hierarchy and re-
turns the members in Country and State levels in the format {<Country Mem-
ber 1>, <State members for Country Member 1>, <Country Member 2>,
<State members for Country Member 2> … }. However, the second query that
specified POST sorts the results as {<State members for Country Member