Ranking, Sorting and Filtering
XLCubed provides two modes of Ranking: Hierarchy Ranking and Axis Ranking.
Hierarchy mode is accessed through the Advanced tab on the member selector for the relevant hierarchy.
The following tutorial shows how to use Hierarchy Ranking to filter, sort and rank using different members of a Cube. It uses the Internet Sales cube.
Axis Mode is a different approach to Ranking, Filtering and Sorting, and can be thought of as result set ranking. For further information on Axis Ranking go to: Axis Ranking
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.
- Start by clicking the Grid ribbon item (or the XLCubed > Design Grid menu item in Excel 2003 and below), and selecting the Internet Sales cube file
- Drag Calendar Period to Columns and Product to Rows. You can also drag any other hierarchies to Headers. In the example image below, Measures and Customer have been added there.
- Click on the Product hierarchy so that its details appear in the bottom-right panel.
- Drag the Product Key level over to the right of the dialog. You can switch between the members view and levels view by clicking on the Show Levels icon
- Click the Advanced tab to show the advanced selection pane:
- Click the Members dropdown and 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 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
- If it's not already visible, select 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
(As of version 7.2 you can set the sort direction to a range. In this case desc or descending will set the sort to descending, otherwise it will be ascending.
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
Let's sort the slicer elements so that they appear sorted in ascending order
- Right-click on slicer and select Edit.
- Select Advanced tab and then choose to Sort Result.
- Click the drop-down by Sort Asc and Edit Sort.
- Select Sort Ascending(A-Z) and then OK.
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.
- This is achieved by editing the member selector and making sure all members to be reported are included.
- Click on Advanced and select Sort (4th button from left on window) or click drop-down on Member button and select Sort Result.
- In the following window you need to choose whether you are sorting 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
- It is very simple to exclude members from your XLCubed reports. 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 set up the exclusion of the No Discount Promotion.
- Click the Advanced tab and then the Add Member List icon:
- You will see a window as below:
- Now 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.
- Next 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 and click OK. We will also click this icon to rank the result:
- Let's 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.
- Our report now looks like this:
As you can see the report now excludes 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)