订购复杂的套装 [英] Ordering a complex set

问题描述

我一直在使用Chris Webb的脚本，该脚本位于以下位置:

我如何ORDER输出，以使每10名雇员中的每组都按从1到10的顺序排列，总是在位置11上找到MEMBER称为RestOfEmployees，即它应该跟随排名第10的成员?

诱惑是在标记为HERE的点处添加ORDER函数，即该行将变为:

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

这样做会导致以下错误消息:

在分析字符串[EmployeeSet0]时，在多维数据集中找不到维度"[EmployeeSet0]".

我相信这是因为度量EmployeeRank使用了在generate函数中创建的内联集.

解决方案

如果将EmployeeRank的EmployeeRank重新定义为11，则可以将该成员添加到作为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];

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.

解决方案

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

这篇关于订购复杂的套装的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！