Ranking, Sorting and Filtering

Once we have returned cube members into a grid report we often need to exclude or change the order of the result set to provide more meaningful information. MDX (Multidimensional Expressions) language includes some very useful operators to provide filtering (FILTER), sorting (ORDER) and ranking (TOPCOUNT/BOTTOMCOUNT) of dimension members. These can be quite overwhelming even for power users of XLCubed.  So, in V6, we have introduced a new feature “Advanced Member Selections” to provide easy access to this powerful part of Microsoft Analysis Services.

Using this new functionality we can nest and combine these operations to answer complex business questions (for simpler operations you can right-click on a member in the grid and use the “Apply” menu to perform simple ranking, filters and sorting).

Filtering

So let’s go through a simple filtering example.  Say, for example, that 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.

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 ().
5. Click the Advanced tab to show the advanced selection pane:

6. Click the Members drop down and choose Filter result:

7. Click the Calendar Period edit control in the grid to change its selection to the desired member (2003, Quarter 1):

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:

10. Click OK. The new filter is displayed in the advanced selections tab:

11. Click OK again to run the Report – the Grid shows the members that fit our criteria:

So we can see the results, filtering by 2003 Q1, but displaying the values for All Time (or any other period we wish to use). We could have also used the Range selector:

  to drive the period selecting from an Excel Range and our grid would automatically refresh whenever the driving value changes.

Ranking

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 inQ1:

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:

We now have the filter, following by the ranking:

Run the Grid: only the lowest 8 members are returned

Sorting

Now let’s sort the report on a different dimension – for example, 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 ()
4. Change the Calendar Period selection to 2003, Quarter 1:

5. Click the Sort Descending (9-1) radio button
6. Click OK. The new sort is displayed in the advanced selections tab

Click OK again to run the Report

Joining Results

It’s also possible to join different results together: combining both sets (UNION), excluding members (EXCEPT) and returning common members (INTERSECT).

So we could also add the top 10 products  along side the bottom 8 products to the grid. Begin by adding another member selection using the “Add Member List” tool-bar button:

As before, we select the list of members to rank (in this case the Product Key level) and then select the operation we want to perform, a Top 10:

There are various options to decide how to combine the lists, we’ll stick with Add:

And we get both results combined:

So the “Advanced Member Selections” feature provides lots of the power of Analysis Services in a simplified way  – to try this feature for yourself you can begin by downloading XLCubed.

‍