Sorting using MDX query

Question

I need to sorting like as below without using DrillDownMember,

FY 2005
FY 2004
FY 2003
   H2 FY 2003 
   H1 FY 2003
FY 2002

I need to sort the hierarchy members followed by children sorting. I am trying "Hierarchies" key for this kind of sorting, it works on hierarchies based order only. here is my MDX,

Select 
  NON EMPTY
   ( 
     HIERARCHIZE ( 
       {
         {
           DrillDownlevel(
              ([Customer].[Customer Geography])
           )
         }
       }
     )
   ,{[Measures].[Internet Sales Amount]}
  )
dimension properties 
   MEMBER_TYPE
  ,CHILDREN_CARDINALITY
  ,PARENT_UNIQUE_NAME  ON COLUMNS 
,NON EMPTY
  ( 
    HIERARCHIZE(
      ( 
        {
          (
            {
              ORDER(
                {
                  HIERARCHIZE(
                   {
                     DrillDownlevel(
                      ([Date].[Fiscal])
                     )
                   }
                  )
                }
             ,[Date].[Fiscal].CurrentMember.MEMBER_CAPTION
             ,asc
             )
           }
          ),
      (order ( [Date].[Fiscal].[Fiscal Semester].&[2003]&[2].children , [Date].[Fiscal].CurrentMember.MEMBER_CAPTION,asc))})))
 dimension properties MEMBER_TYPE,CHILDREN_CARDINALITY, PARENT_UNIQUE_NAME  ON ROWS
 FROM [Adventure Works]
 CELL PROPERTIES VALUE, FORMAT_STRING, FORMATTED_VALUE 

Answer 1

Initially I'll attempt to get rid of lots of the braces / brackets as they are just boilerplate and not required - even if I cannot answer the question this should make things a bit more readable for other viewers:

SELECT
  NON EMPTY
     HIERARCHIZE ( 
           DRILLDOWNLEVEL(
              ([Customer].[Customer Geography])
           )
     )
   *{[Measures].[Internet Sales Amount]}
  ON 0
,NON EMPTY
    HIERARCHIZE(
              ORDER(
                  HIERARCHIZE(
                     DrillDownlevel(
                      ([Date].[Fiscal])
                     )
                  )
             ,[Date].[Fiscal].CurrentMember.MEMBER_CAPTION
             ,ASC
             )
      * ORDER( 
         [Date].[Fiscal].[Fiscal Semester].&[2003]&[2].children 
       , [Date].[Fiscal].CurrentMember.MEMBER_CAPTION
       , ASC
       )
     )
   ON 1
 FROM [Adventure Works];

Looking at the result you require I'd have guessed it would be DESC based on the member_value based on date member values being numeric:

SELECT
  NON EMPTY
     HIERARCHIZE ( 
           DRILLDOWNLEVEL({[Customer].[Customer Geography]})
     )
   *{[Measures].[Internet Sales Amount]}
  ON 0
,NON EMPTY
      ORDER(
          HIERARCHIZE(
             {
              [Date].[Fiscal].[Fiscal Year].MEMBERS,
              [Date].[Fiscal].[Fiscal Semester].&[2003].CHILDREN
             }
           )  
          ,[Date].[Fiscal].CURRENTMEMBER.MEMBER_VALUE
          ,BDESC
        )
   ON 1
 FROM [Adventure Works];

Ok - Its actually a bit simpler than the above - no need to include hierarchize:

SELECT
  NON EMPTY
     HIERARCHIZE ( 
           DRILLDOWNLEVEL({[Customer].[Customer Geography]})
     )
   *{[Measures].[Internet Sales Amount]}
  ON 0
,NON EMPTY
      ORDER(
         {
          [Date].[Fiscal].[Fiscal Year].MEMBERS,
          [Date].[Fiscal].[Fiscal Year].&[2007].CHILDREN
         }
       ,[Date].[Fiscal].CURRENTMEMBER.Member_Key
       ,DESC
       )  
   ON 1
 FROM [Adventure Works];

oK #2 - this order the set the way you want - there will be a more elegant way but currently this is all I have:

WITH 
  MEMBER [Measures].[x] AS 
    (EXISTING 
      [Date].[Fiscal].[Fiscal Year].MEMBERS).Item(0).Item(0).Member_Key 
SELECT 
  NON EMPTY 
      Hierarchize(DrillDownLevel({[Customer].[Customer Geography]}))
    * 
      {[Measures].[Internet Sales Amount]} ON 0
 ,NON EMPTY 
    Order
    (
      {
        Order
        (
          Descendants
          (
            [Date].[Fiscal].[Fiscal Year].&[2007]
           ,[Date].[Fiscal].[Fiscal Semester]
           ,SELF_AND_BEFORE
          )
         ,[Date].[Fiscal].CurrentMember.Member_Caption
         ,deSC
        )
       ,Order
        (
          Except
          (
            [Date].[Fiscal].[Fiscal Year].MEMBERS
           ,[Date].[Fiscal].[Fiscal Year].&[2007]
          )
         ,[Date].[Fiscal].CurrentMember.Member_Key
         ,bDESC
        )
      }
     ,[Measures].[x]
     ,bdesc
    ) ON 1
FROM [Adventure Works];

Just in case no believes me...