THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

SSAS 2005 SP2 breaks Excel calculated member selection

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.

Published Wednesday, March 7, 2007 10:25 PM by Marco Russo (SQLBI)



SQLBI - Marco Russo said:

I was tempted to start this post by this sentence “Analysis Services 2008 is not a new full release of

December 8, 2008 10:28 AM

SQLBI - Marco Russo said:

You know, selecting calculated members in Excel is not an option. I talked about this issue more than

February 19, 2010 8:07 PM

Mamta said:

I was able to get this to work for SSAS 2008 SP2.  If you change the setting for MdxSubqueries in the msmdsrv.ini file from 15 to 1 and restart the AS service, Excel 2007 will allow you to select only some of the calculated members.  Apparently, this issue was fixed since SAAS 2008 CU1 but this config setting was not published as the fix for this issue.  In fact, I don't think there is much (if any) documentation about this setting.

March 11, 2010 9:32 AM

Marco Russo (SQLBI) said:

Mamta, I investigated a little bit and the feature has been developed in SSAS2008 but it doesn't work with some features (selection of calculated members at different levels, inclusion of calculated members for Drilldown* functions.

Probably, this is an undocumented and unsupported behavior. Using 2008 R2 is the supported way.

March 18, 2010 5:49 PM

Akshai said:

Yes, Excel 2010 and AS 2008 R2 supports this more completely...

Excel had to change their query patterns to start including the calculated members in their drilldown requests -- that's why you need Excel 2010. It might work in some situations with Excel 2007 and AS 2008, but you may suddenly find some calculated members are missing -- or your TOP N filter returns you unexpected rows.



March 18, 2010 8:23 PM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement