TopCount Union with Others

[Jon Webber]

I am trying to return the top 5 Cities and their sales and then the Sales
for all other cities in the same result set. How do I union the two sets of
results. I tried Union, CrossJoin, etc. Do I have to create a Cube to solve
this?

Here is my MDX query

WITH
SET [Top5Cites] AS
' TOPCOUNT( [Store].[Store City].Members , 5, [Measures].[Unit
Sales] )'

SET [AllCities] AS
' FILTER ( { [Store].[Store City].Members} ,
[Measures].[Unit Sales] > 0 )'

SET [NotTop5] AS
' EXCEPT( [AllCities] , [Top5Cites] ) '

MEMBER [Measures].[NotTop5_Sum] AS
' Sum ([NotTop5] , [Measures].[Unit Sales] )'

--/*
SELECT { [Measures].[Unit Sales] } on COLUMNS
, [Top5Cites] ON ROWS
FROM Sales
--*/

/*
SELECT { [Measures].[NotTop5_Sum] } on COLUMNS
FROM Sales
*/

I want the results to look something like

City Sales
Salem 41,580
Tacoma 35,257
PDX 26,079
LA 25,663
SD 25,635

OTHERS 112,559

[Tom Chester]

Try this:

WITH
SET Top5Cities AS

' TOPCOUNT( [Store].[Store City].Members , 5,
[Measures].[Unit Sales] )'

MEMBER Store.Other AS
' (Store.DefaultMember, [Unit Sales]) -
Sum([Top5Cities], [Unit Sales]) '

SELECT { [Measures].[Unit Sales] } on COLUMNS,

{ [Top5Cities], {Store.Other} } ON ROWS
FROM Sales

p.s. I wish everyone expressed their problems in foodmart terms.

tom @ the domain below
www.tomchester.net

"Jon Webber" <jwe...@noSpam.com> wrote in message
news:#kfBktkF...@TK2MSFTNGP10.phx.gbl...

[Jon Webber]

You are the best! Thanks, that is exactly what we wanted. I am fighting
with Percents now as [Top5Cities] has no .Parent

"Tom Chester" <public...@tomchester.net> wrote in message
news:3lRua.270$6I2....@news.uswest.net...

[Tom Chester]

Jon, members have parent, sets don't have parents. Is this what you mean...?

WITH
SET Top5Cities AS
' TOPCOUNT( [Store].[Store City].Members , 5,
[Measures].[Unit Sales] )'

MEMBER Store.Other AS
' (Store.DefaultMember, [Unit Sales]) -
Sum([Top5Cities], [Unit Sales]) '

MEMBER Measures.[%Total] AS
' (Store.CurrentMember, [Unit Sales]) /
(Store.DefaultMember, [Unit Sales]) ' ,
Format_String = '0.0%'

SELECT { [Measures].[Unit Sales], Measures.[%Total] } ON COLUMNS,

{ [Top5Cities], {Store.Other} } ON ROWS
FROM Sales

tom @ the domain below
www.tomchester.net

"Jon Webber" <jwe...@noSpam.com> wrote in message

news:OK8X2RoF...@TK2MSFTNGP12.phx.gbl...