I'm trying to use dynamic sets to handle the way Excel 2010 does subqueries when using filters, but I'm running into what seems to be a strange bug in the MDX query engine.
Suppose I have a time hiearchy with Year - Month - Date. I then create a dynamic set using:
CREATE DYNAMIC SET CURRENTCUBE.[YMDDS]
AS [Time].[YMD].[Date];
I then use this dynamic set as following:
CREATE MEMBER CURRENTCUBE.[Measures].[TestCount] AS
(Sum(Existing [YMDDS], 1)),
VISIBLE = 1;
If I open excel 2010 and add the testcount key figure and put YMD hierarchy in my report filter, filter this to say January 21 2012, January 22 2012, Feb 12 2012 I get 3 as it should. But if I change this to December 21 2011, January 22 2012, Feb 12 2012 so that it spans a year, I will get around 1500 or as many days as there are in the time dimension? It works fine with spanning weeks and months but not year? It's the same if I use count on the set. The strange thing is that it sometimes works, e.g. if I select the whole of 2011.
I have encountered this bug with two different cubes and 3 different hierarchies. If I have a hierarchy of Year tertial month week and day, it will work on year, tertial, week. But not month and day.
Any idea what is going on here?
