MDX - TOP N PARENT WITH TOP N CHILDS

来源：互联网发布：淘宝子账号不能登录编辑：程序博客网时间：2024/06/05 20:02

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 1FROM [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