I love the new TFS dashboards that are available if you are using the full version of SharePoint 2010 or MOSS 2007. Unfortunately, the install instructions are geared for the MOSS 2007 install and have essentially left everyone to fend for themselves if they are working in the newer 2010 product. For many, this might not be a big deal, but the person setting up the TFS system may not be a SharePoint expert and translating the 2007 instructions into 2010 isn’t always clear.
I thought I had everything working great and made the appropriate leaps of faith, but was running into an issue on the dashboard that exposes the Excel reports through Excel Services and SSS. Every time the page loaded, I was getting an error on each chart: This workbook contains one or more queries that refresh external data… Do you want to enable queries to external data in this workbook?
The good part was that the solution is very simple and can be resolved in just a few mouse clicks.
- Log on to SharePoint 2010 Central Administration
- Under Application Management, click on Manage Service Applications
- Click on Excel Services Application
- Click on Trusted File locations
- Here you will see a list of file locations. When a user is accessing the site, it will choose the best match for the URL. If this is a dedicated SharePoint 2010 installation for your TFS instance, you might configure this at just http:// or https:// but you might have to pick a deeper location in the path if you have a bunch of other applications competing for resources and don’t want to have Excel Services opening locations for everyone. Click on the appropriate scope… Choose Edit.
- Validate the following Settings:
Location Type: Microsoft SharePoint Foundation
Trust Children: Checked
Allow External Data: Trusted data connection libraries and embedded
Warn on Refresh: UNCHECKED
Maximum Concurrent Queries per Session: 20
- Click OK
That’s all there is to it. I provided the recommended settings as a guide, but the Warn on refresh causes that dialog anytime the data needs to be updated… On a dashboard, that doesn’t create a very good user experience.