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.

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

SELECT
[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

Advertisements

One thought on “MDX – Top count with aggregate

  1. This is what I am looking for… however I want to create a calculated member in SSAS Cube…is it possible. If yes how to go ahead with it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s