I'm using MDX and Reporting Services to bring back data from a cube. I
want to suppress any data where Measure.B is null or 0, but nothing I
try works. For example, the MDX below returns many rows where
Measure.B=0:

SELECT NON EMPTY filter(nonemptycrossjoin({[Period].[200407]},
{Measures.B}), measures.b>0) ON COLUMNS, NON EMPTY
{[PS_OF_RK_TK].AllMembers} ON ROWS FROM HoursReport

Can anyone help? PS_OF_RK_TK is a dimension with four levels, Section,
Office, Rank, and Timekeeper.

The actual MDX has many measures (this is a simplified version), so
I'm hoping there's a way to suppress the whole row if all
measures=null or 0, without having to operate on each measure, though
even that would be a start.

Thanks,

Burt

Jamie,

Thanks much for the help. You're right- I did need filter on the
[PS_OF_RK_TK] dimension. But I did some testing and unfortunately
there is an additional issue.

Measure B is a calculated member. If I set its expression as:

IIF(Measures.Apbw<20,(Measures.Apbw),0)

in my cube, my filter function works. But if I simply change the <>
operator to:

IIF(Measures.Apbw>20,(Measures.Apbw),0)

my MDX totally ignores the filter function and returns all rows. I can
think of no reason the <> operator would break my filter. The server
also returns an error the first time I execute the MDX after
processing the cube with the > sign, but then works (but no filter) on
subsequent tries. Maybe this will all work in Yukon...

Burt







"Jamie" <Jamie (AT) discussions (DOT) microsoft.com> wrote

The reason is returns everthing is because the logic behind what I posted is:
Return the member IF:
(1) It is not zero OR
(2) It is not NULL

Anyting forced to zero will get caught by (2). Everything else will get caught by (1)

Regards
Jamie


"Burt" wrote: