Ramunas Balukonis Blog

Default Members are good thing in BI environment. You can define Default member as “Last month” in Date dimension, “Active” in status dimension and so on.
But be careful defining default members: Analysis Services returns incorrect results with default member in some circumstances. To reproduce buggy behaviour, open Adveture Works, define default member as [Road Bikes] subcategory in [Product].[Product Categories] hierarchy. First, set Category attribute default member to “[Product].[Category].&[1]” or [Product].[Category].[Bikes], and then set “subcategory” default member to “[Product].[Subcategory].[Subcategory].&[2]” or [Product].[Subcategory].[Subcategory].[Road Bikes].

From Excel 2007, connect to Adventure Works cube and put [Product].[Product Categories] in Filter area, Gross profit on data area. Try select [Road Bikes], [Mountain Bikes] or both ([Road Bikes], [Mountain Bikes]) in [Product].[Product Categories]. For multi-select Excel 2007 shows incorrect results, only default member is taked into account! See below screenshots and generated MDX.

SELECT  FROM [Adventure Works] WHERE ([Product].[Product Categories].[Subcategory].&[1],[Measures].[Gross Profit]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

SELECT  FROM [Adventure Works] WHERE ([Product].[Product Categories].[Subcategory].&[2],[Measures].[Gross Profit]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

 

SELECT  FROM (SELECT ({[Product].[Product Categories].[Subcategory].&[1],[Product].[Product Categories].[Subcategory].&[2]}) ON COLUMNS  FROM [Adventure Works]) WHERE ([Measures].[Gross Profit]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

I created BUG incident in Microsoft feedback database and everyone could vote for it, hope Microsoft will recognize this error and pay attention on this case:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=275207 .