Is there a way to specify that every cell should be brought back using MDX?

Question

I'm using a query of the form:

Select {
    ([Measures].[M1], [Time].[2000]),
    ([Measures].[M2], [Time].[2000]),
    ([Measures].[M1], [Time].[2001]),
    ([Measures].[M2], [Time].[2001])
} on 0
From [Cube]
Where
    ([Some].[OtherStuff])

using Analysis Services 2008. Even though I'm not specifying NON EMPTY or similar, I'm still only getting three of the cells back (one of them is null).

How do I make sure that all the cells are brought back - even null ones?

Additional thoughts: The query above isn't actually the one I'm running (surprisingly enough:) ). The real one has a couple of hierarchies from the same dimension specified as part of the select, and also as part of the where clause. I'm wondering if it's something to do with that, but I can't think what exactly.

Additional additional thoughts:* This seems to be an AS2005/8 feature called Auto-Exists. Have a look at the relevant section on this MSDN article.

Answer 1

You're pulling back different dimensions, which doesn't fly too well. Try this instead:

with 
member [Time].[Calendar].[CY2000] as 
    ([Time].[Calendar].[2000], [Time].[Fiscal].[All Time])

member [Time].[Calendar].[FY2001] as
    ([Time].[Calendar].[All Time], [Time].[Fiscal].[2001])

select
    {[Time].[Calendar].[CY2000], [Time].[Calendar].[FY2001]}*
    {[Measures].[M1], [Measures].[M2]}
on columns
from [Cube]
where
    {[Some].[OtherOtherStuff]}

Answer 2

Not an answer but this should help narrow down the problem, a query that replicates the issue in AdventureWorks

SELECT

{ ( [Date].[Calendar].[Date].&[1], [Date].[Fiscal].[Date].&[1129] ) } on 0

FROM [Adventure Works]

I'd expect this to bring back the tuple specified and a Null, but instead no tuples are retrieved.

I think this is because the tuple located on two hierarchies of the same dimension where there is no commonality.