Hi all,
I have the following calculated member that should return either null or
Name (defined as varchar) :

1) iif(IsEmpty([Measures].[Total1]) ,null,
[Dimension1].currentmember.Properties("Name"))
-- syntax error
then I tried

2)iif(IsEmpty([Measures].[Total1]) ,null,
val([Dimension1].currentmember.Properties("Name")))
-- it returns null or 0 since Name is varchar
then I tried

3)iif(IsEmpty([Measures].[Total1]) ,"",
[Dimension1].currentmember.Properties("Name"))
-- it returns Empty string or Name but the Empty string is not = null, thus
the row can not be removed using 'Empty rows' option in OWC.

Thanks for your advice.

Based on your results, I would double-check the syntax for (1). The way you
have it here looks correct, and since (3) works and only differs by a simple
substitution, maybe you made a typo. If it still gives you problems, post
information about at which token the parser is raising an error.

"Truc H." <TrucH (AT) discussions (DOT) microsoft.com> wrote

Looks like the issue addressed in this earlier post:

http://groups.google.com/group/micro...olap/msg/c1f5b
a11441f9aab
From: Deepak Puri <deepak_p... (AT) progressive (DOT) com>
Date: Mon, 01 Aug 2005 17:19:48 -0700

Subject: Re: Text in measures

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 ***

And it worked like a charm !!

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote