I need to create a calculated measure of type string, based on a
dimension level property. But I can't find a way to avoid having
empty values of the calculated measure show up. Our client is Excel.

I've put together an example of this using FOODMART so others can see
the problem better. In the Sales cube, I create a calculated measure
called "Store Manager". Its formula is:

iif([Store].CurrentMember.Level.Name="Store Name",
iif([Store].CurrentMember.Properties("Store Manager")="",
"EMPTY",
[Store].CurrentMember.Properties("Store Manager")
),
"NOTTHISLEVEL"
)

This works, and in Excel, it displays "EMPTY" for
Store=USA.CA.Alameda.HQ, which is the only store that has an empty
string for the Store Manager's name.

But what I really want is for the calculated measure to return NULL so
the Alameda HQ store or any others without an assigned manager simply
don't show up. But if I change the formula like this:

iif([Store].CurrentMember.Level.Name="Store Name",
iif([Store].CurrentMember.Properties("Store Manager")="",
NULL,
[Store].CurrentMember.Properties("Store Manager")
),
NULL
)

I get the error message:

Unable to update the calculated member.
Formula error - syntax error - token is not valid: " ^)^,
NULL
)"

I have also tried creating a measure with the value NULL and making
reference to it in place of "NULL", but MSAS treats it just as if it
were the value NULL and gives me the same error.

Our client is Excel, so there is no possibility of filtering on known
values. As far as I know, this must happen in the calculated measure.

Help! This seemingly simple problem is turning into a showstopper on
our project. Thanks in advance.

The reason you are getting this error is because inside IIF everything has
to be either string or everything has to be number. (this limitation is
lifted in Yukon, BTW). In the meantime - here is the workaround for you:

with member measures.x as 'store.properties("Store Manager")'
cell calculation y for '({measures.x})' as 'iif(store.properties("Store
Manager")="", NULL, CalculationPassValue(measures.currentmember, -1,
RELATIVE))'
select {x} on 0, store.[store name].members on 1 from sales

Now, in Foodmart 2000 you will see that HQ will have NULL, and you can
filter it out with NON EMPTY.

--
==================================================
Mosha Pasumansky - http://www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================
"Timothy Chen Allen" <google (AT) timallen (DOT) org> wrote

google (AT) timallen (DOT) org (Timothy Chen Allen) wrote in message news:<f216760f.0312020205.724c4719 (AT) posting (DOT) google.com>...
SOLUTION: (kind of)

In the end, I added the description as a level to the hierarchy. This
works as long as the description is not very long (I don't know what
will happen if it is very long). Since there are blank descriptions,
both the key and name cells have to be written like this:

"dbo"."InvoiceDimension"."Description" + ' '

Otherwise you get an empty cell as the description level and you can't
drill down to levels below it.

Also you must set "Unique Names" to false, since two siblings might
have the same description.

This is ugly, but it is a solution for my particular problem.

Apparently the following can't be used as a calculated measure in
MSAS:

iif(some_string_value = "",
NULL,
some_string_value
)

I consider this a bug. Can someone explain to me why it must be this
way (I know, you can't do analysis on strings, but my clients always
want this).

Please see my post with the explanation why it is not a bug and the
workaround.

--
==================================================
Mosha Pasumansky - http://www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================