Non empty for calculated members

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 rows
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 valu
es. 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.
Regards
Tore G.
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/de...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?
"T. Gylver" <nospam@.tgylnospam.no> wrote in message
news:9BD94A01-B03F-4B75-9224-CF3C9CF471E6@.microsoft.com...
> 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 rows
> 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.
> Regards
> Tore G.
|||WITH
MEMBER [Measures].[M1] AS '10'
MEMBER [Measures].[M2] AS '0'
MEMBER [Measures].[M] AS '[Measures].[M1] + [Measures].[M2]'
--WAY 1
MEMBER [Measures].[Don't Show 0] AS 'IIF([Measures].[M2] = 0,NULL, [Measures].[M2])'
--WAY 2 - You have to put the selected measure in the set
SET [Selected Measure] AS '{[Measures].[M1]}'
MEMBER [Measures].[Don't Show 0 Bis] AS 'IIF([Selected Measure].Item(0) = 0, NULL, [Selected Measure].Item(0))'
SELECT
NON EMPTY CROSSJOIN({[Fecha]}, {[Don't Show 0 Bis]} ) ON COLUMNS,
--NON EMPTY [Fecha] ON COLUMNS, --<== This is for WAY 1
NON EMPTY{[Mercado].[Standard]} ON ROWS
FROM ST_StockResumenII
--WHERE ([Measures].[M1]) --<== YOU CAN FILTER MEASURE BY THIS IN WAY 1

Non empty for calculated members

I have a cube with a calculated member The calculation for the calculated me
mber "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 rows
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 "M
yMembers". The query seems to return correct values for the member but does
not suppress non empty valu
es. 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 200
0 server, Service Pack 3a is installed for sql server and analysis server.
Regards
Tore G.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 [Measur
es].[Units
Ordered] instead of [Measures].[MYTESTCAL]. It seems the query did n
ot
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/d...-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?
"T. Gylver" <nospam@.tgylnospam.no> wrote in message
news:9BD94A01-B03F-4B75-9224-CF3C9CF471E6@.microsoft.com...
> 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 rows
> 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.
> Regards
> Tore G.|||WITH
MEMBER [Measures].[M1] AS '10'
MEMBER [Measures].[M2] AS '0'
MEMBER [Measures].[M] AS '[Measures].[M1] + [Measures].&
#91;M2]'
--WAY 1
MEMBER [Measures].[Don't Show 0] AS 'IIF([Measures].[M2] = 0
,NULL, [Measures].[M2])'
--WAY 2 - You have to put the selected measure in the set
SET [Selected Measure] AS '{[Measures].[M1]}'
MEMBER [Measures].[Don't Show 0 Bis] AS 'IIF([Selected Measure].
Item(0) = 0, NULL, [Selected Measure].Item(0))'
SELECT
NON EMPTY CROSSJOIN({[Fecha]}, {[Don't Show 0 Bis]} ) ON C
OLUMNS,
--NON EMPTY [Fecha] ON COLUMNS, --<== This is for WAY 1
NON EMPTY{[Mercado].[Standard]} ON ROWS
FROM ST_StockResumenII
--WHERE ([Measures].[M1]) --<== YOU CAN FILTER MEASURE BY THIS IN WA
Y 1