How to Reverse SSRS Recursive Hierarchy Sorting from Parent-Child to Child-Parent

Question

I am trying to reverse the sorting of a recursive hierarchy in a report in SSRS 200R2. The report is built over an SSAS cube that contains the required hierarchy.

I read the answers to this topic: SSRS ignores sort in a recursive hierarchy and I understood that I can't sort the hierarchy between levels. However, what I want to do is reverse the sorting from Parent-Child to Child-Parent, rather than the sorting between levels, and I can't find a way to do it.

Here's an example of what I mean:

A recursive hierarchy of employees and a summed measure appears likes this:

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

I'd like to see the same information, but I want to have the children appear at the top and the parent at the bottom of its group:

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

I tried adding a new field, "employee_level", that represents the level of the employee in the employee hierarchy that I've used to create the report, but sorting the tablix by "employee_level" did nothing.

Here is the query I used in MDX to get the above output:

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

Is it possible to achieve the above reversal? If so, how?

edit: What I'm trying to do is to get the reverse order in the SSRS report. Adding ORDER to the MDX isn't effecting the end report.

whytheq whytheq · Accepted Answer · 2016-05-19T17:30:47

Can you use the ORDER function within the SELECT clause?

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

How to Reverse SSRS Recursive Hierarchy Sorting from Parent-Child to Child-Parent

1 Answers