Excel Style Advanced Selections

From Version 9.2 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 and choose 'Excel style sort/filter' you will get the following options:

Sort

You have four options to choose from

• Sort Level
  • Ascending (1-9) - Sorts within the level of the currently selected member
  • Descending (9-1)
• Sort Grid
  • Ascending (1-9) - Sorts the entire grid
  • Descending (9-1)

In these examples the selected cell was used when sorting.

Sorting by Level

Sorting by Grid

Filter

If you right click on a data cell and Rank you can choose to filter by Level, or to filter the entire Grid.

Rank

If you right click on a data cell and Rank you can choose to Rank by Level, or to Rank the entire 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.