Null values for a calculated measure (type string)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Analysis Services (2000)

Null values for a calculated measure (type string)

Author

Topic

TimChenAllen
Starting Member

45 Posts

Posted - 2003-12-01 : 13:45:05

I have a calculated measure that is based on a dimension property. The property is a string. My problem is that I can't make the calculated measure go to NULL at the top level, and even if the property is blank, the value shows up.

The MDX for the Calculated Measure is:


IIF([Invoice].CurrentMember.Level.Name="(All)",
"",
[Invoice].CurrentMember.Properties("lddesc")
)

The first bit is just to handle the the measure is being viewed from the top level of the hierarchy "Invoice". Incidentally, I have to use the "", I can't use NULL (which is what I wanted in the first place).

The trouble here is that there are about 10000 members in the second level of the Invoice hierarchy, and most of them have a NULL for the description property. But all 10000 members show up in both Excel and Analysis services. Can anyone help me here?

Thanks in advance.

--
TimothyAllen
[url]http://www.timallen.org[/url]

TimChenAllen
Starting Member

45 Posts

Posted - 2003-12-01 : 14:20:25

Another try at this: I tried this calculated measure:


IIF([Invoice].CurrentMember.Level.Name="(All)",
  "",
  IIF([Invoice].CurrentMember.Properties("lddesc") = "",
    CStr(NULL),
    [Invoice].CurrentMember.Properties("lddesc")
  )
)

But every single value comes back as "#ERR". Dismay.

By the way, I can't just put "NULL" in place of "CStr(NULL)". I get back this error:


Unable to update the calculated member.
Formula error - syntax error - token is not valid: "    ^NULL^,
[Invoice].CurrentMember.Properties("lddesc")
)
)

It's like MSAS just blows up from the point where I use NULL on.

--
TimothyAllen
[url]http://www.timallen.org[/url]

TimChenAllen
Starting Member

45 Posts

Posted - 2003-12-01 : 14:33:02

Frustration city. I read an article that said the solution to this was to create a measure with a value of NULL and return that as the empty value. No such luck. MSAS is too smart for it and acts like my NULL measure is just another way of saying "NULL". I get the invalid token message. Help!

--
TimothyAllen
[url]http://www.timallen.org[/url]

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-01 : 18:22:46

Can you change NULL to some known value and filter that known value?

TimChenAllen
Starting Member

45 Posts

Posted - 2003-12-02 : 04:11:21

quote:
Originally posted by ehorn

Can you change NULL to some known value and filter that known value?

I have to do this within the actual calculated member, as Excel is our client and as far as I know there is no real filtering capability in Excel (well, if you call selecting dimension values one at a time filtering...).

Is there a way to create the filter within the Calculated Measure?

--
TimothyAllen
[url]http://www.timallen.org[/url]

TimChenAllen
Starting Member

45 Posts

Posted - 2003-12-02 : 05:01:42

NOTE: FOODMART EXAMPLE

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.

--
TimothyAllen
[url]http://www.timallen.org[/url]

TimChenAllen
Starting Member

45 Posts

Posted - 2003-12-03 : 03:49:02

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).

--
TimothyAllen
[url]http://www.timallen.org[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources