Calculations in a subcube world

I'm having trouble getting my calculations to work with query tools that use subcubes in the WHERE clause instead of a normal slicer. Both Excel 2007 and SSRS (when using the query designer) use this technique.

This is an example of a calculation:

CREATE MEMBER CURRENTCUBE.[MEASURES].[YTD]

AS SUM(

PERIODSTODATE([Time].[Y-Q-M-D].[Year],

[Time].[Y-Q-M-D].CurrentMember),

[Measures].CurrentMember),

FORMAT_STRING = "#,#",

VISIBLE = 1;

A typical query through Excel 12 will use the following MDX to filter when selecting March 2006 from a time hierarchy:

..FROM (SELECT ({[Time].[Y-Q-M-D].[Quarter].&[2006]&[1]}) ON COLUMNS FROM [Datawarehouse])

Instead of the old-fashioned

WHERE([Time].[Y-Q-M-D].[Quarter].&[2006]&[1])

When querying for March 2006 filtered by a subcube the mentioned calculated member will return NULL, when hand crafting the query using the WHERE construct, it will return the expected results.

Running a select through Excel 2007 with the filter and the following test member makes the problem obvious:

CREATE MEMBER CURRENTCUBE.[MEASURES].[test]

AS [Time].[Y-Q-M-D].CurrentMember.Name,

VISIBLE = 1;

The .CurentMember of the Y-Q-M-D hierarchy is the All member when a subcube is used, not the actual member(s) in the filter.

Can anyone advice me on how to formulate my calculations so they work with subcubes?

Hi Peter

This is a known issue. Please see the following blog entry for an elaboration.

http://www.sqljunkies.com/WebLog/reckless/archive/2006/03/08/18601.aspx