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?
The problem was that the attribute hiearchies were wrong. In case someone stumpled upon this as well.