I am using analysis services 2000. I have a measure which returns the
member property of a dimension. What I want to do is only return this
property if another measure is <> 0. This I can do by using the IIF
funtion. My only problem is that when the IIF statement is false, I can
only get it to return a blank string, not a null value. Therefore when
I output the results using proclarity it doesn't remove the blank rows
because they are not null.

Is there a way around this?

Any help appreciated



*** Sent via Developersdex http://www.developersdex.com ***

Apart from upgrading to AS2005 I don't think there is anyway around
this.

If you were hand writing your MDX you could use the Filter() function to
get rid of the empty strings. But the IIF() function in AS2k is limited
to returning the same data type for both the true and false arguments.
It can either return one of two strings, or one of two numeric
expressions and only the numeric expressions can return null.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <ux3FNxplGHA.3740 (AT) TK2MSFTNGP02 (DOT) phx.gbl>, trekkie95
@hotmail.com says...

thanks for the help

*** Sent via Developersdex http://www.developersdex.com ***

but i saw a solutin once - think it was deepac

where u make another calc measure and use isempty and then set it to
null.....

cannot remember the syntax - hope it makes sence


"Darren Gosbell" <jam (AT) newsgroups (DOT) nospam> wrote

Michael, you're right in your recollection - though the original
solution was posted perhaps by George Spofford:

http://groups.google.com/group/micro...olap/msg/cec78
b442eb1c21e

From: Deepak Puri
Date: Wed, Apr 20 2005 5:42 pm
Groups: microsoft.public.sqlserver.olap

There is a well-known issue when combining a string with NULL in iif(),
which you may be encountering. Using a second measure to "hide" the text
may work, like:

[Measures].[Text1] :

Right([Measures].[Codigo Data Venc Titulo],2) + "/" +
Mid([Measures].[Codigo Data Venc Titulo], 5, 2) + "/" +
Left([Measures].[Codigo Data Venc Titulo],4)


[Measures].[Text2] :

iif(IsEmpty([Measures].[Codigo Data Venc Titulo]),
NULL, [Measures].[Text1])
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Cool, I had not seen that one - sorry about that Andrew.

It looks like it "tricks" AS2k by passing it a null and an expression
rather than a null and a string.

For those of us lucky enough to have upgraded to AS2k5 this is no longer
an issue as the iif() function has been enhanced to not require that the
two return arguements be of the same type.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <uTg256wlGHA.4268 (AT) TK2MSFTNGP05 (DOT) phx.gbl>,
deepak_puri (AT) progressive (DOT) com says...