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.
