MDX, Remove Zero-Rows On ROWS

Question

My MDX-Query looks like this:

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]);

The Query itself is working. However it is also returning a lot of zero-rows and I would like to get rid of these zero-rows. The strange thing is - If I put only one MEMBER (e.g. [YEAR].[A2014_1-12]) on the COLUMNS-axis it is not returning any zero-rows. But as soon as I start to add another MEMBER to the COLUMN-axis it is also many returning zero-rows (It seems like it is ignoring the NON EMPTY in my CROSSJOIN). How can I remove the zero-rows in the above MDX-Query?

I suspect they are not empty but are 0 - Non Empty and NonEmpty will only ignore empty cube space but if there is a zero in the cube space then these functions will not exclude the data — whytheq

whytheq whytheq · Accepted Answer · 2015-10-25T16:04:35

Try this as a start

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]);

As an aside you do not need to define the current member of the measures hierarchy in this definition:

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

This is enough:

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

Try this instead:

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]);

MDX, Remove Zero-Rows On ROWS

1 Answers