Sharepoint 2010: Aggregate Tasks across sites

Question

We have a Site Collection with several Team Sites. Each Team site has a task list.

We would like to show an aggregated list of tasks in all team sites on the root site. What is the best way to do this?

Answer 1

You can use SPSiteDataQuery to query all lists across all webs of a collection. You can query for a specific type of list by passing a ServerTemplate value to the SPSiteDataQuery.Lists property. The ServerTemplate ID for Tasks is 107. Querying all webs is accomplished by passing the "SiteCollection" value to the SPSiteDataQuery.Webs property.

SPSiteDataQuery returns a DataTable with very limited information (only list and item IDs I think) so you have to specify all extra fields in the SPSiteDataQuery.ViewFields property.

The following code (adapted from the class documentation sample) will return all Tasks in progress, order by their start date descending:

SPWeb web = SPContext.Current.Web;
SPSiteDataQuery query = new SPSiteDataQuery();

//Ask for all lists created from the tasks template.
query.Lists = "<Lists ServerTemplate=\"107\" />";

// Get the Title field.
query.ViewFields = "<FieldRef Name=\"Title\" />" ;
// Set the criteria and sort order.
query.Query = "<Where>" +
                       "<Eq>" +
                             "<FieldRef Name='Status'>" + 
                             "<Value Type='Choice'>In Progress</Value>" +
                       "</Eq>" +
                   "</Where>" +
                   "<OrderBy>" +
                       "<FieldRef Name=\"StartDate\" Ascending='False'/>" + 
                   "</OrderBy>";

// Query all Web sites in this site collection.
query.Webs = "<Webs Scope=\"SiteCollection\" />";

DataTable dt = web.GetSiteData(query);