The PowerPivot team just posted a white paper on Using SharePoint List Data in PowerPivot, which is awesome. Now I can slice, dice, and analyze the contents of a document library I manage.
Except… PowerPivot doesn’t pull every column from the library. The instructions basically involve connecting to the listdata service (which works fine) for a particular list (via a URL like http://sharepoint/sites/mysite/_vti_bin/listdata.svc/IPLibrary). Looking at the raw XML that is returned, not all columns are provided by SharePoint that are really present in the list (I’ll put a snapshot of the returned XML at the end of this post). The missing columns seem to be either multivalue columns or contain managed metadata.
Is there any way to make the list data service return such columns?
Sample entry in the output XML:
<entry m:etag="W/"4"">
<id>http://sharepoint/sites/mysite/_vti_bin/listdata.svc/IPLibrary(309)</id>
<title type="text">Introduction to System Center Configuration Manager 2007</title>
<updated>2011-03-14T17:34:31-07:00</updated>
<author>
<name />
</author>
<link m:etag=""{5D7F8D04-7350-4FCD-9953-11432E94D28C},4"" rel="edit-media" title="IPLibraryItem" href="IPLibrary(309)/$value" />
<link rel="edit" title="IPLibraryItem" href="IPLibrary(309)" />
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/IPType" type="application/atom+xml;type=entry" title="IPType" href="IPLibrary(309)/IPType" />
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/DocumentStatus" type="application/atom+xml;type=entry" title="DocumentStatus" href="IPLibrary(309)/DocumentStatus" />
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/IPConfidentiality" type="application/atom+xml;type=entry" title="IPConfidentiality" href="IPLibrary(309)/IPConfidentiality" />
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Language" type="application/atom+xml;type=entry" title="Language" href="IPLibrary(309)/Language" />
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/CreatedBy" type="application/atom+xml;type=entry" title="CreatedBy" href="IPLibrary(309)/CreatedBy" />
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/ModifiedBy" type="application/atom+xml;type=entry" title="ModifiedBy" href="IPLibrary(309)/ModifiedBy" />
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/CheckedOutTo" type="application/atom+xml;type=entry" title="CheckedOutTo" href="IPLibrary(309)/CheckedOutTo" />
<category term="Microsoft.SharePoint.DataService.IPLibraryItem" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
<content type="application/octetstream" src="http://sharepoint/sites/mysite/Library/Introduction%20to%20System%20Center%20Configuration%20Manager%202007.PPT" />
<m:properties xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices">
<d:ContentTypeID>0x0101009D2D2256AF1CDC4289EE8ED041F0FAB9000DF4A84F48DB1349AA3026BAC0A8AF27</d:ContentTypeID>
<d:Name>Introduction to System Center Configuration Manager 2007.PPT</d:Name>
<d:Title>Introduction to System Center Configuration Manager 2007</d:Title>
<d:DocumentIDValue>GPSDOCID-51-108</d:DocumentIDValue>
<d:DocumentID>http://sharepoint/sites/mysite/_layouts/DocIdRedir.aspx?ID=GPSDOCID-51-108, GPSDOCID-51-108</d:DocumentID>
<d:IPDescription>This presentation discusses the new features of System Center Configurationion Manager 2007</d:IPDescription>
<d:IPTypeValue>Community IP</d:IPTypeValue>
<d:DocumentStatusValue>Active</d:DocumentStatusValue>
<d:IPConfidentialityValue>Partner Ready</d:IPConfidentialityValue>
<d:URL m:null="true"></d:URL>
<d:Description m:null="true"></d:Description>
<d:BillableHours m:type="Edm.Double" m:null="true"></d:BillableHours>
<d:LanguageValue>English</d:LanguageValue>
<d:Id m:type="Edm.Int32">309</d:Id>
<d:ContentType>GPS IP Document</d:ContentType>
<d:Created m:type="Edm.DateTime">2010-06-21T09:53:27</d:Created>
<d:CreatedById m:type="Edm.Int32">1068</d:CreatedById>
<d:Modified m:type="Edm.DateTime">2011-03-14T17:34:31</d:Modified>
<d:ModifiedById m:type="Edm.Int32">1</d:ModifiedById>
<d:CopySource m:null="true"></d:CopySource>
<d:ApprovalStatus>3</d:ApprovalStatus>
<d:Path>/sites/mysite/Library</d:Path>
<d:CheckedOutToId m:type="Edm.Int32" m:null="true"></d:CheckedOutToId>
<d:VirusStatus>3271680</d:VirusStatus>
<d:IsCurrentVersion m:type="Edm.Boolean">true</d:IsCurrentVersion>
<d:Owshiddenversion m:type="Edm.Int32">4</d:Owshiddenversion>
<d:Version>1.1</d:Version>
</m:properties>
</entry>