Hello,

Please help. Can someone please tell me how to return only the 'non empty'
results from the following query using the FoodMart 2000 Sales cube:

with member [Measures].[AltadenaUnitSales] as '([Measures].[Unit Sales],
[Customers].[State Province].&[CA].&[Altadena], [Promotions].[Promotion
Name].&[Best Savings])'
select
{[Measures].[AltadenaUnitSales], [Measures].[Unit Sales]} on columns,
NON EMPTY [Customers].[All Customers].[USA].[CA].Children on rows
from Sales
where ([Promotions].[All Promotions].[Best Savings])


If I exclude the calculated member called [Measures].[AltadenaUnitSales],
then my query does return only the 'non empty' results as in:
select
{[Measures].[Unit Sales]} on columns,
NON EMPTY [Customers].[All Customers].[USA].[CA].Children on rows
from Sales
where ([Promotions].[All Promotions].[Best Savings])


However, I really need the calculated member for other calculations I intend
to do. In this sample query, the fact that the mdx is returning every leaf
level of the Customers dimension is really impacting performance on my cube.

I really do appreciate your assistance.

Kind regards,

Jason

Will this tweak to [AltadenaUnitSales] work?

'iif(IsEmpty([Measures].[Unit Sales]), NULL,
([Measures].[Unit Sales],
[Customers].[State Province].&[CA].&[Altadena],
[Promotions].[Promotion Name].&[Best Savings]))'
select
{[Measures].[AltadenaUnitSales], [Measures].[Unit Sales]} on
columns,
NON EMPTY [Customers].[All Customers].[USA].[CA].Children on rows
from Sales
where ([Promotions].[All Promotions].[Best Savings])

- Deepak

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