Unexpected result from Analysis Services when Dimension DefaultMember is not All and a Report Filter is applied in Excel

This article helps you work around the problem wherein you get an unexpected result from Analysis Services when Dimension DefaultMember is not All and a Report Filter is applied in Excel.

Original product version:   SQL Server
Original KB number:   2607575

Summary

When the DefaultMember of a Dimension attribute hierarchy is set to a member other than the ALL member and you execute an MDX query that uses a sub-select, which returns a set that excludes the DefaultMember, the values returned in the outermost SELECT are the aggregate values associated with the members of the set defined in the sub-select. If the Multidimensional Expressions (MDX) query uses a sub-select, which returns a set that includes the DefaultMember, the values returned in the outermost SELECT are the values associated with the DefaultMember.

The designed behavior is that at the outermost SELECT, the default members on the axes are determined based on what is available in the sub-cube space defined by any inner SELECT statements. If the DefaultMember is in the slicer but not on an axis, the value(s) associated with the DefaultMember are returned. If the DefaultMember is excluded from the slicer, the values returned are overwritten by the aggregate values under an 'ALL' member.

More information

When using Excel 2007 or Excel 2010 to query an Analysis Services database, a fairly common practice and frequent necessity is to slice the cube by setting a filter using one or more of the dimensions in the 'Report Filter' area of the Pivot Table control. When this action is performed, the Excel client generates an MDX query that includes an inner SELECT or sub-select clause to filter the data and restrict the cube space. For example, using Excel 2010 and connecting to the Adventure Works DW 2008 database, placing [Measures].[Internet Sales Amount] in the Values section of the Pivot Table, adding the [Customer].[Customer Geography] hierarchy including only the [Customer].[Customer Geography].[Country].[Canada] member, and adding the [Product].[Category] dimension to the Row Filter area then setting a filter that includes only [Product].[Category].[Accessories] and [Product].[Category].[Bikes], the value returned is $1,924,680.24. In that case, the query generated by Excel is:

SELECT
NON EMPTY Hierarchize(AddCalculatedMembers(
{DrilldownLevel({[Customer].[Customer Geography].[All Customers]})}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME ON COLUMNS
FROM (SELECT ({[Customer].[Customer Geography].[Country].&[Canada]}) ON COLUMNS
FROM (SELECT ({[Product].[Category].&[1], [Product].[Category].&[4]}) ON COLUMNS
FROM [Adventure Works]))
WHERE ([Measures].[Internet Sales Amount])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

If the Filter conditions are changed so that the Row Filter now contains the members [Product].[Category].[Accessories] and [Product].[Category].[Clothing], the value returned is $156,542.47 and the query generated by Excel is:

SELECT
NON EMPTY Hierarchize(AddCalculatedMembers(
{DrilldownLevel({[Customer].[Customer Geography].[All Customers]})}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME ON COLUMNS
FROM (SELECT ({[Customer].[Customer Geography].[Country].&[Canada]}) ON COLUMNS
FROM (SELECT ({[Product].[Category].&[3], [Product].[Category].&[4]}) ON COLUMNS
FROM [Adventure Works]))
WHERE ([Measures].[Internet Sales Amount])
 CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

If the design of the [Product].[Category] attribute hierarchy is modified to set the DefaultMember property to [Product].[Category].&[2], and the Pivot Table is set to use the following filter condition, the [Customer].[Customer Geography].[Country].[Canada] member on columns, with the [Product].[Category] dimension to the Row Filter area including only [Product].[Category].[Accessories] and [Product].[Category].[Bikes], the query generated by Excel is identical to the first and the value returned is $1,821,302.39 although the aggregate value for both [Product].[Category].[Accessories] and [Product].[Category].[Bikes] at the intersection with [Customer].[Customer Geography].[Country].&[Canada] is $1,924,680.24. If the [Product].[Category] attribute hierarchy is moved to either the Rows or Columns axis, the value displayed for the 'Grand Total' is $1,924,680.24. If the [Product].[Category] attribute hierarchy is moved back to the Row Filter area and the Filter conditions are again changed so that the Row Filter now contains the members [Product].[Category].[Accessories] and [Product].[Category].[Clothing], the value returned is $156,542.47 and the MDX query generated by Excel is identical to the second MDX query above.

This behavior is by design. The default members are determined during query execution based on what members are available in the cube space. If the default member of a hierarchy is in the slicer that defines the cube space used for query execution and the hierarchy is not explicitly included on one of the axes, the value(s) associated with the default member are returned. If the default member of a hierarchy is in the slicer that defines the cube space used for query execution and the hierarchy is explicitly included on one of the axes, the values for the individual members are returned and the aggregate value for the members is displayed as a Grand Total. If the default member of a hierarchy is not in the slicer that defines the cube space used for query execution and the hierarchy is not explicitly included on one of the axes, the aggregate values of the members in the slicer are returned as an 'ALL' member.

Workaround

To work around this behavior, filter hierarchies with DefaultMembers set to a member other than the 'ALL' member on either Rows or Columns.