Filter empty results in Pentaho Mondrian 3.2

Question

I am using Pentaho Mondrian 3.2.

I want to count the number of days in a specific range having [Measures].[CNT] > 0. The following MDX query counts all days including null and empty results:

WITH
MEMBER [Measures].[numofday]
    AS Count( Filter( [Date].[1390].[3].[10] : [Date].[1391].[3].[10]
                    , [Measures].[CNT].Currentmember > 0 ))

SELECT [Measures].[numofday] ON AXIS(0)
  FROM [Cube]

In addition, Non Empty function doesn't work in calculated member.

How can I modify this query to get the correct number of days?

gonsalu gonsalu · Accepted Answer · 2012-06-07T20:11:15

The Count function includes empty members by default; you'll have to the EXCLUDEEMPTY parameter in this case:

WITH
MEMBER [Measures].[numofday]
    AS Count( Filter( [Date].[1390].[3].[10] : [Date].[1391].[3].[10]
                    , [Measures].[CNT].Currentmember > 0 )
            , EXCLUDEEMPTY )

SELECT [Measures].[numofday] ON AXIS(0)
  FROM [Cube]

Filter empty results in Pentaho Mondrian 3.2

1 Answers