Excel 2007 PivotTable with calculated members

Today I had a big trouble with one customer.

We migrated an AS2000 solution to SSAS2005, making a lifting to dimensions just to consolidate into attributes what were indipendent dimensions in the old cube.

One of the old dimensions contained only one “dummy” member, and a series of calculated members. With Excel 2003, you had no troubles in selecting only the calculated members you wanted from those available. With Excel 2007, the user experience is bad.

First of all, you need to change PivotTable options to enable the display of calculated members (check PivotTable Tools / Options / Options / Display / Show calculated members from OLAP server). When you do that, you choose to get all calculated members available from a dimension/attribute. Now, if you have 100 calculated members into an attribute, chances are that you want to select only one or two of those members. Unfortunately, Excel 2007 shows you a list of disabled checkbox that cannot be used to select only the members you want.

I found only one workaround to this: use the “Convert to formulas” command into OLAP Tools menu and then delete all unwanted members.

I understand the reason for this behavior: Excel 2007 generates a MDX query using the AddCalculatedMembers function, that returns all calculated members.

However, I hope that this will be fixed in a future release and possibly with a Service Pack, because it’s a breaking change (at least from the user point of view) from the previous version.

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.