订购了一套复杂(Ordering a complex set)

我一直在玩发现这里的克里斯·韦伯的脚本： http://cwebbbi.wordpress.com/2007/06/25/advanced-ranking-and-dynamically-generated-named-sets-in-mdx/

该剧本改编是这样的：

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];

如何ORDER输出，使每组十名员工是为了1到10，与MEMBER叫RestOfEmployees总是在11位发现也就是说，它应该遵循会员排名第10位？

诱惑是添加ORDER在标记点功能， HERE即线将变为：

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

这样做，将导致以下错误信息：

尺寸“[EmployeeSet0]”未在立方体发现当字符串，[EmployeeSet0]，被解析。

我相信这是因为该措施EmployeeRank使用生成函数过程中创建的内联集。

Answer 1:

如果重新定义EmployeeRank是11 RestOfEmployees ，你可以在成员刚加入到集合是第一个参数Filter ，而且比申请Order ，因为这将位置1后进行排序，以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];

文章来源: Ordering a complex set