Re: NON EMPTY in calculated measures?

#1

OK so the way to avoid having AS traverse empty cells in retrieving a value
is to combine the ISEMPTY function with an IF statement. I had thought that
there would be a way to prefix an entire calculated measure with a NON EMPTY
statement that would avoid traversing empty fields in the dimension.

But the answer that helped was from Deepak pointing out that Excel issues
the NON EMPTY statement. I can see from the MDX log how that works.
Thanks.

Jon

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

Quote:

Don't know if this is "deja vu all over again", but both of you
contributed to an earlier thread that seems relevant here. The "Non
Empty Behavior" Property of a calculated member can sometimes speed up
MDX queries that use the NON EMPTY construct (which Excel issues by
default):

http://groups.google.com/groups?hl=e...8&th=36fbc5523
981a225&rnum=8

From: Jon Pearce (jon (AT) remove_this (DOT) pearcefamily.org)
Subject: Using NON EMPTY in a calculated measure?

View this article only
Newsgroups: microsoft.public.sqlserver.olap
Date: 2003-09-18 18:04:46 PST

I have a calculated measure (C) that is the ratio of two
sparsely-populated
measures (A and B). For dimension members in which A and B don't exist,
I
still get the calculated measure C. This means that a lot of dimension
members show up that have no values for A and B whenever I add measure C
to
the pivot table.

How can I suppress C when there are no A and B measures? I tried a NON
EMPTY statement in the calculated measure but can't get the syntax
right.

Thanks.

Jon

jpearce (AT) remove_this_dgapartners (DOT) com
Message 2 in thread
From: Tom Chester (publicNOSPAM (AT) tomchester (DOT) net)
Subject: Re: Using NON EMPTY in a calculated measure?

View this article only
Newsgroups: microsoft.public.sqlserver.olap
Date: 2003-09-18 22:21:11 PST

Notice the calc members have a property "non empty behavoir". For calc C
set
this property to A or B.

tom @ the domain below
www.tomchester.net

"Jon Pearce" <jon (AT) remove_this (DOT) pearcefamily.org> wrote in message
news:eUzEtnkfDHA.3284 (AT) tk2msftngp13 (DOT) phx.gbl...
I have a calculated measure (C) that is the ratio of two
sparsely-populated
measures (A and B). For dimension members in which A and B don't
exist, I
still get the calculated measure C. This means that a lot of
dimension
members show up that have no values for A and B whenever I add measure
C to
the pivot table.

How can I suppress C when there are no A and B measures? I tried a
NON
EMPTY statement in the calculated measure but can't get the syntax
right.

Thanks.

Jon

jpearce (AT) remove_this_dgapartners (DOT) com

Message 3 in thread
From: Mosha Pasumansky [MS] (moshap (AT) microsoft (DOT) com)
Subject: Re: Using NON EMPTY in a calculated measure?

View this article only
Newsgroups: microsoft.public.sqlserver.olap
Date: 2003-09-19 19:03:34 PST

"Tom Chester" <publicNOSPAM (AT) tomchester (DOT) net> wrote in message
news:1nwab.112$OC.72322 (AT) news (DOT) uswest.net...
Notice the calc members have a property "non empty behavoir". For calc
C set
this property to A or B.

However, remember that "Non Empty Behavior" is just a performance hint.
It
cannot overwrite results of the calculation.
Therefore if you want that the result of A/B was NULL when both A and B
NULLs, you need to code it as

iif( IsEmpty(B) AND IsEmpty(A), NULL, A/B )

and then set Non Empty Behavior to A.

--
==================================================
Mosha Pasumansky - 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.
==================================================

Message 4 in thread
From: Jon Pearce (jpearce (AT) remove_this (DOT) dgapartners.com)
Subject: Re: Using NON EMPTY in a calculated measure?

View this article only
Newsgroups: microsoft.public.sqlserver.olap
Date: 2003-09-24 05:52:48 PST

Works great - thanks to both of you.
..

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: NON EMPTY in calculated measures?

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Re: NON EMPTY in calculated measures? - 12-23-2003 , 09:08 AM