....in my brain...mind the gap as the London underground tells you...

Why isn't this allowed - why is a tupple - should think the where clause is
to be interpreted as IN..?

"DRILLTHROUGH SELECT {[Measures].[Unit Sales]} on Columns,
{[Store].[USA].[OR].[Salem]} on ROWS FROM SALES WHERE
([Time].[1997].[Q1],[Time.[1997].[Q2])"

Is there any other way to solve it ? Can use IIF-statements ?

and another small question - why is it possible just to write Salem instead
of [Store].[USA].[OR].[Salem] ?

--Michael V

Its a common misconception that the MDX WHERE clause is like the SQL WHERE
clause - it isn't!!!

Your WHERE clause can only slice on one member from each dimension A way
around this is to declare a calculated member that is an aggregation of the 2
(or more) members that you have in your WHERE clause currently. Like this:

WITH MEMBER Time.MySlicer AS 'AGGREGATE({Time.1997.Q1, Time.1997.Q2})'
SELECT {Measures.[Unit Sales]} on Columns,
{Store.USA.OR.Salem} on ROWS
FROM SALES
WHERE (Time.MySlicer)

The reason you can get away with [Salem] is cos its the only member in the
dimension with that name. You also don't need all of those square brackets as
you can see above - only where there is a space in the name.

What you might want to do is put another level into the Time dim inbetween
Year and Quarter called [Half Year] which can be used as a slicer in case you
are going to be issuing this sort of query alot!

Hope that helps

Jamie Thomson
http://www.conchango.com


"Michael Vardinghus" wrote:

Thanks Jamie - that helped a lot

My problem is however that i need it in a drillthrough select statement and
there aggregates won't work - your idea about
half year sounds good but what do i do 30-09 - then i want it to get q1, q2
and q3 ?



"Jamie" <Jamie (AT) discussions (DOT) microsoft.com> skrev i en meddelelse
news47405F2-54BF-4FA5-8D8A-050791175CED (AT) microsoft (DOT) com...

and i actually need it for all monjths as well....if user has chosen april i
want jan, feb, mar, apr ...

Have an acc calculated measure but when drill i want it to get all
transactions to the chosen date....


"Michael Vardinghus" <michaelvardinghus (AT) notexisting (DOT) com> skrev i en
meddelelse news:ex%23FfnYqEHA.3712 (AT) TK2MSFTNGP15 (DOT) phx.gbl...

Hi Michael,
I'm afraid I don't know much about DRILLTHROUGH so can't help much here. In
regular MDX the method I previously proposed is the only way I know how to do
what you want to do, and if DRILLTHROUGH doesn't allow calculated members
then I'm guessing you can't do it.
Your alternative is to crossjoin the Measures dimension with the required
Time dimension members on the Columns axis and therefore show Unit Sales per
store and time period - but then this isn't what you want is it?

By the way, the : operator would be useful for what you want to do, but I
still can't see how you're going to get around your main problem without
using a calculated member.

If there's a way of doing it - I don't kow it! Sorry!.

Regards
Jamie Thomson


"Michael Vardinghus" wrote:

For what its worth...AS2005 has a new feature which allows you to apply the
kind of WHERE clause that you after i.e. Reduce the cellset from which you
are selecting. Confusingly this sub-cellset is defined in the FROM clause.

Regards
Jamie Thomson

"Michael Vardinghus" wrote: