MDX，删除行上的零行

Question

我的MDX查询如下所示：

WITH 
MEMBER [YEAR].[A2014_1-12] AS 
  ([YEAR].[2014], [TYPE].[ACTUAL]), 
MEMBER [YEAR].[P2014_1-12] AS 
  ([YEAR].[2014], [TYPE].[PLAN]),                           
MEMBER [YEAR].[P2015_1-12] AS 
  ([YEAR].[2015], [TYPE].[PLAN]),                          
MEMBER [YEAR].[A2015_YTD] AS 
  SUM(
    {[YEARPERIOD].[201501]:[YEARPERIOD].[201509]}
    ,(
     [Measures].currentmember
    ,[TYPE].[ACTUAL]
     )
   )                                           
SELECT 
{
[YEAR].[A2014_1-12],
[YEAR].[P2014_1-12],
[YEAR].[P2015_1-12],
[YEAR].[A2015_YTD]
}             
ON COLUMNS,
NON EMPTY 
 [COST_CENTER]
 *[COST_ELEMENT] ON ROWS 
FROM [CUBE] 
WHERE ([Measures].[COSTS]);

查询本身正在工作。然而，它也返回了很多零行，我想去掉这些零行。奇怪的是-如果我只在列轴上放了一个成员(例如YEAR.A2014_1-12)，它不会返回任何零行。但是，只要我开始向列轴添加另一个成员，它也会返回许多零行(似乎忽略了我的CROSSJOIN中的非空)。如何删除上述MDX-Query中的零行？

Answer 1

试着从这个开始

WITH 
MEMBER [YEAR].[A2014_1-12] AS 
  iif(
    ([YEAR].[2014], [TYPE].[ACTUAL]) = 0
    ,null
    ,([YEAR].[2014], [TYPE].[ACTUAL])
  )
MEMBER [YEAR].[P2014_1-12] AS 
  iif(
    ([YEAR].[2014], [TYPE].[PLAN]) = 0 
    ,null
    ,([YEAR].[2014], [TYPE].[PLAN])
  )                                                    
SELECT 
NON EMPTY
{
[YEAR].[A2014_1-12],
[YEAR].[P2014_1-12]
}             
ON COLUMNS,
NON EMPTY 
 [COST_CENTER]
 *[COST_ELEMENT] ON ROWS 
FROM [CUBE] 
WHERE ([Measures].[COSTS]);

另外，您不需要在此定义中定义度量层次结构的当前成员：

MEMBER [YEAR].[A2015_YTD] AS 
  SUM(
    {[YEARPERIOD].[201501]:[YEARPERIOD].[201509]}
    ,(
     [Measures].currentmember
    ,[TYPE].[ACTUAL]
     )
   ) 

这就足够了：

MEMBER [YEAR].[A2015_YTD] AS 
  SUM(
    {[YEARPERIOD].[201501]:[YEARPERIOD].[201509]}
    ,([TYPE].[ACTUAL])
   ) 

试着这样做：

WITH 
MEMBER [YEAR].[A2014_1-12] AS 
  iif(
    ([YEAR].[2014], [TYPE].[ACTUAL]) = 0
    ,null
    ,([YEAR].[2014], [TYPE].[ACTUAL])
  )
MEMBER [YEAR].[P2014_1-12] AS 
  iif(
    ([YEAR].[2014], [TYPE].[PLAN]) = 0 
    ,null
    ,([YEAR].[2014], [TYPE].[PLAN])
  ) 
SET [X] AS
 NonEmtpy(
  [COST_CENTER]*[COST_ELEMENT],
  {[YEAR].[A2014_1-12],[YEAR].[P2014_1-12]}
)                                                   
SELECT 
NON EMPTY
{
[YEAR].[A2014_1-12],
[YEAR].[P2014_1-12]
}             
ON 0,
//NON EMPTY 
 [X] ON 1
FROM [CUBE] 
WHERE ([Measures].[COSTS]);