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.
