You can add Excel style set selections to a grid.

These functions work in a similar way to Excel Pivot Table sorting/filtering, applying to specific levels of the result set. This is in contrast to standard ranking, sorting, and filtering, where the function is applied to the set of members selected.

This can also be useful for sorting large datasets when Analysis Services sorting is slow. This style of sorting may perform better.

Enable this functionality by selecting Allow Excel-style advanced sets in the Grid properties screen. A new menu option appears under the Apply right-click menu.

  • Sort. You can select from these options:
    • Sort level. Sorts within the level of the currently selected member
      • Ascending (1-9) 
      • Descending (9-1)
    • Sort grid. Sorts the entire grid
      • Ascending (1-9) 
      • Descending (9-1)
  • Filter. Filter by level, or filter the entire grid.
  • Rank. Rank by Level, or Rank the entire Grid.