Aggregating Data From Different SharePoint List Types
The Content Query web part is a great tool for rolling up data from different SharePoint lists. One of its limitations is that is does not aggregate list items across different list types. Here’s a scenario I recently came across…
We were building a knowledge repository that contained documents in a document library and links in a link list. Both lists have the same custom metadata, as each item represents information about a specific topic. Our objective was to create one interface that grouped the combined data across various metadata filters. That is, for a specific Category we want to show the documents AND links that match… in one list. How?
Step 1: Fire up a Data View web part (DVWP). You’ll do this through SharePoint Designer.
Step 2: Create a new Linked Source that combines the document library and link list. Here’s a good resource for how-to.
Step 3: Display the linked data fields in the DVWP.
Step 4: Now we’re close! The one challenge we have is that although both lists share common custom metadata, documents and links do not have the same main metadata (i.e. documents have a FileRef property; links have a URL property). If we simply drag and drop into the DVWP, each will show data in their respective column. To combine this information into one field we’ll use a little XSL magic…
<xsl:choose>
<xsl:when test="string-length(@FileRef) > 0">
<a href="{@FileRef}"><xsl:value-of select="@Title" /></a>
</xsl:when>
<xsl:otherwise>
<a href="{substring-before(@URL, ', ')}"><xsl:value-of select="substring-after(@URL, ', ')" /></a>
</xsl:otherwise>
</xsl:choose>
Now we have one interface for our documents and links and our hyperlinks bring us to the right location. Very cool!
Thanks to my colleague Marc Anderson, resident XSL guru, for the right syntax…