In my last article I described the situation when Analysis Services reproduces incorrect results with Default Member defined on dimension attributes. Very similar problems occurs when IsAggragatable =False to dimension attributes.
The IsAggregatable property controls show or hide the topmost (aggregatable) level of hierarchy. Let say, you do not want to show the (All) level for some of you hierarchies. As example, IsAggragatable =False for scenario attribute , scenario dimension In Adventure Works database. Therefore this attribute does not have (All) level. But be careful setting IsAggragatable =False, there may be incorrect results in some circumstances using Excel 2007. Follow the steps to reproduce behaviour and get wrong results.
Set IsAggregatable = False to [Product]/[Product categories] attribute. Connect to cube and drag [Product categories] hierarchy on filter area, Gross Profit on values. You have to see the 1’st member from [Product categories] hierarchy: Accessories. Try changing filter values from Accessories to Bikes and then to both (Accessories and Bikes). When you select both, Bikes gross profit is not taked into account! Look below for screenchots and MDX generated by Excel 2007.
SELECT FROM [Adventure Works] WHERE ([Product].[Product Categories].[Category].&[4],[Measures].[Gross Profit])
SELECT FROM [Adventure Works] WHERE ([Product].[Product Categories].[Category].&[1],[Measures].[Gross Profit])
SELECT FROM (SELECT ({[Product].[Product Categories].[Category].&[4],[Product].[Product Categories].[Category].&[1]}) ON COLUMNS FROM [Adventure Works]) WHERE ([Measures].[Gross Profit])
I created BUG incident in Microsoft feedback, so please everyone that is interesting in IsAggregatable, vote for this case. https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=277058 . Hope Microsoft will then pay more attention.