Thursday, August 3, 2006

Top 10 Plus Other Query


On Teo Lachev's forum at Prologika, there was a question from gudjonv about how to do a RS chart that showed the top 10 members plus an "all other" member (thus, 11 slices of the pie). [See http://prologika.com/CS/forums/1465/ShowThread.aspx]. I didn't feel like registering so I could post a response, so I'll put the answer (converted to Adventure Works) here.


This is the general method I've used to do "Top 10 Plus Other" since OLAP 7.0. I can't think of any compelling functionality in 2005 that would do the job better. (Of course, in 2005, the Aggregate function will work even with a distinct count measure, so that makes this solution more functional, but it doesn't change the solution.)


WITH
  SET [TCat] AS TopCount([Product].[Subcategory].[Subcategory],10,[Measures].[Sales Amount])
  MEMBER [Product].[Subcategory].[Other] AS
Aggregate([Product].[Subcategory].[Subcategory] - TCat)
 SELECT { [Measures].[Sales Amount] } ON COLUMNS,
 TCat + [Other] ON ROWS
FROM [Adventure Works]
;


- Reed Jacobson


No comments: