When working with SharePoint list data in Microsoft Excel, it is often necessary to report based on user data (e.g. name, department, job role, etc.) and most of the time this information isn’t immediately available as User fields only store the user’s unique ID and extended metadata is not pulled in from SharePoint automatically.
This article assumes you have connected your Excel workbook to one or more SharePoint lists and already have some list data available.
NOTE: When using the following instructions in Excel, you must use the ODATA FEED option – as attempting to use ‘Get Data, From Other Sources, From SharePoint List’ on the DATA tab of Excel’s ribbon menu seems to fail!
So, we need to add the user data feed using an OData connection and this can be done from the DATA tab of the ribbon menu by selecting: Get Data, From Other Sources, From OData Feed.
On the ‘OData Feed’ configuration window that is displayed, provide the URL to the UserInformationList list instance you need to retrieve your user data from (i.e. the same site the existing feeds are coming from). The URL should look something like this:
http://site-url/_vti_bin/listdata.svc/UserInformationListCode language: plaintext (plaintext)You can also filter the results to just people objects, like this:
http://site-url/_vti_bin/listdata.svc/UserInformationList?$filter=substringof('Person',ContentType) eq trueCode language: plaintext (plaintext)It is a good idea to use ‘Load To…’ when setting up the feed, and then only creating a connection to the data source (i.e. don’t load it to a sheet in the workbook). This saves unnecessary data being held/displayed in the workbook file and makes it easier to develop a single cohesive data model for reporting. Instead of clicking the Load button on the OData Feed window, click the caret (down arrow) just to the right of it, then choose ‘Load To…’, choose the ‘Only Create Connection’ option, and then click OK.
Once you have connected to your user data source, you can do a merge of your existing list data and the user information list feed so that user data can be resolved per list item in your core list content.
On the merge configuration window, select the list you want to add user data to in the top box (Tasks in this example), and then choose the column containing the user ID you want to resolve further information about (e.g. AuthorId). In the second drop down, choose your user data source and then choose the field (e.g. Id) that links the data from the first list to the user information list data. Choose a Left Outer Join so that all the values from the first list are always included (and the matching record from the user data or a null from the other list).
You’ll now have a merged query in your Excel workbook that provides task details and resolves AuthorId to a Name, Account Name, Department, Job Title, etc.
If you have more than one column containing user IDs (like AuthorId and EditorId above) then you’ll need to do a second merge using the first merge as the entity in the top drop-down and your user information list feed as the one in the second drop-down box. Everything else will be as before.
Once you’ve finished merging, add the last merge to the workbook’s data model (via the LoadTo… option on the context menu for the query in the Queries & Connections pane) so that the data can be reported on in pivot tables, charts, etc.