Suppress Zeros and Empty Rows and Columns by using MDX

Question

Hi, following is my MDX. Still getting zero rows and columns. how to remove zero rows and columns.

Select
Non Empty CrossJoin(
CrossJoin(
{{[Account].& [1]}, {[Account].& [2]},
{[Account].& [3]}}, {{[Measures].[YTD]},
{[Measures].[MTD]}, {[Measures].[QTD]}}
),
{{[Reporting].[Level 1].& [1]},
{[Reporting].[Level 1].& [2]}}
) Dimension Properties [Parent_Unique_Name] On Rows,
Non Empty {{{[Time].& [2019]},
{Descendants({[Time].& [2019]},, LEAVES) }},
{{[Time].& [2020]},
{Descendants({[Time].& [2020]},, LEAVES) }}} Dimension Properties [Parent_Unique_Name] On Columns
From
RepCube
Where
(
{[ICSegment].& [10],
[ICSegment].& [102]},
(
[Scenario].& [2], [BusinessCenter].& [3],
[Company].& [1], [CostCenter].& [5],
[Department].& [2], [Geography].& [8],
[ProductLine].& [2]
)
)

Please help me on this
Thanks,
Narendra

Answer 1

Hi,
Try this see if it work :
Select
Non Empty CrossJoin(
CrossJoin(
{{[Account].& [1]}, {[Account].& [2]},
{[Account].& [3]}}, {{IIF([Measures].[YTD]<>0,[Measures].[YTD],null)},
{IIF([Measures].[MTD]<>0,[Measures].[MTD],null)}, {IIF([Measures].[QTD]<>0,[Measures].[QTD],null)}}
),
{{[Reporting].[Level 1].& [1]},
{[Reporting].[Level 1].& [2]}}
) Dimension Properties [Parent_Unique_Name] On Rows,
Non Empty {{{[Time].& [2019]},
{Descendants({[Time].& [2019]},, LEAVES) }},
{{[Time].& [2020]},
{Descendants({[Time].& [2020]},, LEAVES) }}} Dimension Properties [Parent_Unique_Name] On Columns
From
RepCube
Where
(
{[ICSegment].& [10],
[ICSegment].& [102]},
(
[Scenario].& [2], [BusinessCenter].& [3],
[Company].& [1], [CostCenter].& [5],
[Department].& [2], [Geography].& [8],
[ProductLine].& [2]
)
)