Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

MDX Aggregate Named Set Than Others

In this quick post I wanted to provide some code that's been useful to me recently.  This MDX statement creates a named set of the Top 20 products and then aggregates on a selected measure.  Commonly though you may want to return an aggregation on those Top 20 products but optionally have an Others member that displays the aggregated sales for all other products not included in the set. 

My explanation might be more confusing than my code so here it is!  You can run it against the Adventure Works cube to simulate the results.

With 
Set [Top20Products] AS 
(TOPCOUNT({ ORDER( HIERARCHIZE( {[Product].[Product].[All].Children}), ([Measures].[Internet Sales Amount]), BDESC ) }, 20))  
   
MEMBER [Product].[Product].[Other Products] AS  
(AGGREGATE({EXCEPT([Product].[Product].Members, [Product].[Product].[Top20Products])}))  

Select [Measures].[Internet Sales Amount] on 0,  
{[Top20Products],[Product].[Product].[Other Products]}on 1  
From [Adventure Works]

 

 

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.