Welcome to MSDN Blogs Sign in | Join | Help

Microsoft Excel

The team blog for Microsoft Excel and Excel Services.
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.

Posted: Sunday, May 06, 2007 10:16 PM by David Gainer

Comments

Konduru Soma Sekhar said:

I want to know more things from excel 2007 by working in microsoft office and Autocad

# May 8, 2007 11:57 PM

John Greenan said:

Hi David,

Any chance of the developer roadmap that I have been asking for since 2006? You know, should developers build .xla, .xll, com addins, automation addins or whatever Microsoft has planned????

Pretty please??

Thanks,

John

# May 9, 2007 7:46 AM

David Gainer said:

For now, I can point you at the Excel developer portal:

http://msdn2.microsoft.com/en-us/office/aa905411.aspx

More coming later.

Dave

# May 10, 2007 4:26 PM

Stefan KZVB said:

I've got an Excel question who probably only a Excel developer can answer right now:

Is there a way to enable print gridlines or a specific theme for every new workbook/sheet? There's no setting in Tools/Options for those things - just things like font and number of sheets for new workbooks.

I know I can set those defaults by creating some XLTXs and putting them in the XLSTART-Folder but then the user name who saved the templates will be in the newly created workbooks and the settings  under Tools/Options will be ignored.

# May 14, 2007 5:53 AM

David Gainer said:

Not sure if this helps, but Harlan Pointed this out in a previous comment:

HKCU\Software\Microsoft\Office\<version>\Excel\Options

with Options, Options3, Options5, Options6, Options95 values? [BTW, WTH is Options6 from?] Specifically, the first thing I change on new Excel installs is adding 4000H to Options3 in order to eliminate gridlines.

# May 15, 2007 2:04 PM

Stefan KZVB said:

@David:

Adding 4000H top Options3 is a working way to hide the gridlines from the screen on new workbooks with Excel 2007. But I found no option to SHOW gridlines on the printout.

So only solution seems to be writing an addin with events and set the gridline option to print (worksheet.pagesetup.printgridlines) for new sheets.

But there really should be an easier way for administrators to specify this as default (and also a default theme)...

# May 16, 2007 4:43 AM

marcorusso said:

David, one feature removed from SP2 ("Ability to show/hide calculated members using the checkboxes in the filter drop down menus") is very disappointing. One of my customers has been forced to reinstall SQL Server 2005 to return to Analysis Services 2005 SP1 just for this reason. His cube has many of calculated members on non-measures dimensions.

Other customers has issues too. Even the Time Intelligence Wizard (of SSAS) creates calculated members on dimension attributes and they have the same issue in Excel 2007.

I really don't understand why you did this in a SP without giving a chance to get the previous behavior.

Do you have any news about this?

Thank you

Marco Russo

# May 19, 2007 8:10 PM

David Gainer said:

Hey Marco

Shoot me an email - thanks.

Dave

# May 21, 2007 12:09 PM

SQLBI - Marco Russo said:

The short answer is yes. The long answer is that Microsoft should still improve in many ways. I thought

# May 26, 2007 8:17 AM

Microsoft Excel said:

Today's author: Allan Folting, a program manager who works on the Excel team. I have gotten lots of questions

# February 6, 2008 1:21 AM

Noticias externas said:

Today&#39;s author: Allan Folting, a program manager who works on the Excel team. I have gotten lots

# February 6, 2008 1:44 AM
New Comments to this post are disabled
Page view tracker