how to excluded the intersection of 2 dimensions ?

Hi,

I have the following simplified problem.

I have a fact table and two dimension tables, colors and weekdays.

I can make a query to select all except red and all except on monday but I like to make a query to select all except red mondays ?

They query tool doesn't give you really the options.

Any suggestions ?

Constantijn Enders

You can do it in MDX, but only a few query tools will allow you to construct queries like this. I think vendors tend to refer to this capability as 'asymmetic sets'; I can only think of one tool I've seen recently that did this (Intelligencia - http://www.it-workplace.co.uk/i4wfeatures.aspx) but I'm sure that if you hunt around there will be others. Does anyone else know of one?

Here's an MDX query to prove it's possible:

select

measures.[internet sales amount] on 0,

except(

[Date].[Day Name].[Day Name].members

*

[Product].[Color].[Color].members

,{([Date].[Day Name].&[2], [Product].[Color].&[Red])}

) on 1

from [Adventure Works]

HTH,

Chris