MDX Left Join or Non-TopCount Group Attribute

[mvee]

Looking for way to get properties of topcount records and leave non-topcount group properties null


Take this example which gets the top 10 cities by internet order count and then groups the other cities into one.

WITH

SET [TopCustomers] AS TOPCOUNT(nonempty([Customer].[Customer Geography].[City]),10,[Measures].[Internet Order Count])
MEMBER [Customer].[Customer Geography].[OtherCustomers] AS AGGREGATE({EXCEPT([Customer].[Customer Geography].Members, [TopCustomers])})

SELECT
{
[Measures].[Internet Order Count]
} ON 0,

(
{[TopCustomers],[Customer].[Customer Geography].[OtherCustomers]}
--,[Customer].[Country].[Country]
)
on 1

FROM [Adventure Works]

WHERE ( [Product].[Category].&[1] )


What I want is to get another field for top customers, but not get that field for the non-top.
So, uncomment the country above - it creates duplicates countries for the OtherCustomers group, but I'd rather just have null displayed for country.

Is this possible?

The real-life example is much more complicated and the crossjoin between 'cities and countries' takes way too long.

Suggestions?

Thanks, Megan

[mvee]

Thanks to Chris Webb for helping me with this!

The answer is:

WITH

SET [TopCustomers] AS TOPCOUNT(nonempty([Customer].[Customer Geography].[City]),10,[Measures].[Internet Order Count])
MEMBER [Customer].[Customer Geography].[OtherCustomers] AS AGGREGATE({EXCEPT([Customer].[Customer Geography].Members, [TopCustomers])})

SELECT
{
[Measures].[Internet Order Count]
} ON 0,

{
([TopCustomers], [Customer].[Country].[Country].members),
([Customer].[Customer Geography].[OtherCustomers], [Customer].[Country].[All Customers])
}

on 1

FROM [Adventure Works]

WHERE ( [Product].[Category].&[1] )


yay!

[Island1]

And thanks so much for sharing your success with the rest of us!

Bill