With the following select:

select non empty
{[Measures].[Umsatz]} on columns,
non empty [Artikel].[Artikelbezeichnung].Members on rows
from [Umsatz]
where
([Produktgruppe].[Alle Produktgruppe].[Glasmosaik (11)])

the result is 6 Members.

But with the following select:

WITH MEMBER Measures.[SKU]
AS '[Artikel].CurrentMember.Properties("Artikelnummer")'
select non empty
{[Measures].[SKU],[Measures].[Umsatz]} on columns,
non empty [Artikel].[Artikelbezeichnung].Members on rows
from [Umsatz]
where (
[Produktgruppe].[Alle Produktgruppe].[Glasmosaik (11)])

the result is AllMembers of [Artikel] , the WHERE clause
is ignored.

Is there a solution for a result which shows only the 6
members
with [Measures].[SKU] and [Measures].[Umsatz]

Many thank's to All.

Reinfried

The reason you getr "extra" members is because the calc that is equal to a
member property is not null, even if blank. Hence the rows are not filtered
using NON EMPTY. If the property is numeric, you can use an IIF expression
to set it to NULL if property is blank.

Also, be aware of the FILTER function, which is the MDX equivelent of SQL
WHERE.

public @ the domain below
www.tomchester.net

"Reinfried Englmair" <reinfried.englmair (AT) c-bergmann (DOT) at> wrote

Many thank's for the quick answer.
But I have some problems with the solution:

1. All [Artikel].[Artikelbezeichnung].Members have a
member-propety (= SKU)
2. I think that member properties are characters in the
cube
3. I work with OWC10, so the
[Produktgruppe] is in the Filter Axis (=WHERE),
[Artikel].[Artikelbezeichnung].Members are in the Row
Axis,
[Measures].[SKU] and [Measures].[Umsatz] are in the Data
Axis.
[Measures].[SKU] is an calculated member in the cube.

Tom, many thanks for a new idea.

There is a bug/glitch when using strings in MDX iif() expressions. This
has been discussed in the newsgroup. So, to get around it, you can
"hide" the string in a measure:

'[Artikel].CurrentMember.Properties("Artikelnummer")'
Member [Measures].[SKU] as
'iif(IsEmpty([Measures].[Umsatz]), NULL,
[Measures].[SKUText])'

Select non empty
{[Measures].[SKU], [Measures].[Umsatz]} on columns,
non empty [Artikel].[Artikelbezeichnung].Members on rows
From [Umsatz]
Where
([Produktgruppe].[Alle Produktgruppe].[Glasmosaik (11)])

Here is an earlier example from the Foodmart Sales cube:

http://groups.google.com/groups?hl=e...8&th=2f638c2fc
0ac644e&rnum=1



- Deepak

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

I like to call it "design limitation"

This is a great example, of calculated measure, which could benefit from
defining Non Empty Behavior on it pointing to the measure Umsatz. The
performance of NON EMPTY algorithm should improve over large sets

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