Aggregating Lists in SharePoint

Friday, November 7, 2014

Another post that could be subtitled “How to do Things in SharePoint By Hand”. This time, combining data sources – lists, in this case, but the same can be applied to other source types, such as SOAP (SoapDataSource) or REST (XmlUrlDataSource) web services, hierarchies of sites (SPHierarchyDataSourceControl), calendar (SPCalendarDataSource), XML files (SPXmlDataSource), BCS external lists (BdcDataSource) or even SQL queries (SPSqlDataSource).

We will query two lists, using an SPDataSource for each, and then we’ll combine the results with an AggregateDataSource and finally we’ll display the results using DataFormWebPart and XSLT.

Enough talk, here is the code:

<WebPartPages:DataFormWebPart runat="server" ID="data">

    <DataSources>

        <SharePoint:AggregateDataSource runat="server" ID="aggregates" SeparateRoot="True">

            <Sources>

                <SharePoint:SPDataSource runat="server" ID="tasks" DataSourceMode="List" UseInternalName="True" UseServerDataFormat="True" SelectCommand="&lt;View><Query></Query></View>">

                    <SelectParameters>

                        <WebPartPages:DataFormParameter Name="ListName" ParameterKey="ListName" PropertyName="ParameterValues" DefaultValue="Tasks"/>

                    </SelectParameters>

                    </SharePoint:SPDataSource>

                <SharePoint:SPDataSource runat="server" ID="pages" DataSourceMode="List" UseInternalName="True" UseServerDataFormat="True" SelectCommand="&lt;View><Query></Query></View>">

                    <SelectParameters>

                        <WebPartPages:DataFormParameter Name="ListName" ParameterKey="ListName" PropertyName="ParameterValues" DefaultValue="Pages"/>

                    </SelectParameters>

                </SharePoint:SPDataSource>

            </Sources>

            <Aggregate>

                <concat name="data source">

                    <datasource name="Tasks" id="0" type="SPList"/>

                    <datasource name="Pages" id="1" type="SPList"/>

                </concat>

            </Aggregate>

        </SharePoint:AggregateDataSource>               

    </DataSources>              

    <Xsl>            

        <xsl:stylesheet xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal" xmlns:agg="http://schemas.microsoft.com/sharepoint/aggregatesource">

              <xsl:output method="html" indent="no"/>                  

              <xsl:template match="/">                                

                      <xsl:for-each select="/dsQueryResponse/Tasks/Rows/Row">

                          <xsl:variable name="AuthorID" select="@Author.id"/>

                          <p>Task Title: <xsl:value-of select="@Title"/></p>

                          <p>Pages of same author: <xsl:value-of select="count(/dsQueryResponse/Pages/Rows/Row[@Author.id = $AuthorID])"/></p>

                      </xsl:for-each>

                  </xsl:template>                    

        </xsl:stylesheet>

    </Xsl>

</WebPartPages:DataFormWebPart>

Noteworthy:

If you specify the SeparateRoot parameter as true, in the XSL section, you will get two data sources, one for each data source specified as a concat/datasource entry, you will have to access them separately, using syntax /dsQueryResponse/Tasks/Rows/Row, of course, replacing Tasks for the right name, as specified in the concat/datasource’s name attribute;
If you don’t specify a value for SeparateRoot, the default is false, which means that the values for both data sources will come together in /dsQueryResponse/Rows/Row; you can sort out what records belong to one list or the other using the syntax /dsQueryResponse/Rows[@agg:source='Pages']/Row; this is made possible by the xmlns:agg="http://schemas.microsoft.com/sharepoint/aggregatesource" entry in the xsl:stylesheet declaration, which I’m sure you hadn’t noticed ;
I am using DataFormParameters to specify the source for the SPDataSources as a list name, instead of a list id. This makes it more portable;
Although I am not filtering the lists, you probably should, there are several ways to do this, which usually include using the SelectCommand together with SelectParameters;
In the XSLT, I am iterating through the first list, and for each item, I get one field value (Author = Creation User) and use it to filter the second list. This is the same that SharePoint Designer does when you insert a linked list data source.