Filter out zero (0) values in MDX

Question

I am looking to filter out zero values (0) from my MDX retrieve. I am querying Oracle's Essbase using the MDX below. Could someone help me with my MDX? There are no examples online and from my experience, MDX syntax and functions between Oracle and other products aren't the same.

I tried using <SELECT NON EMPTY [Measures].[Value] ON COLUMNS,> in the first line, but got the below two errors:

ERROR - 1260046 - Unknown Member Measures.Value used in query.
ERROR - 1241101 - Unexpected Essbase error 1260046.

The following MDX is working, I just want to filter out values that are 0.

SELECT {[USD]} on columns,
NON EMPTY Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(
{[EBIT]},
{[TOTAL_COUNTRY_OF_SALE]}),
Filter(Descendants([USA], USA.Levels(0)),NOT USA.CurrentMember.Shared_Flag)),
{[FORECAST]}),
{[Q1_REPORTING_CYCLE]}),
{[Sample_Product]}),
{[2022]}),
{[JAN]})
on rows
FROM [APPLICATION].[CUBE]

score 0 · Answer 1 · answered Jun 22 '23 at 20:48

Can you try wrapping that massive cross join in a FILTER?

SELECT {[USD]} on columns,
NON EMPTY 
FILTER(
  Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(
  {[EBIT]},
  {[TOTAL_COUNTRY_OF_SALE]}),
  Filter(Descendants([USA], USA.Levels(0)),NOT USA.CurrentMember.Shared_Flag)),
  {[FORECAST]}),
  {[Q1_REPORTING_CYCLE]}),
  {[Sample_Product]}),
  {[2022]}),
  {[JAN]})
,[USD] <> 0
)
on rows
FROM [APPLICATION].[CUBE]

Filter out zero (0) values in MDX

1 Answers1