Excel Style Advanced Selections

From Version 9.1 It is possible to 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.

This functionality can be enabled per grid by checking the "Allow Excel-style advanced sets" property in the Grid Properties screen. A new menu option will appear under the normal "Apply" right click menu allowing the new functionality.

Data options

When you right click on a data cell you will get the following options:

Sorting

If you right click on a data cell and sort you have two options to choose from

• Sort order
  • Ascending (1-9)
  • Descending (9-1)
• Sort target
  • Standard - Level sorting - members of the currently selected level are sorted.
  • Entire Grid - All members of the grid are sorted regardless of their level

In these examples the selected cell was used when sorting.

Sorting by Level

Sorting by Grid

Members

When you right click on a member cell you will get the following options:

Sorting

Member sorting always applies to the selected level, you can choose

• Sort order
  • Ascending (A-Z)
  • Descending (Z-A)

Filtering

Member filtering always applies to the selected level.

Label Filtering

There are a number of text comparisons you can use to filter the level members.

• Equals
• Not Equals
• Begins with
• Not Begins with
• Ends with
• Not Ends with
• Contains
• Not Contains

This example uses "Begins with"

Value Filtering

Value filtering applies to the total value for each the member, you can use the following numeric comparisons

• Equals
• Not Equals
• Greater than
• Greater than or Equals
• Less than
• Less than or Equals
• Between
• Not Between

This example uses "Between"

Ranking

You can select only the top/bottom members of a level by value by clicking a member and picking Top 10.

You can have a different ranking on each level. This example has the top 3 countries, and the top 4 regions for each country.