Ranking, Sorting and Filtering
The following tutorial shows how to filter, sort and rank using different members of a Cube. It uses the Internet Sales cube.
Step 1: Simple Filtering
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:
Step 2: 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
Step 3: 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