CODEWITHSUNDEEP
nullcasemdx
Phil
Phil

Reputation: 41

MDX - With or without empty rows depending on parameter

I'm using an MDX as a basis for SSRS-Reports. I'm now trying to create a parameter to define, whether the NULL-Entry rows should be shown in the report or not. Is there a possibility to write an MDX in the sort of:

select
CASE @WithNull
 WHEN 'false'   THEN 'NONEMPTY'
 WHEN 'true'    THEN ''
 END
{[Dimension1]} on 0,
{[Dimension2]
 * [Dimension3]} on 1
from [Cube]

Meaning, if the User sets the Parameter "WithNull" on "True", then the MDX will show the NULL-Entries, otherwise they will be left out.

I already tried to solve it in the Report, but it didn't work out either.

Thanx in advance for your kind help. Phil

Upvotes: 1

Views: 370

Answers (1)

FrankPl
FrankPl

Reputation: 13315

I do not think you can directly parameterize this part of the query, but as the axes are MDX sets, it should be possible to calculate these ( I did not try it):

select
CASE @WithNull
     WHEN 'false'   THEN NonEmpty( {[Dimension1]} )
     ELSE                          {[Dimension1]} 
END
on 0,
{[Dimension2] * [Dimension3]}
on 1
from [Cube]

I used the NonEmpty function here, which expects a set as parameter and returns a set. The NON EMPTY key words are only allowed as the very first part of an axis clause, I think, and not for arbitrary sets.

Upvotes: 1

Related Questions