After many tries, today I came to the conclusion that Analysis Services 2005 SP2 breaks the Excel 2007 calculated member selection feature. I already talked about this issue here, but today I found the way to reproduce the error.
This is a scenario that works well:
- Analysis Services 2005 SP1 + hotfixes (build 2153)
- Excel 2007 RTM
- Cube with several calculated members on a hierarchy or attribute (not on measures)
- When you build a pivot table with Excel, you can freely select single calculated members from the hierarchy or attribute.
Adding Analysis Services 2005 SP2 even only on the server, you get all the calculated members of the hierarchy or attribute selected, and they are all always visible.
In my opinion, this is a breaking change of SP2. I don’t see any reason for this behavior. Remember, is is suffice to update the server to break calculated member selection on any Excel 2007 client, regardless of the client component version you use.
I posted a bug here: please vote the bug to get an higher rating if you think (like me) that this requires a promptly response from Microsoft.
UPDATE February 20, 2010: SQL Server 2008 R2 and Excel 2010 have solved the issue – you need both. Chris Webb posted a description of the fix.