如何将SSRS递归层次结构排序从父-子级反转为子-父级

Question

我正在尝试在SSRS 200R2中反转报表中递归层次结构的排序。该报表是在包含所需层次结构的SSAS多维数据集上生成的。

我阅读了这个主题的答案：SSRS忽略了递归层次结构中的排序，我知道我不能在级别之间对层次结构进行排序。然而，我想做的是将排序从Parent Child反转为Child Parent，而不是在级别之间进行排序，而且我找不到方法。

下面是我的意思的一个例子：

员工的递归层次结构和汇总度量如下所示：

Employee | Measure
------------------
Peter    | 10
  Adam   | 3
  Zelda  | 7
James    | 20
  Becky  | 15
  Paul   | 5

我想看到同样的信息，但我想让孩子们出现在小组的顶部，家长们出现在底部：

Employee | Measure
------------------
  Adam   | 3
  Zelda  | 7
Peter    | 10
  Becky  | 15
  Paul   | 5
James    | 20

我尝试添加一个新字段"employee_level"，该字段表示我用于创建报告的员工层次结构中的员工级别，但按"employae_level"对tablix进行排序没有任何作用。

以下是我在MDX中使用的查询，以获得上面的输出：

WITH  
SET Filter_Employee_Hierarchy AS 
FILTER ([Employees].[Emp_Id].ALLMEMBERS , [Measures].[Sum] > 0)
MEMBER Employee_Level AS
[Employees].[Emp_Id].CURRENTMEMBER.LEVEL.ORDINAL 
MEMBER Employee AS
[Employees].[Emp_Id].MEMBER_CAPTION
MEMBER Employee_Parent AS 
[Employees].[Emp_Id].PARENT.MEMBER_CAPTION
SELECT 
{ [Measures].[Sum], Employee_Level, Employee,Employee_Parent } ON COLUMNS, 
{ Filter_Employee_Hierarchy } ON ROWS 
FROM [Cube];

有可能实现上述逆转吗？如果是，如何？

编辑：我想做的是在SSRS报告中获得相反的顺序。将ORDER添加到MDX不会影响最终报告。

Answer 1

您能在SELECT子句中使用ORDER函数吗？

WITH  
SET [Filter_Employee_Hierarchy] AS 
  FILTER (
    [Employees].[Emp_Id].ALLMEMBERS 
  , [Measures].[Sum] > 0 
  )
SET [Filter_Employee_Hierarchy_ORDERED] AS
 ORDER(
    HIERARCHIZE([Filter_Employee_Hierarchy])
   ,[Measures].[Employee_Level]
   ,BDESC  
  )
MEMBER [Measures].[RNKforOrdering] AS
  RANK(
     [Employees].[Emp_Id].CURRENTMEMBER
    ,[Filter_Employee_Hierarchy_ORDERED]
  )
MEMBER [Measures].[Employee_Level] AS
  [Employees].[Emp_Id].CURRENTMEMBER.LEVEL.ORDINAL 
MEMBER [Measures].[Employee] AS
  [Employees].[Emp_Id].MEMBER_CAPTION
MEMBER [Measures].[Employee_Parent] AS 
  [Employees].[Emp_Id].PARENT.MEMBER_CAPTION
SELECT 
  { 
    [Measures].[Sum]
  , [Measures].[Employee_Level] 
  , [Measures].[Employee_Parent] 
  , [Measures].[RNKforOrdering]
  } ON 0, 
  [Filter_Employee_Hierarchy_ORDERED] ON 1
FROM [Cube];