Hi I have an MDX-guru question.

[Knut Ivar]

Hi
I would like to do add a filter on my MDX result... We would only like to
see rows in the result table that have values in the measure
[Measures].[DiseaseCount]. If the [Measures].[DiseaseCount] contain (null) I
would like to remove the row from the result set.

SELECT NON EMPTY { [Measures].[DiseasePctLast12Months],
[Measures].[DiseasePctRegion], [Measures].[DiseasePctLast12MonthsCompany],
[Measures].[DiseasePctLast12MonthsRegion],
[Measures].[DiseasePctLast12MonthsGroup], [Measures].[DiseasePctGroup],
[Measures].[DiseaseCount], [Measures].[DiseasePct],
[Measures].[DiseasePctCompany] } ON COLUMNS, NON EMPTY {
([Harvest].[HarvestID].[HarvestID].ALLMEMBERS *
[Disease].[DiseaseName].[DiseaseName].ALLMEMBERS ) } DIMENSION PROPERTIES
MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( {
[Dates].[YearMonthDate].[PeriodMonth].&[2006m06] } ) ON COLUMNS FROM ( SELECT
( { [Harvest].[HarvestID].&[792576] } ) ON COLUMNS FROM [GildeCube])) WHERE (
[Dates].[YearMonthDate].[PeriodMonth].&[2006m06] )

aprox haf of the rows in this result have (null) in the
[Measures].[DiseaseCount].

Does any one have any ide for where to add an filtere to remove the? (If it
was in SQL as I have much experience with, it would be very easy - but in MDX
I am ony an newbe.


in sql the filter would look like this... where isnull(DiseaseCount,0)

0

Thanks For anysuggestion.

Kind Regards
Knut Ivar

[Peter Yang [MSFT]]

Hello Knut,

I understand that you'd like to filter NULL from the MDX result. If I'm
off-base, please let me know.

You may want to consider use NonEmptyCrossjoin and I have included the
following article for your reference

http://www.sqlmag.com/Articles/ArticleID/26100/pg/2/2.html

You may want to use
NonEmptyCrossJoin([Harvest].[HarvestID].[HarvestID].members,
[Disease].[DiseaseName].[DiseaseName].members) to test the sitaution.

If you have any update or comments, please feel free to let's know. Thank
you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

[Knut Ivar]

Hi Peter!
Thank you for the link - there was some usefully information there.
But I tryes apply your code-suggestion to my big MDX-query above - but
Haven't found out where to add it. I only get cryptical-MDX error's when I
tryes... (a am only an newbe to MDX).

Do you have any suggestion where to add the code you posted in to my MDX?

Thanks
Knut Ivar

Post by Peter Yang [MSFT]
Hello Knut,
I understand that you'd like to filter NULL from the MDX result. If I'm
off-base, please let me know.
You may want to consider use NonEmptyCrossjoin and I have included the
following article for your reference
http://www.sqlmag.com/Articles/ArticleID/26100/pg/2/2.html
You may want to use
NonEmptyCrossJoin([Harvest].[HarvestID].[HarvestID].members,
[Disease].[DiseaseName].[DiseaseName].members) to test the sitaution.
If you have any update or comments, please feel free to let's know. Thank
you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

[Peter Yang [MSFT]]

Hello Knut,

I think you may want to try the following MDX to see if it works:

SELECT NON EMPTY { [Measures].[DiseasePctLast12Months],
[Measures].[DiseasePctRegion], [Measures].[DiseasePctLast12MonthsCompany],
[Measures].[DiseasePctLast12MonthsRegion],
[Measures].[DiseasePctLast12MonthsGroup], [Measures].[DiseasePctGroup],
[Measures].[DiseaseCount], [Measures].[DiseasePct],
[Measures].[DiseasePctCompany] } ON COLUMNS, {NON EMPTY
NonEmptyCrossJoin([Harvest].[HarvestID].[HarvestID].members,
[Disease].[DiseaseName].[DiseaseName].members)} DIMENSION PROPERTIES
MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( {
[Dates].[YearMonthDate].[PeriodMonth].&[2006m06] } ) ON COLUMNS FROM (
SELECT
( { [Harvest].[HarvestID].&[792576] } ) ON COLUMNS FROM [GildeCube])) WHERE
(
[Dates].[YearMonthDate].[PeriodMonth].&[2006m06] )


If not, please first try:

SELECT NON EMPTY { [Measures].[DiseasePctLast12Months],
[Measures].[DiseasePctRegion], [Measures].[DiseasePctLast12MonthsCompany],
[Measures].[DiseasePctLast12MonthsRegion],
[Measures].[DiseasePctLast12MonthsGroup], [Measures].[DiseasePctGroup],
[Measures].[DiseaseCount], [Measures].[DiseasePct],
[Measures].[DiseasePctCompany] } ON COLUMNS,
NON EMPTY NonEmptyCrossJoin([Harvest].[HarvestID].[HarvestID].members,
[Disease].[DiseaseName].[DiseaseName].members) ON ROWS FROM [GildeCube]

If you have any update, please feel free to let me know. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.