DefaultMember of Time Dimension (Analysis Services 2005)

I have a very small time dimension. This time dimension has only Year, Quarter, and Month (plus DateID which is just an identity column). I have one hierarchy for Y-Q-M. The DateID attribute is hidden.

I have the default member of the Date ID attribute set to:

Tail(NonEmptyCrossjoin([Date].[Date ID].Members, 1), 1).Item(0).Item(0)

This works if I do not choose any dates, meaning if my last date is December of 2006, I see the values for December 2006 by default. However, if I add the Month attribute into the browser as a row or a column, I only see October, November, and December (though every month has data)

If I remove the DefaultMember property, I can see all months data when I add the months to the columns or rows, but the data doesn't default to the month (without manually selecting a date) like I want it to. I tried removing the Y-Q-M hierarchy and just making a Y-M hierarchy, but still the same results.

I swear this was working once upon a time (maybe before the latest SP install?) ... I cannot recall exactly.

has anyone else experienced this? am I missing something?

Thanks,

Jason

Hi jsaido,

The behaviour of your solution is right, but unfortunatly is not right for you.

It's named autoexist.

Why you don't use Named sets?

For example:

CREATE SET CURRENTCUBE.[Current Date]

AS Tail(NonEmptyCrossJoin([Date].[Date ID].[Date ID].Members, 1), 1);

CREATE SET CURRENTCUBE.[Current Montht]

AS EXISTS([Date].[Month].[Month].members, [Current Date]);

CREATE SET CURRENTCUBE.[Current Quarter]

AS EXISTS([Date].[Quarter].[Quarter].members, [Current Date]);

CREATE SET CURRENTCUBE.[Current Year]

AS EXISTS([Date].[Year].[Year].members, [Current Date]);

|||I did try named sets - my delivery vehicle for this cube is Excel 2003 pivot tables which do not support named sets.