Please check latest updates at the end of the article.
In my previous post I was talking about new 2010 Office Data Connectivity Components, or Microsoft.ACE.OLEDB.14.0. I have a small sample there how to programmatically access SharePoint 2007 data using this component. While this approach works great when you are building any custom solution, there is also a way to use these components without any coding at all.
Microsoft.ACE.OLEDB.14.0 can be used in Excel Services, working around its limitation on SharePoint data sources. For instance this could be used to create Excel Charts and Pivot tables, based on SharePoint data source.
This solution tested on SharePoint 2007 Server (MOSS) and Microsoft Office 2007.
Prerequisites:
- You will need 2010 Office Data Connectivity Components installed on your SharePoint 2007 / Excel Services machine.
- Do not forget to go to Central Admin -> Shared Services -> Trusted Data Providers -> add it up there as Provider ID = "Microsoft.ACE.OLEDB.12.0" and Provider Type = "OLE DB".
- You need to have Excel Services Trusted Location (defined in Central Admin)
We can't use Microsoft.ACE.OLEDB.14.0 directly from Excel, data connection wizard will fail to create Data Link. But we still can create our own Office Data Connection (ODC) file.
Instead of "Provider=Microsoft.ACE.OLEDB.14.0" we have to use "Provider=Microsoft.ACE.OLEDB.12.0". It's a bug and would be probably fixed in release.
Here is the source XML for the sample ODC file. Copy it to your favorite text editor and change <odc:ConnectionString>:
- User ID - not relevant
- Data Source - url to your SharePoint site
- DATABASE - url to your SharePoint site
- LIST - List Title (not Name) or List GUID
- There is no need to change [List] in <odc:CommandText> - it will load data from the list, referenced in LIST anyway
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
<meta name=ProgId content=ODC.Table>
<meta name=SourceType content=OLEDB>
<title>(Default)</title>
<xml id=docprops><o:DocumentProperties
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="http://www.w3.org/TR/REC-html40">
<o:Name>(Default)</o:Name>
</o:DocumentProperties>
</xml><xml id=msodc><odc:OfficeDataConnection
xmlns:odc="urn:schemas-microsoft-com:office:odc"
xmlns="http://www.w3.org/TR/REC-html40">
<odc:Connection odc:Type="OLEDB">
<odc:ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=http://moss.sharepointalist.com/SiteDirectory/ACETest;Mode=Share Deny None;Extended Properties="WSS;HDR=No;IMEX=0;DATABASE=http://moss.sharepointalist.com/SiteDirectory/ACETest;LIST=List Title;RetrieveIDs=Yes";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=0;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False</odc:ConnectionString>
<odc:CommandType>SQL</odc:CommandType>
<odc:CommandText>SELECT * FROM [List]</odc:CommandText>
<odc:AlwaysUseConnectionFile/>
</odc:Connection>
</odc:OfficeDataConnection>
</xml>
<style>
<!--
.ODCDataSource
{
behavior: url(dataconn.htc);
}
-->
</style>
</head>
<body onload='init()' scroll=no leftmargin=0 topmargin=0 rightmargin=0 style='border: 0px'>
<table style='border: solid 1px threedface; height: 100%; width: 100%' cellpadding=0 cellspacing=0 width='100%'>
<tr>
<td id=tdName style='font-family:arial; font-size:medium; padding: 3px; background-color: threedface'>
</td>
<td id=tdTableDropdown style='padding: 3px; background-color: threedface; vertical-align: top; padding-bottom: 3px'>
</td>
</tr>
<tr>
<td id=tdDesc colspan='2' style='border-bottom: 1px threedshadow solid; font-family: Arial; font-size: 1pt; padding: 2px; background-color: threedface'>
</td>
</tr>
<tr>
<td colspan='2' style='height: 100%; padding-bottom: 4px; border-top: 1px threedhighlight solid;'>
<div id='pt' style='height: 100%' class='ODCDataSource'></div>
</td>
</tr>
</table>
<script language='javascript'>
function init() {
var sName, sDescription;
var i, j;
try {
sName = unescape(location.href)
i = sName.lastIndexOf(".")
if (i>=0) { sName = sName.substring(1, i); }
i = sName.lastIndexOf("/")
if (i>=0) { sName = sName.substring(i+1, sName.length); }
document.title = sName;
document.getElementById("tdName").innerText = sName;
sDescription = document.getElementById("docprops").innerHTML;
i = sDescription.indexOf("escription>")
if (i>=0) { j = sDescription.indexOf("escription>", i + 11); }
if (i>=0 && j >= 0) {
j = sDescription.lastIndexOf("</", j);
if (j>=0) {
sDescription = sDescription.substring(i+11, j);
if (sDescription != "") {
document.getElementById("tdDesc").style.fontSize="x-small";
document.getElementById("tdDesc").innerHTML = sDescription;
}
}
}
}
catch(e) {
}
}
</script>
</body>
</html>
- Save file with .odc extension and try opening it in Microsoft Excel. If everything is right, you should get your data loaded in Excel.
- Save your .odc file in Data Connection Library in SharePoint 2007 (MOSS). Approve it, or ask owner of the site (administrator) to approve it. It will still work for you without the approval, but not for the other users.
- Create a new Excel Workbook, under Insert tab select PivotChart (or PivotTable).
- In the "Create PivotTable with PivotChart" dialog select "Use an external data source" and click on "Choose connection button".
- Click "Browse for more" and navigate to your .odc file in SharePoint Data Connection Library.
- Build up your Pivot Chart or Pivot Table.
- When you are done, click on Office Button and select Publish -> Excel Services.
- Browse to your target SharePoint document library.
- Click "Excel Services Options" button and select what items do you want to publish using Excel Services.
- Save the file. Now you should be able to access your pivot table or pivot chart using Excel Web Access Web Part. That charts/table are totally dynamic and will show your updated data when refreshed.
Update (05/07/2010):
After some additional research and help from Ivan Huter looks like described method works with standalone MOSS installation only. You will have to configure Kerberos Authentification in order to make it working in a farm. Unfortunately I do not have time right now to test Kerberos option, but I will post an update when I get a chance.
Let me know if you have any questions or notes.
Hi, I'm looking for something like this you're posting. The thing is that I need to create Pivot tables from lists but I also need to update data from the list, so user's can work on sharepoint and then update data as they modify it on the sharepoint list. Is there a way to do this on Sharepoint 2007 and Excel Services. Thanks in advance and thanks for this solution. Kind regards.
ReplyDeleteThis will work as you expect. When you change your data, Excel Services refresh it in Pivot Table. Just make sure that your pivot table connected to data source directly, and not through query table.
ReplyDeleteIs it possible to adapt this somehow to work in Office 2007?
ReplyDeleteIt is for Office 2007 and SharePoint 2007.
ReplyDeleteI'm the Anonymous that wrote earlier, sorry I forgot to sign my post.
ReplyDeleteIt seems to work fine in my computer, however I have to convince the administrators to install the Office component in the server.
Thanks a lot for sharing this information
Gabriel König
Is there any way to achieve the same end result without having to install the plugin?
ReplyDeleteThanks so much for all your help,
Todd
You can use SharePoint query, but it will not be dynamic - only static snapshot. You can also use UDF, but that one involves custom programming. All the other methods that I am aware of is even more complex.
ReplyDeleteIt seems like the connection works, but when I try to refresh, it sticks at "operation in progress" and then hangs.
ReplyDeleteI followed the steps, but still got the same error:
ReplyDeleteData Refresh Failed
Unable to retrieve external data for the following connections:
NewOrders
The data sources may be unreachable, may not be responding, or may have denied you access.
Verify that data refresh is enabled for the trusted file location and that the workbook data authentication is correctly set.
any ideas? thanks in advance.
Try opening your odc file directly from Microsoft Excel. Check if this one will import the data. If it does not, look into odc file again, make sure you use the right list TITLE (not name), or use list GUID instead.
ReplyDeleteThanks for your quick response Paul!
ReplyDeleteI can open odc file using Excel without problem, the data is shown. It seems every steps above I can follow without error, also checked the 'enable backgroud refresh' checkbox in workbook connection properties. Only the last step after publish, it cannot refresh, and it generated that error when click 'Update --> Refresh all connections'.
Is there anything else to be configured? and how do I check if the ACE componnection is installed correctly? thanks a lot...
Did you look into Prerequisites?:
ReplyDelete•You will need 2010 Office Data Connectivity Components installed on your SharePoint 2007 / Excel Services machine. 64 bit version on 64 bit machine, and 32 bit version on 32 bit machine.
•Do not forget to go to Central Admin -> Shared Services -> Trusted Data Providers -> add it up there as Provider ID = "Microsoft.ACE.OLEDB.12.0" and Provider Type = "OLE DB".
•You need to have Excel Services Trusted Location targeted at the document library you use to store excel spreadsheet (defined in Central Admin)
I did all of that :(
ReplyDeletePaul,
ReplyDeleteI have done all the above prerequisites. and I am using 'windows Authentication' in workbook connection Excel Services - Authentication Settings, changed the list to use GUID in odc file. but still got the same error, anything else I am missing? many thanks.
Please check the SharePoint logs located in C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\LOGS
ReplyDeleteregenerate the error and look for the file with the latest modified date. Let me know if you find any additional info there.
Found database error:
ReplyDeleteUnexpected Unexpected query execution failure, error code 282. Additional error information from SQL Server is included below. "The 'proc_GetTpWebMetaDataAndListMetaData' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead." Query text (if available): "{?=call proc_GetTpWebMetaDataAndListMetaData(?,'93460A66-4CDD-49F8-B870-DD680C8D982F',?,NULL,1,?,?,6187)}"
But the list GUID I put in the odc is '8886F30C-0B9B-4C64-AAAB-26B3318C861C' not '93460A66-4CDD-49F8-B870-DD680C8D982F'
I don't think that is a relevant error. I am not exactly sure why it's not working for you - I can do the live meeting to figure it out - just send me your contact info.
ReplyDeleteThanks for the live meeting Paul! With your help of digging the credential delegation error, I managed to make it work on a standalone vm by running the following commands:
ReplyDeletestsadm -o set-ecssecurity -accessmodel delegation -ssp
stsadm -o execadmsvcjobs
iisreset
Now there is no error when refreshing.
However, each time when I update the list, the pivot table doesn't reflect the latest data even after clicking refresh all connections. If I run iisreset, and refresh the pivot table again, it works. Sorry to take you so much time, I greatly appreciate your help. thanks a lot.
Unable to retrieve external data for the following connections:
ReplyDelete(Default)
The data sources may be unreachable, may not be responding, or may have denied you access.
Verify that data refresh is enabled for the trusted file location and that the workbook data authentication is correctly set.
How do I ensure a secure and valid refresh operation?
Paul, I'm getting the above error as well. I'm able to pull the data into the workbook and refresh, but when published it cannot update the data. Any help would be appreciated!
Looks like the following fixes the error, although I can't verify, cause I am not getting this error on my systems.
ReplyDeletestsadm -o set-ecssecurity -accessmodel delegation -ssp
stsadm -o execadmsvcjobs
iisreset
Also please comment if that is 32 or 64 bit drivers that you are experiencing problem with.
Paul, I no longer get that error, but it now hangs when it goes to refresh the connection. I'm running 64-bit with no Service Pack upgrades. I'm considering using Dundas for Charting...
ReplyDeleteTo complete this process you need to make sure you have workbook Authentication set to NONE then republish your ODC file. And to make sure the workbook updates on command when viewed in the browser you need to set the connection lifetime under Excel service settings to only a couple seconds. Then after you update your list you can choose refresh all connection on the workbook while viewing in browser and it will update.
ReplyDeleteA couple notes:
ReplyDelete1. Authentication will be the same as any other external data source. Windows Authentication should work by default in the standalone installation. If you've done a complete/farm installation, Windows Authentication will require changing the access model to delegation, as well configuring Kerberos if the WFE and ECS are on different machines. The easiest way to rule out authentication issues is to set workbook authentication to None as the previous comment says, and then configure the unattended account in Excel Services global settings in SharePoint Central Administration.
2. Also, as the previous comment suggests, you need to change other Excel Services External Data settings if you want timely refreshes. The provider does not appear to work with connection pooling. Therefore, you need to change Connection Lifetime to 0 (or a very low number). You will also want to change External Data Cache Timeouts for the trusted location that hosts your Excel workbook. Otherwise, your data will be cached for 5 minutes by default, and it will appear that you are not getting updated data. Be careful making these changes in a production environment, as there can obviously be performance implications for removing these caching and pooling layers.
Very interesting article!
Hi
ReplyDeleteI do all of these but still i get the error.
"The data connection uses None as the external data authentication method and Unattended Service Account has not been configured on Excel Services."
Have you configured the unattended account?
ReplyDeletehttp://support.microsoft.com/kb/928738
hi
ReplyDeleteyes i config the unattended account but i still have this error.I'm using sharepoint server 2010 and excel 2010.
This was not tested yet on SharePoint 2010. I will try to test it out next week.
ReplyDeleteI've used it on SharePoint 2010. Are you able to refresh other workbooks with external data connections that use "None" authentication method? If so, your best bet is to look at the ULS logs.
ReplyDeleteUnattended account in SharePoint 2010 uses Secure Store and requires a few more configuration steps.
Hi Paul,
ReplyDeleteDo you know of any Best Practices or recommendations for setting the "Maxmum Sessions Per User" found under Central Admin -> Shared Services -> edit Excel Services Settings. We are encountering the "Maximum Sessions..." error on some of our dashboards. But we don't want to set it the Max. Sessions to a number that would cause performance issues.
Paul, I got it working in a farm environment with Kerberos.
ReplyDeleteThank you for your article!
First of all, Thanks Paul! If I can get this to work I will be VERY happy.
ReplyDeleteI am also having issues with data refresh and use Sharepoint Server 2010.
Does anyone have a good guide on how to configure an Unattended Service Account for use with Excel Services? I am getting really confused about the process there.
Also, how do I set the workbook authentication to None as David has instructed? Is this done in the .odc file's connection string?
Once I complete these 2 steps, should I just start from the top again?
again, thanks so much guys!
So I think i figured out how to set the Authentication to None in Excel 2010. (opened the original .odc file, saw the data loaded, when to Data-> Connections and modified the connection to None and then exported as a new .odc file) I also think I set up the Unattended Services Account right (with the help of this document: http://technet.microsoft.com/en-us/library/ff191191.aspx). I followed the rest of the steps to get a new workbook in my library using the new .odc file.
ReplyDeleteNow the problem I am experiencing is that when I view the workbook and try to Refresh Connections, the browser seems to just load indefinitely. Any ideas on how I can debug or look further into this problem?
It looks as if this thread has gone cold because the solution did not really work for other people. I have tried to follow the instructions as presented and also the additional tweaks from the comments. The result has been the same as reported by others: the worksheet loads with static data, but will not refresh successfully. Unfortunately this is the main attraction of this approach, so it is very disappointing.
ReplyDeleteTested and works with Office 2013 and SharePoint 2013. Now, thanks to that specially crafted ODC file I can have an Excel dashboard embedded into an OneNote page that retrieves data from a SharePoint list. Nice!
ReplyDelete