Excel 2007 and SQL Server Analysis Services 2005 Service Pack 2

I have recently been talking to some customers (both at a conference and on a series of visits last week), and a number of them asked about Excel 2007 and Microsoft SQL Server Analysis Services – specifically, what changes in Excel when Services 2005 Service Pack 2 (or newer) is installed on the server.  There are a number of differences manifested in Excel 2007, so I thought I would list them here for folks.

First, some additional functionality is enabled in Excel.  Specifically, the following filter types are enabled in PivotTables connected to Analysis Services:

  • Label Filters, including the ability to filter based on member properties

  • Date Filters

  • Value Filters, including enhanced Top 10 Filters, evaluated in the context of the PivotTable (ex.: Top 5 customer list per year)

  • Expanding/collapsing items of attribute hierarchies placed next to each other on rows or columns

You might also notice that a couple of options are no longer available with Analysis Services 2005 Service Pack 2:

  • Ability to show/hide calculated members using the checkboxes in the filter drop down menus

  • Ability to turn off visual totals (Include hidden items in totals)

The reason behind these two changes is the way that Excel creates queries when Analysis Services has Service Pack 2 installed – Excel generates slightly different queries for the new filtering features, and these two features are not available with the query constructs Excel uses.