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.

Video demonstration

Click here for video demonstrations on this topic.

Quick Ranking, Sorting and Filtering (Axis Mode)

Axis Mode is a quick approach to ranking, sorting and filtering, and can be thought of as result set ranking. Here the ranking is applied to an entire axis (i.e. a column or row).

To apply axis ranking,

1. Right-click on the column/row header that you wish to rank and select XLCubed> Apply> Ranking> .... (or alternatively, under the Apply menu in the Grid ribbon).
2. Select your desired ranking. Top 10 and Bottom 10 appear as options, but 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.
3. The ranking is immediately applied. 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 ranking is fixed to the axis, so if, for example, you apply a top 5 ranking while Geography then switch Geography with Product, you will get the top 5 Products without having to reapply the ranking. Where you have two hierarchies on the axis, the result will be the top 5 intersections between the two hierarchies (see the Nested Ranking video for a demonstration of this).

The same method is applied to sort and filter.

For further information, see Axis Ranking.

Advanced Ranking, Sorting and Filtering (Hierarchy Mode)

Ranking, sorting and filtering can be applied directly to hierarchy members in the member selector. In this model, the ranking is directly affecting which members are returned and is contained within the hierarchy itself, so that if you move the hierarchy from rows to columns, the ranking is then applied on columns.

Filtering

You can filter your hierarchies so your report only displays members that satisfy a certain condition.

1. 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.
2. Select which measure to filter by,
   • Current measure: use the measure currently used in the grid filters
   • This measure: manually define a measure to use
   • Measure in slice: filter by a certain slice, e.g. Sales amount in a particular period.
3. Select what filtering to apply (greater than, equal to etc.) - this can come from an Excel range (jump to Driving Reports from Excel)
4. Select the value to compare with,
   • This value: specify a value, or use a value in an excel cell
   • Current measure: use the measure currently used in the grid filters
   • This measure: manually define a measure to use
   • This selection: filter by a the value of a certain slice
   • Is null: use this option to remove members where the specified measure value is null

• 
• 

Example

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.

1. Create a grid with Calendar Period on columns and Product on rows.
2. 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.
3. Click the Advanced tab and, on the Members dropdown, choose Filter result.
4. Click the Calendar Period edit control in the grid to change its selection to the desired member (2003, Quarter 1):
5. Select the This measure radio button, and select Order Quantity as the desired measure.
6. Change the Operation to >, and type 25 in the edit field on the right:

7. Click OK. The new filter is displayed in the advanced selections tab.
8. Click OK again to run the Report – the Grid shows the members that fit our criteria:

Sorting

Sorting members can be accessed in the same way.

1. Select which sorting to apply,
   • Current measure: use the measure currently used in the grid filters
   • This measure: manually define a measure to use
   • Member name: sort alphabetically by member name
   • Member key: sort alphabetically by member key
   • Hierarchize: sort members into hierarchy order
   • Reverse: reverse the default member order
   • Use measure in 'Additional members': sort by a particular measure slice
   • Member property: sort by a property defined in the cube.
2. Select 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.

1. Display the Hierarchy Editor for the Product hierarchy by double-clicking on the Product label in the Grid
2. Under the Advanced tab, click the Sort result toolbar button ()
3. Change the Calendar Period selection to 2003, Quarter 1:
   
4. Click the Sort Descending (9-1) radio button
5. Click OK. The new sort is displayed in the advanced selections tab
6. Click OK again to run the Report

Ranking

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).

1. Select whether to return the top or bottom members (this can be determined by an Excel range)
2. Select how many members to return. This can be based on:
   • Count: a defined number of members e.g. top 10 members
   • Percent: the members that make up a percentage of the total e.g. customers that contribute the top 10% of sales (see Customer Segmentation video for a demonstration of this
   • Sum: return the members until their cumulative sum reached the defined value
3. Select which measure to rank by,
   • Current measure: use the measure currently used in the grid filters
   • This measure: manually define a measure to use
   • Measure in slice: rank by a certain slice, e.g. Sales amount in a particular period.
4. Select whether to Exclude zero values and Exclude nulls in the ranking

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:

1. Display the Product Hierarchy Editor dialog
2. Click the Rank result icon () on the advanced selections tab to display the Edit Ranking dialog
3. Select the Bottom radio button, and type 8 into the edit field
4. Select 2003, Quarter 1 for the Calendar Period hierarchy in the grid below:
   
5. Run the Grid: only the lowest 8 members are returned

Sorting slicer elements

Controlling which members are displayed in a slicer can be done in exactly the same way as advanced ranking etc. in a grid.

For example, it is possible to sort the elements of a slicer showing Customers, so that they appear sorted in ascending alphabetical order

1. Right-click on slicer and select Edit.
2. Select Advanced tab and then choose to Sort Result.
   

3. On the drop down, sort by Member name
4. Select Sort Ascending(A-Z) and then OK.

The members in the slicer will now be displayed in alphabetical order.

Sorting by sub-category within category

Advanced ranking allows for members to be maintained in hierarchical order when sorting is applied, i.e. higher level members can be sorted with their children sorted beneath them.

For example, the grid report below shows three bicycle product groups - Allround, Mountain and Road - and each of their subgroups. We would like to sort the Product hierarchy members by the current measure so that the categories are sorted in ascending order with their subcategories underneath them in ascending order.

• 
• 

1. Go into the Product hierarchy member selector. Under the advanced tab, select the sort icon or drop-down option
2. Sort by the current measure in 2002 in ascending order.
3. Important: 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 quickly excluded from a grid using the Grid ribbon or right click menu - see Keep Members.

For more advanced member filtering, create a second member list in the advanced tab of the member selector and then subtract the second list from the first.

Example

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.

1. Edit the Promotions hierarchy and click the Advanced tab. Select the Add Member List icon to create an additional member list.
2. Click on the new member list, or on the drop-down to the right-hand side and select Edit. This will allow us to edit the member set:
   

3. Select the member (or level/group) to exclude in the same way you would normally make member selections. In this case we select just one member, No Discount.
4. 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

     

5. We will also rank these Promotions based on the current measure, Reseller Sales Amount, by selecting the 'Rank Result' icon in the toolbar

The Promotions hierarchy has now been edited to exclude No Discount and then ranked.

The grid now excludes the No Discount row and has been ranked to show the top 10 Promotions across All Geographies.

Non Empty

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.

Subsets

You can take a subset of any member list with the subset option. This allows you to take a top/bottom selection of the returned members, or the top members with an offset.

For example, you could sort the members by your chosen measure, then take a subset to return the top 10 starting after the third member.

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)

See Also

• Outlier Grouping
• Set Searching
• Keep Members