I have a dimension that has a level of [Customer Number] and a property of
Customer Name. I would like to list all customer numbers with customer name
and their sales([Measures].[Sales]) sliced by whatever. However any query
that I can come up will lists ALL customers in the Customer Number Dimension
including empty members (no Sales).

Here is an example of the closest thing that I can get from the FoodMart
database.
with member [Measures].[CustomerCard] as
'[Customers].CurrentMember.properties("Member Card")'
select NON EMPTY{Filter({[Measures].[CustomerCard]}, [Measures].[Profit] <>
0),[Measures].[Profit]} on axis(0),
NON EMPTY{[Customers].members} on axis(1)
from Sales
where ([Product].[Drink])

I assume that "CustomerCard", since it is a property present on every
customer, passes the NON EMPTY test thus it shows with no measure value.

Can anyone provide a working example or tweak my query to make it work
correctly?
Thanks,
Chris Umbaugh
chrisumbaugh (AT) toast (DOT) net

This query will only return customers with the property:

member [Measures].[CardText] as
'[Customers].CurrentMember.properties("Member Card")'
member [Measures].[CustomerCard] as
'iif(IsError([Customers].CurrentMember.properties("Member Card")),
[Measures].[Null], [Measures].[CardText])'
select NON EMPTY{[Measures].[CustomerCard]} on axis(0),
NON EMPTY [Customers].members on axis(1)
from Sales
where ([Product].[Drink])
- Deepak

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

Thank you for your help. This seems obvious to me now. Actually I was
looking for something slightly different, but using your tip it was an easy
change. This is what actually solved my problem:

with member [Measures].[Null] as 'NULL'
member [Measures].[CardText] as
'[Customers].CurrentMember.properties("Member Card")'
member [Measures].[CustomerCard] as 'iif(IsEmpty([Measures].[Profit]),
[Measures].[Null], [Measures].[CardText])'
select NON EMPTY{[Measures].[CustomerCard],[Measures].[Profit]} on axis(0),
NON EMPTY [Customers].members on axis(1)
from Sales
where ([Product].[Drink])

Thanks again,
Chris Umbaugh

"dpuri" <dpuri (AT) anonymous (DOT) com> wrote