Isolating the worst N members in a set

In the previous recipe we've shown how to identify members with the highest result. In this recipe we'll do the opposite and return those with the lowest result.

Getting ready

Start SQL Server Management Studio and connect to your SSAS 2012 instance. Click on the New Query button and check that the target database is Adventure Works DW 2012.

In this example we're going to use the Reseller dimension. Here's the query we'll start from:

WITH
SET [Ordered Resellers] AS
   Order( [Reseller].[Reseller].[Reseller].MEMBERS,
          [Measures].[Reseller Sales Amount],
   BASC )
SELECT
   { [Measures].[Reseller Sales Amount] } ON 0,
   { [Ordered Resellers] } ON 1
FROM
   [Adventure Works]

Once executed, that query returns resellers' sales values for every individual reseller, where the resellers themselves are sorted in the ascending order of the sales amount. Our task is to extract only the five with the worst sales amount.

How to do it...

We are going to use the...