Difference between revisions of "Ranking, Sorting and Filtering"

(Created page with '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 Filter…')
 
(Added ranking step, and edited the rest - more members make a more illuminating demo)
Line 2: Line 2:
  
 
==Step 1: Simple Filtering==
 
==Step 1: Simple Filtering==
Let's say we want to find the products at {{Code|Product Subcategory level that sold more than 50 units in {{Code|2003, Quarter 1}} and show the sales figures for those subcategories during {{Code|2003}} and its quarters.
+
Let's say we want to find the products at {{Code|Product Key}} level that sold more than 25 units in {{Code|2003, Quarter 1}} and show the sales figures for those subcategories during {{Code|2003}} and its quarters.
 
# Start by clicking the {{Menu|Grid}} ribbon item (or the {{Menu|XLCubed|Design Grid}} menu item in Excel 2003 and below), and selecting the Internet Sales cube file
 
# Start by clicking the {{Menu|Grid}} ribbon item (or the {{Menu|XLCubed|Design Grid}} menu item in Excel 2003 and below), and selecting the Internet Sales cube file
 
# Drag {{Code|Calendar Period}} to Columns and {{Code|Product}} to Rows. You can also drag any other hierarchies to Headers. In the example image below, {{Code|Measures}} and {{Code|Customer}} have been added there. [[File:RankSortFilterDemo1.png|center]]
 
# Drag {{Code|Calendar Period}} to Columns and {{Code|Product}} to Rows. You can also drag any other hierarchies to Headers. In the example image below, {{Code|Measures}} and {{Code|Customer}} have been added there. [[File:RankSortFilterDemo1.png|center]]
 
# Click on the {{Code|Product}} hierarchy so that its details appear in the bottom-right panel.
 
# Click on the {{Code|Product}} hierarchy so that its details appear in the bottom-right panel.
# Drag the {{Code|Product Subcategory}} 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 ([[File:LevelsMembersIcon.png]]).
+
# Drag the {{Code|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 ([[File:LevelsMembersIcon.png]]).
 
# Click the '''Advanced''' tab to show the advanced selection pane: [[File:RankSortFilterDemo2.png|center]]
 
# Click the '''Advanced''' tab to show the advanced selection pane: [[File:RankSortFilterDemo2.png|center]]
 
# Click the '''Members''' dropdown and choose '''Filter result''': [[File:RankSortFilterDemo3.png|center]]
 
# Click the '''Members''' dropdown and choose '''Filter result''': [[File:RankSortFilterDemo3.png|center]]
 
# Click the {{Code|Calendar Period}} edit control in the grid to change its selection to the desired member ({{Code|2003, Quarter 1}}): [[File:RankSortFilterDemo4.png|center]]
 
# Click the {{Code|Calendar Period}} edit control in the grid to change its selection to the desired member ({{Code|2003, Quarter 1}}): [[File:RankSortFilterDemo4.png|center]]
 
# Select the '''This measure''' radio button, and select {{Code|Order Quantity}} as the desired measure.
 
# Select the '''This measure''' radio button, and select {{Code|Order Quantity}} as the desired measure.
# Change the '''Operation''' to '''>''', and type {{Code|50}} in the edit field on the right: [[File:RankSortFilterDemo5.png|center]]
+
# Change the '''Operation''' to '''>''', and type {{Code|25}} in the edit field on the right: [[File:RankSortFilterDemo5.png|center]]
 
# Click '''OK'''. The new filter is displayed in the advanced selections tab: [[File:RankSortFilterDemo6.png|center]]
 
# Click '''OK'''. The new filter is displayed in the advanced selections tab: [[File:RankSortFilterDemo6.png|center]]
# Click '''OK''' again to run the Report - the Grid shows the two members that fit our criteria: {{Code|1. Mountain Bikes}} and {{Code|2. Road Bikes}}: [[File:RankSortFilterDemo7.png|center]]
+
# Click '''OK''' again to run the Report – the Grid shows the members that fit our criteria:[[File:RankSortFilterDemo7.png|center]]
  
 
==Step 2: Sorting on a different dimension==
 
==Step 2: Sorting on a different dimension==
Line 24: Line 24:
 
# Click '''OK'''. The new sort is displayed in the advanced selections tab
 
# Click '''OK'''. The new sort is displayed in the advanced selections tab
 
# Click '''OK''' again to run the Report
 
# 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 {{Code|Product Key}} level that have sold more than 25 units in {{Code|Q1}}:
 +
# Display the {{Code|Product}} [[Hierarchy Editor]] dialog
 +
# Click the '''Rank result''' icon ([[File:RankResultIcon.png]]) on the advanced selections tab to display the '''Edit Ranking''' dialog
 +
# Select the '''Bottom''' radio button, and type {{Code|8}} into the edit field
 +
# Select {{Code|2003, Quarter 1}} for the {{Code|Calendar Period}} hierarchy in the grid below: [[File:RankSortFilterDemo9.png|center]]
 +
# Run the Grid: only the lowest 8 members are returned

Revision as of 14:53, 4 October 2010

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.

  1. 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
  2. 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.
    RankSortFilterDemo1.png
  3. Click on the Product hierarchy so that its details appear in the bottom-right panel.
  4. 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 (LevelsMembersIcon.png).
  5. Click the Advanced tab to show the advanced selection pane:
    RankSortFilterDemo2.png
  6. Click the Members dropdown and choose Filter result:
    RankSortFilterDemo3.png
  7. Click the Calendar Period edit control in the grid to change its selection to the desired member (2003, Quarter 1):
    RankSortFilterDemo4.png
  8. Select the This measure radio button, and select Order Quantity as the desired measure.
  9. Change the Operation to >, and type 25 in the edit field on the right:
    RankSortFilterDemo5.png
  10. Click OK. The new filter is displayed in the advanced selections tab:
    RankSortFilterDemo6.png
  11. Click OK again to run the Report – the Grid shows the members that fit our criteria:
    RankSortFilterDemo7.png

Step 2: 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. If it's not already visible, select the Advanced tab
  3. Click the Sort result toolbar button (SortResultIcon.png)
  4. Change the Calendar Period selection to 2003, Quarter 1:
    RankSortFilterDemo8.png
  5. Click the Sort Descending (9-1) radio button
  6. Click OK. The new sort is displayed in the advanced selections tab
  7. 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:

  1. Display the Product Hierarchy Editor dialog
  2. Click the Rank result icon (RankResultIcon.png) 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:
    RankSortFilterDemo9.png
  5. Run the Grid: only the lowest 8 members are returned