MDX - TOP N PARENT WITH TOP N CHILDS

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
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值