Advanced Topics in MDX - Professional SQL Server Analysis Services 2005 with MDX - page 308

TOPCOUNT ([Product].[Product
Categories].[SubCategory].Members,
10, Measures.[Internet Sales Amount]) ON ROWS
from [Adventure Works]
Example 3
If you want to drill down further on the top 10 product categories within the
United States, you can use the following query. Based on this information you
can improve sales on products that are doing well or try to boost sales on the
remaining products after some market research on why they are not doing
well.
SELECT Measures.[Internet Sales Amount] on COLUMNS,
TOPCOUNT ([Product].[Product
Categories].[SubCategory].Members,
10, Measures.[Internet Sales Amount]) ON ROWS
from [Adventure Works]
WHERE ([Customer].[Customer
Geography].[Country].&[United States])
Example 4
If you want to see growth figures for the top 5 products in the last 4 quarters,
you can use the following MDX query. This query provides you with the trend
information that allows you to see if the top 5 products have been consistently
increasing in sales. If you see that some products do not show a positive
trend, you do need to drill down further for details and take appropriate action.
WITH SET [Top5Products] as 'TopCount ( [Product].[Product
Categories].[SubCategory].Members,
5,Measures.[Internet Sales Amount] )'
SET [CurrentQuarter] as 'Tail (Filter
([Date].[Fiscal].[Fiscal Quarter].Members,
Not IsEmpty
([Date].[Fiscal].CurrentMember)),1)'
SET [Previous4Quarters] as ' [CurrentQuarter].item
(0).item (0).Lag (4) :
[CurrentQuarter].item (0).item (0).Lag (1) '