I have a cube with a calculated member The calculation for the calculated member "MyMembers" is a simple addition
[Measures].[NoOfOwners] + [Measures].[NoOfUsers

In the MDX select statment I use the "non empty" expression on both columns and rows. My MDX query goes like this

SELECT
non empty {[TMD_Products].[All TMD_Products].[Datatjenester].[Bedriftsnett]} on columns
non empty {[TMD_KIDs].[Kid].members} on row
from TMC_Products where ([Measures].[MyMembers]


The problem is that when I substitute "MyMembers" in the from statement with "NoOfOwners" or "NoOfUsers" I always get the same number of rows as with "MyMembers". The query seems to return correct values for the member but does not suppress non empty values. In stead the query returns the value 0

How can I set up my query to supress the the lines with value 0?
I run SQL Server 2000 Enterprise edition with Analysis Server on windows 2000 server, Service Pack 3a is installed for sql server and analysis server

Regard

Hello Tore,

I would appreciate your patience while I am looking into this issue. I will
post my response at soon as I have update for you. In the mean time, if you
have any other useful information, please feel free to let me know.

Thank you,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Hi Tore,

I learned you want to set up the query to suppress the lines with value 0.
When we use NON EMPTY keyword, it is important to note that this function
screens out empty tuples, not individual empty cells. Because of this,
empty cells can appear in a result dataset even when the NON EMPTY keyword
is used For example:

With member [Measures].[MYTESTCAL] as '[Measures].[Units
Shipped]+[Measures].[Units Ordered]'
Select
NON EMPTY{[Time].[1997],[Time].[1998] } on columns,
NON EMPTY [Store].[Store Name].members on rows
From Warehouse where ([Measures].[MYTESTCAL])


I tried the above MDX statements on FoodMart 2000 sample database using MDX
Sample application and use [Measures].[Units Shipped] or [Measures].[Units
Ordered] instead of [Measures].[MYTESTCAL]. It seems the query did not
return 0 instead of NuLL. I am afraid I cannot reproduce the problem you
described. If it is possible, please create a example on the FoodMart 2000
sample database so that I can reproduce it in house and perform further
research.

I also found the following articles for your reference.
244650 INF: Working with NULL Values in OLAP Services
http://support.microsoft.com/?id=244650

Working with Empty Cells
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/ag
mdxadvanced_8jcj.asp

I am looking forward to hearing from you soon.
Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Have you tried using the 'Filter' function?



[quoted text, click to view]
with "NoOfOwners" or "NoOfUsers" I always get the same number of rows as
with "MyMembers". The query seems to return correct values for the member
but does not suppress non empty values. In stead the query returns the value
0.
[quoted text, click to view]
2000 server, Service Pack 3a is installed for sql server and analysis
server.
[quoted text, click to view]