This article was originally written in April 2012. It is being reposted here as an archive.
In SharePoint 2007, the Data View Web part (DVWP) was all the rage. You could create complex views from multiple sites within in your site collection or other data sources and group, sort and format the data in a visually appealing and meaningful way.
SharePoint Designer 2007 made it easy for you to connect multiple lists on sub-sites with a handy little “Connect to another library…” link at the bottom of the data sources task pane. With all the improvements Microsoft has made with the Content Query Web Part, I was excited to use the DVWP as a sort of expanded version of information that I show in a CQWP. However, SharePoint Designer 2010 has gotten rid of the task pane and the handy little link that allows you to quickly connect data sources to display in the DVWP. Out of the box you can see you site’s document libraries and lists in the data sources.
So the question is, how do you combine data from multiple sub-sites to display in a consolidated list? For this example, I have 8 departments and each department has a sub-site. Each of these sub-sites has a “Goals” list that uses a global content type in a custom task list. I want to consolidate each department’s goals on the parent site to display a view the company-wide goals.
Inbar Cizar (@Cizi) does an excellent job of explaining how to add a sub-site’s list to your list of data sources in this post: http://blogs.microsoft.co.il/blogs/cizi/archive/2010/08/01/connect-to-another-library-in-sharepoint-designer-2010.aspx
Once you have added each of your department’s goals as a data source, you will need to create a “Linked Data Sources” and use this Linked Data Source in your Data View Web Part
Then open an existing or create a new page (I used an .aspx page).
On the ribbon, click on the down arrow on the Data View button and select your Linked Data Source.
From there you can add your columns, group, sort and modify the DVWP as needed.
Name your SOAP datasources carefully; you can use the name of the connection to group or sort a DVWP by the name of the sub-site, or whatever you call it. After the datasources have been connected as a Linked Source, any name changes are not reflected, so you will need to create a new Linked Source to reflect a name change.
Here are instructions to group the DVWP by the site name, which is what I named the SOAP Service datasource.
- Call your “SOAP Services” data source the name of your sub-site.
- Click on sort and Group
- Select “Add Sort Expression…”
- Select “agg:source”