WITH
SET [CountryRankSet] AS
Order
(
NonEmpty
(
[Geography].[Country].[Country].MEMBERS
,[Measures].[Internet Sales Amount]
)
,[Measures].[Internet Sales Amount]
,desc
)
MEMBER [Measures].[CountryRank] AS
Rank
(
[Geography].[Country].CurrentMember
,CountryRankSet
)
SET [ProductRankSet] AS
Order
(
NonEmpty
(
[Product].[Product].[Product].MEMBERS
,[Measures].[Internet Sales Amount]
)
,[Measures].[Internet Sales Amount]
,desc
)
MEMBER [Measures].[ProductRank] AS
Rank
(
[Product].[Product].CurrentMember
,ProductRankSet
)
SELECT
{[Measures].[CountryRank],[Measures].[ProductRank],[Measures].[Internet Sales Amount]} ON 0
,Generate
(
[Geography].[Country].[Country].MEMBERS
,TopCount
(
Order
(
[Geography].[Country].CurrentMember
*
[Product].[Product].[Product].MEMBERS
,[Measures].[Internet Sales Amount]
,DESC
)
,3
)
) ON 1
FROM [Adventure Works];
CountryRank ProductRank Internet Sales Amount
Australia Road-150 Red, 48 1 1 $1,205,876.99
Australia Road-150 Red, 62 1 2 $1,202,298.72
Australia Road-150 Red, 52 1 3 $1,080,637.54
Canada Road-150 Red, 48 2 1 $1,205,876.99
Canada Road-150 Red, 62 2 2 $1,202,298.72
Canada Road-150 Red, 52 2 3 $1,080,637.54
France Road-150 Red, 48 3 1 $1,205,876.99
France Road-150 Red, 62 3 2 $1,202,298.72
France Road-150 Red, 52 3 3 $1,080,637.54
Germany Road-150 Red, 48 4 1 $1,205,876.99
Germany Road-150 Red, 62 4 2 $1,202,298.72
Germany Road-150 Red, 52 4 3 $1,080,637.54
United Kingdom Road-150 Red, 48 5 1 $1,205,876.99
United Kingdom Road-150 Red, 62 5 2 $1,202,298.72
United Kingdom Road-150 Red, 52 5 3 $1,080,637.54
United States Road-150 Red, 48 6 1 $1,205,876.99
United States Road-150 Red, 62 6 2 $1,202,298.72
United States Road-150 Red, 52 6 3 $1,080,637.54