MDX – Top count with aggregate

A few times now I have had a request to build a reporting services report where I show the TOP N customers followed by the subtotal and then a total of ALL OTHERS. This query could be broken out into three seperate MDX queries, but I want to use only one tablix in SSRS. Since you cannot point mulitple data sets at a single tablix, I came up a query that would give you a single data set in the correct order.

SET [Top10Products] AS
[Product].[Product Name].[Product Name]
,[Measures].[Sales Amount]
,[Measures].[Sales Amount]
MEMBER [Product].[Product Name].[SumTop10Products] AS Aggregate([Top10Products])
MEMBER [Product].[Product Name].[All Other Products] AS Aggregate(Except([Product].[Product Name].[Product Name],[Top10Products]))

[Measures].[Sales Amount] ON COLUMNS,
[Top10Products] +
[Product].[Product Name].[SumTop10Products] +
[Product].[Product Name].[All Other Products] ON ROWS
FROM Sales
WHERE [Date].[Calendar YQMD].[Calendar Month].&[200903]

The set Top10Products is pretty self explanatory. The next custom member is an aggregate of my Top10Products set (my subtotal). The next custom member is All other products EXCEPT the Top10Products.

In the select statement, I am using the + sign to UNION the members together in the order I want. Some poeple may want to use UNION instead, but I like the + sign.

What you end up with is

*this query was written against the Contoso database

My BI favorites

Over the last 10 years I have collected quite a lot of articles specific to BI covering SSAS design, Reporting Services, SSIS, etc… Recently at SQL Saturday 101, I told the attendees in my class I would share them here. So, here you go.