MDX IIF Statement - IsEmpty()

building semantic layer
1

Hi,

I’m trying to check at the Universe level whether data coming through is null (or IsEmpty), however when I run my webi report it doesn’t parse.

I’ve tried the following formula:


<EXPRESSION>IIF(IsEmpty(@Select(Key Figures\Target No. of New Opps)), 0 , @Select(Key Figures\Target No. of New Opps))</EXPRESSION>

<EXPRESSION>IIF(IsEmpty([Measures].[DAIPLZ4C62X0FMFVZYZOZ2IME]),0,[Measures].[DAIPLZ4C62X0FMFVZYZOZ2IME])</expression>

Both fail with WIS 10901 -“Invalid MDX command with ,.”

Anyone got any ideas on this?

ally3009 :uk: (BOB member since 2010-08-10)

2

What OLAP data source are you reporting against?

Mak 1 :uk: (BOB member since 2005-01-06)

3

Hi Mak,

sorry for the late response, I’m over SAP BW 7 (sp 22).

ally3009 :uk: (BOB member since 2010-08-10)

4

Ally,

Are they actaully coming through empty? Usually, through BW they come through as # or Not assigned?

Cheers,

Mark.

Mak 1 :uk: (BOB member since 2005-01-06)

5

I believe the IFF MDX Syntax for SAP BW only allows 0 or 1 as return values.

Andreas :de: (BOB member since 2002-06-20)

6

Hi Ally,

Have you got this working for you.? i need the same level of syntax to use at the cost centre level. Could you let’s know if you solved this.?

looksmee :uk: (BOB member since 2006-02-08)