Order

Function Order() arranges members of a set, optionally preserving or breaking the hierarchy.

If you just want to order the report rows by the measure value you can explore standard functionality.

Syntax

 Order(Set_Expression, Value)
 Order(Set_Expression, Value, Symbol)

Arguments

Set_Expression

MDX expression that returns set.

Value

Value by which this set should be ordered

Symbol

ASC, DESC - orders ascending or descending within members hierarchy

BASC, BDESC - orders ascending or descending overriding members hierarchy

Examples

The following example would return resolution date of the last resolved sub-task of the issue.

  -- Order function starts
     Order(
		 -- set of sub-tasks for the issue
        Filter(
           Descendants([Issue.Sub-task].CurrentMember,
             [Issue.Sub-task].[Sub-task]),
           NOT isempty ([Issue.Sub-task].CurrentMember.Get('Resolved at'))), 
		 -- value for ordering
        [Issue.Sub-task].CurrentMember.Get('Resolved at') ,
     -- symbol
        DESC
     )
  -- Order function ends
  .Item(0).Get('Resolved at')

The next example is eazyBI default calculated member in Sprint dimension All closed sprints. The calculated member pulls in all sprints from any board and orders them by start date. Symbol BASC is used to ignore boards and order sprints from any board by start date:

Aggregate(
-- Order function starts
  Order(
  -- set
    Filter([Sprint].[Sprint].Members,
      [Measures].[Sprint Closed?] = 'Yes' AND
      NOT isEmpty([Sprint].CurrentMember.Get('Complete date'))),
  -- value for ordering
   [Sprint].CurrentMember.Get('Start date'), 
  -- symbol
   BASC)
-- Order function ends
)

The sample report Sprint velocity chart in our demo account uses the calculated member All closed sprints in Sprint dimension to show only completed sprints in the report.