THE SQL Server Blog Spot on the Web

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

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

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.

Published Wednesday, January 31, 2007 7:52 PM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Reed Jacobson said:

Hi Marco,

Interesting post. It's particularly interesting that you can slice by a single calculated member, and that the Options "Show Calculated Members" applies only to non-Measure calculated members (calculated measures always appear). So I think you're correct that they would rather use AddCalculatedMembers than end up doing except filtering on calculated members tha may be ephemeral.

There is another workaround you may want to consider. You can create the "calculated" members explicitly in a dimension table. (Only one of the members in the table will "join" to the fact table.) Then in the MDX script you use a script assignment to put a calculation into the existing member rather than creating a calculated member dynamically. As far as Excel is concerned, it will be a real member. If you do this, you might want to make the dummy dimension a true separate dimension (rather than a separate hierarchy) to avoid Auto Exists behavior issues with the Time dimension key.

February 7, 2007 5:43 PM
 

Marco Russo (SQLBI) said:

Hi Reed,

this is exactly the solution I have thinked as a workaround, I hadn't time to implement it (other assignments in the middle) but I thought to create a real dimension "without relationship" to the fact table. This avoid the autoexists but still evaluates the MDX scripts. The only caveat is that you have to rebuild the cube if you change that dimension (not necessary if you only redeploy the MDX Script). It's not a problem in my actual case, but...

Thank you to confirm me this is going to be a working solution.

Marco

February 7, 2007 7:02 PM
 

SQLBI - Marco Russo said:

After many tries, today I came to the conclusion that Analysis Services 2005 SP2 breaks the Excel 2007

March 7, 2007 3:26 PM
 

arvind ravish said:

Hi Marco,

Where can i find the "Convert to formulas" command into OLAP Tools menu option?

April 7, 2009 2:20 AM
 

Marco Russo (SQLBI) said:

In the ribbon, when you're on a Pivot Table, you have a button "OLAP Tools" and clicking on it you choose the third menu, "Convert to formulas"

April 7, 2009 1:08 PM
 

arvind ravish said:

Hi Marco,

Can you explain in brief what does the

//CROSS SEMESTERS/QUARTER/MONTHS

//          Redirection on Calendar Hierarchy

script do in the calculation of your DateTool Solution ,as I am not able to understand it completely.

Thanks for the help in advance...

April 13, 2009 12:57 AM
 

Marco Russo (SQLBI) said:

That calculation is necessary to get the right number when you cross the Month attribute (12 members - no years here) and the Year attribute (and the same for Week/Year and Quarter/Year crosses).

In order to get the right number, the calculation is hijacked to the corresponding Calendar Hierarchy member.

April 13, 2009 1:33 PM
 

Giancarlo Ferrara said:

Hi Marco.

There is an other way to force Excel 2007 to work with calculated members.

Set the default format to Excel 97-2003.

No matter how you will save really to the old format or the 2007 format, if you set up this as your standard format, Excel 2007 will create the pivot table as a PivotTable 10 version (and not as 12 version).

In this way you can select/unselect any calculated member.

I use a time period dimension for YTD, Delta YTD, Prev Year and so on and with this workaround it work fine

June 4, 2009 9:24 PM
 

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

Leave a Comment

(required) 
(required) 
Submit

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

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement