SSAS MDX Query: How to Return Top N products by revenue


A really quick note about how to write a MDX query to get Top N products (think dimension!) by revenue (Think measure!). Using this query as a starting point, you should be able to write queries that meet your similar business requirements:

Here’s the query structure:

[code gator=”false” language=”language"sql"”]

{[Measures].[Revenue]} ON COLUMNS,
{TopCount([Product].[Product Name],20,[Measures].[Revenue])} ON ROWS


Note the use of Top Count & it’s query syntax. Also note that you can specify other number instead of 20. I hope this gives you a good starting point.

What do you think? Leave a comment below.