December 14, 2009 at 12:31 pm
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, upcomment the country above - it duplicates the countries for the OtherCustomers group, but I'd rather just have null displayed.
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
December 16, 2009 at 3:29 pm
Use All member and crossjoining sets.
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!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply