Ranking, Sorting and Filtering
Ranking, sorting and filtering can be applied in two ways: on the hierarchy or on the grid axis.
Hierarchy mode is accessed through the Advanced tab on the member selector for the relevant hierarchy, whereas Axis mode is achieved by right clicking on the column/row header.
- 1 Quick Ranking, Sorting and Filtering (Axis Mode)
- 2 Advanced Ranking, Sorting and Filtering (Hierarchy Mode)
- 3 Sorting slicer elements
- 4 Sorting by sub-category within category
- 5 Excluding members from a report
- 6 Non Empty
- 7 Driving Reports from Excel
- 8 See Also
Quick Ranking, Sorting and Filtering (Axis Mode)
Axis Mode is a quick approach to ranking, filtering and sorting, and can be thought of as result set ranking. Here the ranking is applied to an entire axis.
For example, if you apply a top 5 ranking while Geography is on rows you get the top 5 Geographies, but if you switch Geography with Product, you will get the top 5 Products without having to apply the ranking. Where you have two hierarchies on the axis, the result will be the top 5 intersections between the two hierarchies.
To apply axis ranking, right-click on the column/row header (not the hierarchy) that you wish to rank and select XLCubed > Apply> Ranking> .... You can see that Top 10 and Bottom 10 appear as options. If you wish to rank any other count you select Top x Rows/ Bottom x Rows and you will be prompted to enter a figure.
A red indicator will appear on the first member of the ranked hierarchy to show that the axis is being ranked. Right clicking XLCubed > Axis > .... will allow you to edit or remove the ranking.
The same method is applied to sort and filter.
For further information, see Axis Ranking.
Advanced Ranking, Sorting and Filtering (Hierarchy Mode)
Filtering, sorting and ranking can be applied directly to hierarchy members in the member selector. In this model, the ranking is contained within the hierarchy itself, so that if you move the hierarchy from rows to columns, the ranking is then applied on columns.
The following examples show how to use Hierarchy Ranking to filter, sort and rank using different members of a Cube. It uses the Internet Sales cube.
You can filter your hierarchies so your report only displays members that satisfy a certain condition. Go to the member selector of the hierarchy you wish to filter. Under the Advanced tab, click the drop down and select filter result, or click on the filter icon.
You can choose which measure to filter by, and what filtering to apply (greater than, equal to etc.) - this can come from an Excel range (jump to Driving Reports from Excel).
Let's say we want to find the products at Product Key level that sold more than 25 units in 2003, Quarter 1 and show the sales figures for those subcategories during 2003 and its quarters.
- Create a grid with Calendar Period on columns and Product on rows.
- We want to filter what products are shown, so click on the Product hierarchy so that its details appear in the right panel. We will choose to show products at the Product Key level.
- Click the Advanced tab and, on the Members dropdown, choose Filter result:
- Click the Calendar Period edit control in the grid to change its selection to the desired member (2003, Quarter 1):
- Select the This measure radio button, and select Order Quantity as the desired measure.
- Change the Operation to >, and type 25 in the edit field on the right:
- Click OK. The new filter is displayed in the advanced selections tab:
- Click OK again to run the Report – the Grid shows the members that fit our criteria:
Sorting members can be accessed in the same way. Select which sorting to apply, and whether to sort ascending or descending. This can also be determined from an Excel range ( jump to Driving Reports from Excel).
Keep children under parent: Select this option to ensure children members are kept below their parent, even if the sorting would have otherwise placed them higher/lower than a higher-level member (jump to Sorting by subcategory).
Example: Sorting on a different dimension
Now let's sort the report in descending order of the Q1 sales.
- Display the Hierarchy Editor for the Product hierarchy by double-clicking on the Product label in the Grid
- Under the Advanced tab, click the Sort result toolbar button ()
- Change the Calendar Period selection to 2003, Quarter 1:
- Click the Sort Descending (9-1) radio button
- Click OK. The new sort is displayed in the advanced selections tab
- Click OK again to run the Report
Ranking can be applied to a member set in the same way. Choose whether to display the top or bottom products ( this can be determined by an Excel cell ) and how many members to display (again, this can be static or determined by an Excel cell - jump to Driving Reports from Excel).
Example: Combining Ranking, Filtering and Sorting
Now let's add a ranking to find the bottom 8 selling products at the Product Key level that have sold more than 25 units in Q1:
- Display the Product Hierarchy Editor dialog
- Click the Rank result icon () on the advanced selections tab to display the Edit Ranking dialog
- Select the Bottom radio button, and type 8 into the edit field
- Select 2003, Quarter 1 for the Calendar Period hierarchy in the grid below:
- Run the Grid: only the lowest 8 members are returned
Sorting slicer elements
The members displayed in a slicer can be controlled in the same way.
For example, let's sort the elements of a slicer showing Customers, so that they appear sorted in ascending order
- Right-click on slicer and select Edit.
- Select Advanced tab and then choose to Sort Result.
- On the drop down, sort by Member name
- Select Sort Ascending(A-Z) and then OK.
The members in my slicer will now be displayed in alphabetical order.
Sorting by sub-category within category
The grid report below is based on the Bicycle Sales cube and it shows a measure called value in 2002 against the three bicycle product groups: Allround, Mountain and Road.
We then include the sub-category into the report and you can see the bicycle models appearing as a list beneath the bicycle product groups.
We would like to sort the grid members so that the categories, Allround, Mountain and Road are sorted by the current measure (value). We also want the sub-categories of bicycle models to be sorted on the same measure within that first sort.
- Under the Advanced tab of the member selector, select the Sort icon or click the drop-down and select Sort Result.
- Choose whether to sort the measure in ascending or descending order. It is also important to check the Keep children under parent box. Click OK twice to save changes.
You will see the grid has refreshed and sorted in ascending order of value of sales of the category as well as value of sales of the sub-category. Allround sales are the lowest, followed by Road and then Mountain with highest. Within each of these categories, the individual model sales are ranked in ascending order showing that, for example, within the Allround category, Scenario W7000 has the lowest sales, whilst Scenario W6000 has the highest sales.
Excluding members from a report
Members can be excluded by creating a second member list and then choosing to subtract the second list from the first:
Here we have a simple grid which shows lowest level descendants of Promotions on rows and Geography on columns. We would like to rank this report, and also exclude the Promotion No Discount which is not really adding any value to the report.
- Edit the Promotions hierarchy and click the Advanced tab. Select the Add Member List icon: . You will see a window as below:
- Click the drop-down on the right-hand side member list and select Edit. This will allow us to edit the member set:
- We are going to exclude No Discount, so select it and drag it across
- We need to choose one of the following operations to perform on our two member lists:
- Add: left and right sides combined
- Common: must exist on left and right side
- Subtract: left side minus right side
We will select the subtract operator in order to exclude the No Discount member
- We will also rank these Promotions based on the current measure, Reseller Sales Amount:
The Promotions hierarchy has now been edited to exclude No Discount and then ranked.
The report now excludes the No Discount row and has been ranked to show the top 10 Promotions across All Geographies.
From Version 8.1 you can add a Non-Empty to a set of members. This allows you to reduce the set of members in a result set, potentially increasing performance.
You can select the measure to be evaluated which must have a value for the member to be returned, and optionally also select a slice too.
Driving Reports from Excel
As well as the normal ability to drive member selections from Excel ranges, you can also drive other parameters.
- Ranking - Count
- Sets the number of items to be returned. (Any number)
- Ranking - Type (v7.5 onwards)
- Set whether to return the top or bottom n elements. (Valid values are top or bottom)
- Filter - Operator
- How to compare the measure when filtering. (Valid values are >, >=, <, <=, =, <>)
- Filter - Measure
- The measure to use when filtering. (Any valid measure)
- Sorting - Direction
- Allows you to reverse the order as required. (Valid values are asc, ascending, desc, descending)