"Getting the top 5 sales by year and display the results by Quarter " using MDX

Question

I'm working with a sales cube.
I want to get the top 5 store by year and display their sales by Quarter later on in my report.

I was able to get the top 5 store but by quarter aswell not only year !

SELECT NON EMPTY { [Measures].[Revenue] } ON COLUMNS, 
NON EMPTY { ([DWH REF DATE].[H_CALENDER].[QUARTER NUM].ALLMEMBERS * TOPCOUNT([DWH REF STORE].[H_STORE].[STORE].ALLMEMBERS , 5  ,  [Measures].[Revenue] )) } 
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_VALUE, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( -{ [DWH REF STORE].[H_STORE].[TERRITORY].&[N/A].&[N/A] } ) ON COLUMNS FROM ( SELECT ( { [DWH REF DATE].[H_CALENDER].[YEAR NUM].&[2012] } ) ON COLUMNS FROM [SALES CUBE])) 

What I want exactly to be able to, is to get the top 5 store by year and after get their sales by quarter of the 5 stores.

Answer 1

Welcome to SO, so lets take a look at an example. I am trying to get the top 5 subproducts by internet sales from adventureworks cube for 2013

select 
[Measures].[Internet Sales Amount]
on columns,
non empty
topcount
(
[Product].[Subcategory].[Subcategory],
5,
[Measures].[Internet Sales Amount]
)
on rows
from 
[Adventure Works]
where [Date].[Calendar].[Calendar Year].&[2013]

Result

Now Lets try to divide there sales by quater

select 
[Measures].[Internet Sales Amount]
on columns,
non empty
[Date].[Calendar Quarter of Year].[Calendar Quarter of Year]
on rows
from 
(select  topcount([Product].[Subcategory].[Subcategory],5,[Measures].[Internet Sales Amount]) on 0 from [Adventure Works] )
where [Date].[Calendar].[Calendar Year].&[2013]

Result

Edit: based on comment

select 
[Measures].[Internet Sales Amount]
on columns,
non empty
(
topcount
(
[Product].[Subcategory].[Subcategory],
5,
[Measures].[Internet Sales Amount]
),[Date].[Calendar Quarter of Year].[Calendar Quarter of Year])
on rows
from 
[Adventure Works]
where [Date].[Calendar].[Calendar Year].&[2013]

Result