Date Filtering vs Row placement misbehaviour

I am using the classic Add Business Intelligence date dimension hierachy and getting correct results when I had my calendar hierachy on the rows expanded to the day level and my dimension calculations on the columns. The results were wrong when I moved the calendar hierachy to the filter area and selected one date.

In particular, the [Date].[Date Calculations].[Current Member Name] = [Date].[Reporting Calendar].CurrentMember.Name shows that when you filter the Hierachy down to the day, the Current Member is always incorrectly showing the week (in every cell). It is correct when filtered to all other higher levels than date (week, month...).

I have tested this on multiple date dimensions for some days now and it is very consistent. Also If you bring the Date Attribute Hieracy onto the rows, it jumps to the correct Current Member.

Anyone seen anything like this - filtering by a Hierachy member does not work at the lowest level (the day level) but it does work as a row heading? It may be related to the lowest level being the Dimension Key?

Weird.

See my post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=517552&SiteID=1 with TOPCOUNT and many hierarchies. I am also getting inconsistent results.

Are we missing somehting when we set a 'conformed' hierarchy? I did not have the lowest level as the dimesion key. Then I set it to the DImension key for my 11 hierarchies in the Product dimension. It worked for a small sample data set and then it started failing when I introduced it to the main project with 10+ dimensions and many hierarchies.

Let me have your views on this.

|||

I looked at your issue. It is not obvious to me that the problem I had with .CurrentMember being wrong under a lowest level filter member is the same as your TOPCOUNT problem. I had to move my formulae out to a different dimension and put them on an attribute hierachy to work.

However, by lots of trial and error with the display of simple diagnostic formulae, it is likely that you can identify a key simple value that is not correct. We need more such simple problem examples because I am convinced there is a basic structural problem in the implimentation of MDX against UDM and it will show up as the wrong answer in many different circumstances.

Good luck with finding a structure where the formulae works - at least UDM had many choices.

|||

If you have defined date hierarchies such as Fiscal or calendar, have you ensured that the key element of the attributes used in the hierarchy is unique? You might have to use the key collection to define a combined key of Year, Quarter for the quarter in the attribute hierarchy.

I think that is why you maybe seeing the wrong results. I am looking through all my attribute hierarchies to ensure that I have these set-up correctly.

|||

I am using unique running integers as the key of the attributes in the hierachy.

Also I have repeated the problem in any other dimension hierachy where the lowest level used as a filter member causes the .Currentmember to get the incorrect value (it gets the parent member of the filter value)

So it seems to be an issue on even the simplest dimension hierachy when the formulae is added to a hierachy within the filter dimension. I fixed it by moving all the formulae out to a completely different dimension.