Hi everyone,

I have the following MDX for FoodMart 2000:

SELECT CROSSJOIN({[Measures].[Unit
Sales]},{FILTER([Time].[1997].[Q3]:[Time].[1998].[Q4],[Measures].[Unit
Sales] <7000)}) ON COLUMNS,
{[Product].[Drink]} ON ROWS FROM [WareHouse and Sales]

The intend here is to only show Unit Sales for [Product].[Drink] for
1997 Q3 and Q4 quarters. However, this MDX returns all 1998 quarters.
The reason being The search condition in the Filter function above
considers [Product].[All Products] member and therefore excludes the
desired 1997 quarters.

I tried to nartow the the Filter search condition to
[Product].CurrentMember as follows but get the same result:

SELECT CROSSJOIN({[Measures].[Unit
Sales]},{FILTER([Time].[1997].[Q3]:[Time].[1998].[Q4],([Product].Current
Member,[Measures].[Unit Sales])<7000)}) ON COLUMNS,
{[Product].[DRINK]} ON ROWS FROM [WareHouse and Sales]

My intend for this query is to filter out those quarters after 1997 Q2
which have Unit Sales of less than 7000. So the resultset should be:

Unit Sales
1997 Q3 1997 Q4
Drink 6065 6661


All help is greatly appreciated,
kr

*** Sent via Developersdex http://www.developersdex.com ***

Assuming that quarters with no data should be excluded, this version of
the query returns the desired results:

FILTER([Time].[1997].[Q3]:[Time].[1998].[Q4],
([Measures].[Unit Sales],[Product].[Drink]) <7000)) ON COLUMNS,
{[Product].[Drink]} ON ROWS FROM [WareHouse and Sales]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,

Thanks for your reply. I guess I did not use a good MDX to truely convey
my problem. Please consider the following MDX:

select
{ crossjoin( {[Product].[All Products].children},{[Measures].[Unit
Sales] } )} on rows,
{ crossjoin( { [Store].[All Stores].[USA].children},
{filter(([Time].[1997].[Q1]:[Time].[1998].[Q3]),[Measures].[Unit
Sales]>0) } ) } on columns
from [Warehouse and Sales] WHERE [Promotions].[Big Time Discounts]


Here I get back Q1 and Q4 for all stores while my desired resultset
should look like the following:

OR WA
Q4 Q1
Drink Unit Sales 39.00 32.00
Food Unit Sales 371.00 291.00
Non-Conum Unit Sales 123.00 76.00

Effectively, I want to suppress all zero cells. This seems like a common
and easy problem but I can not get the Filter funciton to behave. Do you
have any ideas?

thx,
kr




*** Sent via Developersdex http://www.developersdex.com ***

In AS 2000, there is a distinction between empty and zero valued cells -
I think you wish to eliminate empty, like:

crossjoin([Product].[All Products].children,
{[Measures].[Unit Sales] }) on rows,
NON EMPTY crossjoin([Store].[All Stores].[USA].children,
{[Time].[1997].[Q1]:[Time].[1998].[Q3]}) on columns
from [Warehouse and Sales]
WHERE [Promotions].[Big Time Discounts]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Deepak,

Thank you. That' what I wanted to do.


*** Sent via Developersdex http://www.developersdex.com ***