Issue with SubSelect in MDX Query generated by Excel 2007 with Analysis Services 2005

I am using Excel 2007 for connecting to SSAS 2005 (AdventureWorksDW) cube and created a very simple report on perspective "Financial Reporting" where Account (Net Income) is taken on row, Column has Scenario (Actual and Budget) and Fiscal Year (FY 2002) and measure selected is "Amount". Now with this setup (case 1.1) report runs fine and displays the data correctly. However when we move the Scenario to Filter axis (Case 1.2) the Excel shows data only for Actual scenario and Budget data is not considered in the aggregation.

 

However when we create the same report in a Excel file (which is saved in 2003 compatibility mode) the data gets aggregated and the result is displayed as per the expectation.

 

Points that needs to be considered are:

• Pivot table version used in both cases will be different
• Usage of SubSelect (introduced from AS2005 SP2) in Excel 2007 version

 

Excel 2007:

Case 1.1: Scenario on Columns

Report Output:

MDX Generated:

SELECT NON EMPTY CrossJoin(Hierarchize({[Scenario].[Scenario].[Scenario].Members}), Hierarchize({DrilldownLevel({[Date].[Fiscal Year].[All Periods]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize({[Account].[Accounts].[Account Level 01].Members}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[Account].[Accounts].[Account Number],[Account].[Accounts].[Account Type],[Account].[Accounts].[Accounts] ON ROWS  FROM (SELECT ({[Scenario].[Scenario].&[1], [Scenario].[Scenario].&[2]},{[Date].[Fiscal Year].&[2002]}) ON COLUMNS , ({[Account].[Accounts].&[47]}) ON ROWS  FROM [Adventure Works]) WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

MDX Output:

 

Case 1.2: Scenario on Filter Axis (Scenario member selection is still ‘Actual’ and ‘Budget’ both)

Report Output:

MDX Generated:

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Date].[Fiscal Year].[All Periods]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize({[Account].[Accounts].[Account Level 01].Members}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[Account].[Accounts].[Account Number],[Account].[Accounts].[Account Type],[Account].[Accounts].[Accounts] ON ROWS  FROM (SELECT ({[Date].[Fiscal Year].&[2002]}) ON COLUMNS , ({[Account].[Accounts].&[47]}) ON ROWS  FROM (SELECT ({[Scenario].[Scenario].&[2],[Scenario].[Scenario].&[1]}) ON COLUMNS  FROM [Adventure Works])) WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

MDX Output:

 

Excel 2007 in 2003 Compatibility Mode:

Case 2.1: Scenario on Columns

Report Output:

MDX Generated:

SELECT NON EMPTY CrossJoin(Hierarchize(Except({AddCalculatedMembers(Except({AddCalculatedMembers([Scenario].[Scenario].[Scenario].members)}, {[Scenario].[Scenario].[Budget Variance %],[Scenario].[Scenario].[Budget Variance],[Scenario].[Scenario].&[3]}))}, {[Scenario].[Scenario].[Budget Variance %],[Scenario].[Scenario].[Budget Variance],[Scenario].[Scenario].&[3]})),Hierarchize(Except({AddCalculatedMembers(Except({AddCalculatedMembers(DrilldownLevel({[Date].[Fiscal Year].[All Periods]}))}, {[Date].[Fiscal Year].&[2007],[Date].[Fiscal Year].&[2005],[Date].[Fiscal Year].&[2004],[Date].[Fiscal Year].&[2003]}))}, {[Date].[Fiscal Year].&[2007],[Date].[Fiscal Year].&[2005],[Date].[Fiscal Year].&[2004],[Date].[Fiscal Year].&[2003]}))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize(Except({AddCalculatedMembers(Except({AddCalculatedMembers([Account].[Accounts].[Account Level 01].members)}, {[Account].[Accounts].&[95],[Account].[Accounts].&[1]}))}, {[Account].[Accounts].&[95],[Account].[Accounts].&[1]})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS  FROM [Adventure Works] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

MDX Output:

 

Case 2.2: Scenario on Filter Axis (Scenario member selection is still ‘Actual’ and ‘Budget’ both)

Report Output:

MDX Generated:

WITH   MEMBER [Scenario].[Scenario].[XL_QZX] AS ‘Aggregate({[Scenario].[Scenario].&[2],[Scenario].[Scenario].&[1]})’ SELECT NON EMPTY Hierarchize(Except({AddCalculatedMembers(Except({AddCalculatedMembers(DrilldownLevel({[Date].[Fiscal Year].[All Periods]}))}, {[Date].[Fiscal Year].&[2007],[Date].[Fiscal Year].&[2005],[Date].[Fiscal Year].&[2004],[Date].[Fiscal Year].&[2003]}))}, {[Date].[Fiscal Year].&[2007],[Date].[Fiscal Year].&[2005],[Date].[Fiscal Year].&[2004],[Date].[Fiscal Year].&[2003]})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize(Except({AddCalculatedMembers(Except({AddCalculatedMembers([Account].[Accounts].[Account Level 01].members)}, {[Account].[Accounts].&[95],[Account].[Accounts].&[1]}))}, {[Account].[Accounts].&[95],[Account].[Accounts].&[1]})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS  FROM [Adventure Works] WHERE ([Measures].[Amount],[Scenario].[Scenario].[XL_QZX]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

MDX Output: