Cannot Refresh or Filter Data in Published Workbooks [Excel Services – SharePoint 2010]

As shown in the screenshot below, I was trying to consume data from SQL Server Analysis Services cube using Excel 2010 Pivot Table.


Refreshing and filtering data worked as expected on the client but when I published my workbook to Excel Services, filtering and slicing produced the following error


“The data connection uses Windows Authentication and Excel Services is unable to delegate user credentials.”
Of course, the workbook existed in a trusted location and the external data connection file resided in a trusted connection library.

To solve this issue, I navigated to the Central Administration and started “Claims to Windows Token services” as shown below :




This allowed me to refresh data using Windows Authentication…





Using external data in Excel Services [MOSS 2007]

Excel Services supports displaying data from external locations if those locations were configured as “Trusted”. Try creating a simple excel document with a Table that displays data from a SQL server table . Now try to view it in Excel Web Access. You will get the following error:


After some investigations, I found out that Excel services supports displaying data from external data sources, but not in a table format, only in a pivot table!

To work around this limitation, you need to convert your tables to pivot ones? How ? Download this tool and you are good to go !



For more info :

Error when publishing to MOSS 2007 “This file cannot be saved to this location because there is no connection….”

esterday, I was playing with Excel Services on an old MOSS 2007 VM that has no network. When publishing an Excel Spreadsheet to Excel Services, I received the following error:

Microsoft Office Excel This file cannot be saved to this location because there is no connection to the server. Check your network connection and try again. ”

After some quick research, I found out that this problem is related to the System Event Notification Service. To work around this issue, I ran net stop sens from the command line.


Cool Stuff : All up Business Intelligence Demo – Release 9.2

Microsoft has developed a comprehensive Business Intelligence solution demo (version 9.2) using SQL Server 2008 R2, Microsoft Office 2010 and SharePoint 2010 technology. This solution enables Business Intelligence for everyone at Contoso (fictional company created by Microsoft) through familiar tools, self–service capabilities, and access to critical business information.

There are two ways to access this demo solution, online remote access to server configured by Microsoft or via configuring virtual machine locally.

The Demo Release 9.2 has been made available on the Microsoft Partner site for download.  There are 42 files in the download (around 29 GB in all, once extracted though over 80).  Need some serious RAM and of course Hyper-V for it to work.

Key Features:
• SharePoint 2010 Insights, PerformancePoint Services, Excel Services, Visio Services, Reporting Services, Search, Social Relevance, Communities, Team Sites, People Profiles, Blogs, Organizational Browser, Workflows
• Silverlight, integrated with Bing Maps for the Enterprise and SQL Server Analysis Services
• Office 2010 Web Applications, Excel Co-editing
• PowerPivot for SharePoint, Excel
• PowerPivot Management Dashboard
• Report Builder 3.0
• SQL Server 2008 R2 DB Engine

Get it from here:

For more info :

Microsoft SharePoint 2010 MCPD & MCITP Certifications

Last month, I took the beta exams for SharePoint Server 2010. Over the last week, the results for these have been declared. I’m really glad to say that I have passed the exams. That gives me the right to use the following logos:






For those who are not aware of SharePoint 2010 Certifications, Microsoft now offers two separate tracks namely MCPD for Developers and MCITP for IT Professionals each containing two exams. For more information about the exams and how to prepare yourself for them, check Razi’s post here.

More About Feature Scope

Features are scoped, this means that you can have a feature activated at the farm, web application, site collection (site) , or site level (web). When a feature is activated at a scope, it also affects subscopes, so if you use a web application–scoped feature to add a custom action somewhere, then every site collection and every site beneath the web application will have the same custom action.You can combine different elements within a single feature as long as the elements are of compatible scope.

The following table lists some feature elements and the available scopes for each.



As you might have noticed, some features are available at more than one scope and the scope you select will depend on the granularity you need. If you want to add a custom action to the Site Actions menu of every site in every Web application, you can set the scope of the feature in which the element is defined to Farm. If you want to affect a single web within a site collection, set the scope to Web. Other features are available at only one or two scopes. For example, site columns (Field element) and content types only allow Site scope while List Instances and List Templates allow Site and Web scopes.

This does lead to a bit of a challenge when you want to add a web-scoped list template where you add some custom fields, which are site-scoped. More about activation challenges later…

Note: This is an excerpt from a lecture that I teach at USPJA