MDX:当我使用TopCount时，所有级别的值都不正确

Question

我对以下查询有问题。我的要求是将为TOPCOUNT函数选择的所有值的总和作为ALL level值。谢谢。

WITH 
  SET [0] AS 
    {
      {
        [Sum Assured Bucket].[Sum Assured Bucket].[Sum Assured Bucket].[> 1200000]
       ,[Sum Assured Bucket].[Sum Assured Bucket].[Sum Assured Bucket].[> 1200000].Children
      }
    } 
  SET [1] AS 
    {
      {
        [Period].[Period].[Year].[2012]
       ,[Period].[Period].[Year].[2012].Children
      }
    } 
  SET [Agent_Agent_Agent_1_ADV] AS 
    {
      TopCount
      (
        Order
        (
          {AddCalculatedMembers([Agent].[Agent].[Agent])}
         ,[Measures].[Count Of Leads]
         ,BDESC
        )
       ,5
       ,[Measures].[Count Of Leads]
      )
    } 
  SET [Agent_Agent_Agent_1_ADV_VISULATOTAL] AS 
    VisualTotals([Agent_Agent_Agent_1_ADV]) 
  SET [HIDDEN_TOTAL_0] AS 
    VisualTotals([Agent_Agent_Agent_1]) 
  SET [Agent_Agent_Agent_1] AS 
    {
      {[Agent].[Agent].[All]}
     ,Order
      (
        {
          AddCalculatedMembers
          (
            Intersect
            (
              [Agent_Agent_Agent_1_ADV_VISULATOTAL]
             ,AddCalculatedMembers([Agent].[Agent].[Agent])
            )
          )
        }
       ,[Measures].[Count Of Leads]
       ,BDESC
      )
    } 
  SET [HIDDEN_TOTAL_1] AS 
    VisualTotals([Sum Assured Bucket_Sum Assured Bucket_Sum Assured Bucket_1]) 
  SET [HIDDEN_TOTAL_2] AS 
    VisualTotals([0]) 
  SET [Sum Assured Bucket_Sum Assured Bucket_Sum Assured Bucket_1] AS 
    {
      {[Sum Assured Bucket].[Sum Assured Bucket].[All]}
     ,Hierarchize
      (
        Intersect
        (
          [0]
         ,AddCalculatedMembers
          (
            [Sum Assured Bucket].[Sum Assured Bucket].[Sum Assured Bucket]
          )
        )
      )
    } 
SELECT 
  NON EMPTY 
    {
      {[Agent].[Agent].[All]}
     ,Order
      (
        {
          AddCalculatedMembers
          (
            Intersect
            (
              VisualTotals
              (
                {
                  TopCount
                  (
                    Order
                    (
                      {AddCalculatedMembers([Agent].[Agent].[Agent])}
                     ,[Measures].[Count Of Leads]
                     ,BDESC
                    )
                   ,5
                   ,[Measures].[Count Of Leads]
                  )
                }
              )
             ,AddCalculatedMembers([Agent].[Agent].[Agent])
            )
          )
        }
       ,[Measures].[Count Of Leads]
       ,BDESC
      )
    }
  DIMENSION PROPERTIES PARENT_UNIQUE_NAME  ON 0
 ,NON EMPTY 
    [Sum Assured Bucket_Sum Assured Bucket_Sum Assured Bucket_1]
  DIMENSION PROPERTIES PARENT_UNIQUE_NAME  ON 1
 ,{[MEASURES].[Count Of Leads]} ON 2
FROM [New Policy Analysis];

Answer 1

这是多维数据集中的特定成员：[Agent].[Agent].[All]，它将不等于使用TopCount的表达式的结果。

您可以创建对topCount集进行聚合或求和的计算成员：

...
SET [Agent_Agent_Agent_1_ADV] AS 
    {
      TopCount
      (
        Order
        (
          {AddCalculatedMembers([Agent].[Agent].[Agent])}
         ,[Measures].[Count Of Leads]
         ,BDESC
        )
       ,5
       ,[Measures].[Count Of Leads]
      )
    } 
MEMBER [Agent].[Agent].[All_new] AS  
   Aggregate([Agent_Agent_Agent_1_ADV]) //<<<<Sum([Agent_Agent_Agent_1_ADV])
...
...
SELECT 
  NON EMPTY 
    {
      {[Agent].[Agent].[All_new]}  //<<<<
     ,Order
      (
        {
...
...