Ordering a complex set

Question

I've been playing with a script of Chris Webb's found here: http://cwebbbi.wordpress.com/2007/06/25/advanced-ranking-and-dynamically-generated-named-sets-in-mdx/

The adapted script is this:

WITH 
  SET MyMonths AS 
    TopPercent
    (
      [Date].[Calendar].[Month].MEMBERS
     ,20
     ,[Measures].[Reseller Sales Amount]
    ) 
  SET MyEmployees AS 
    [Employee].[Employee].[Employee].MEMBERS 
  SET MyMonthsWithEmployeesSets AS 
    Generate
    (
      MyMonths
     ,Union
      (
        {[Date].[Calendar].CurrentMember}
       ,StrToSet
        ("
         Intersect({}, 
         {TopCount(MyEmployees, 10, ([Measures].[Reseller Sales Amount],[Date].     [Calendar].CurrentMember))
         as EmployeeSet"
            + 
              Cstr(MyMonths.CurrentOrdinal)
          + "})"
        )
      )
    ) 
  MEMBER [Employee].[Employee].[RestOfEmployees] AS 
    Aggregate
    (
      Except
      (
        MyEmployees
       ,StrToSet
        (
          "EmployeeSet" + Cstr(Rank([Date].[Calendar].CurrentMember,MyMonths))
        )
      )
    ) 
  MEMBER [Measures].[EmployeeRank] AS 
    Rank
    (
      [Employee].[Employee].CurrentMember
     ,StrToSet
      (
        "EmployeeSet" + Cstr(Rank([Date].[Calendar].CurrentMember,MyMonths))
      )
    ) 
SELECT 
  {
    [Measures].[EmployeeRank]
   ,[Measures].[Reseller Sales Amount]
  } ON 0
 ,Hierarchize
  (
    Union
    (
      Filter
      (
        MyMonthsWithEmployeesSets * MyEmployees   //<<<HERE<<<<
       ,
        [Measures].[EmployeeRank] >= 1
      )
     ,{
        MyMonthsWithEmployeesSets * [Employee].[Employee].[RestOfEmployees]
      }
    )
  ) ON 1
FROM [Adventure Works];

How do I ORDER the output so that each set of ten employees is in order 1 to 10, with the MEMBER called RestOfEmployees always found in position 11 i.e. it should follow the member ranked 10 ?

The temptation is to add the ORDER function at the point marked HERE i.e. that line will become :

MyMonthsWithEmployeesSets * ORDER(MyEmployees, [Measures].[EmployeeRank])

Doing that results in the following error message:

The dimension '[EmployeeSet0]' was not found in the cube when the string, [EmployeeSet0], was parsed.

I believe this is because the measure EmployeeRank uses the inline sets created during the generate function.

Answer 1

If you redefine EmployeeRank to be 11 for RestOfEmployees, you can just add that member to the set that is the first argument to Filter, and than apply Order, as it will sort after position 1 to 10:

WITH 
  SET MyMonths AS 
    TopPercent
    (
      [Date].[Calendar].[Month].MEMBERS
     ,20
     ,[Measures].[Reseller Sales Amount]
    ) 
  SET MyEmployees AS 
    [Employee].[Employee].[Employee].MEMBERS 
  SET MyMonthsWithEmployeesSets AS 
    Generate
    (
      MyMonths
     ,Union
      (
        {[Date].[Calendar].CurrentMember}
       ,StrToSet
        ("
         Intersect({}, 
         {TopCount(MyEmployees, 10, ([Measures].[Reseller Sales Amount],[Date].     [Calendar].CurrentMember))
         as EmployeeSet"
            + 
              Cstr(MyMonths.CurrentOrdinal)
          + "})"
        )
      )
    ) 
  MEMBER [Employee].[Employee].[RestOfEmployees] AS 
    Aggregate
    (
      Except
      (
        MyEmployees
       ,StrToSet
        (
          "EmployeeSet" + Cstr(Rank([Date].[Calendar].CurrentMember,MyMonths))
        )
      )
    ) 
  MEMBER [Measures].[EmployeeRank] AS 
    IIF([Employee].[Employee].CurrentMember IS [Employee].[Employee].[RestOfEmployees], 
        11,
        Rank
        (
          [Employee].[Employee].CurrentMember
         ,StrToSet
          (
            "EmployeeSet" + Cstr(Rank([Date].[Calendar].CurrentMember,MyMonths))
          )
        ) 
    )
SELECT 
  {
    [Measures].[EmployeeRank]
   ,[Measures].[Reseller Sales Amount]
  } ON 0
 ,
   Order(
      Filter
      (
        MyMonthsWithEmployeesSets  
          * UNION(MyEmployees, {[Employee].[Employee].[RestOfEmployees]})
       ,
        [Measures].[EmployeeRank] >= 1
      ), [Measures].[EmployeeRank]
      )
  ON 1
FROM [Adventure Works];