Wednesday, May 4, 2011

MDX for Top n by group

Here is a little MDX that will get the top n list for each of another set of members.

For example, for Each region, I might want to get a list of the top images in that region.

The query is very simple, just a matter of employing the generate function.

select [Measures].[Hits] on 0,
non empty
generate([Client Host].[Client Geography].[Region],
([Client Host].[Client Geography],
topcount(order([Target].[Resource Hierarchy].[Resource Type].&[.jpg].children,[Measures].[Hits],bdesc),10)))
on 1
from EasternMining

You can plug in the grouping, ordering that you want. Even nested top n lists.

No comments: