How to cross site list rollup in SharePoint 2010 custom code

Posted on 2011-10-12
Last Modified: 2012-05-12
We are writing a custom web part to perform a cross-site list roll-up of data across a single collection. All of the sites (and sub-sites) are part of one single collection. Our goal is to have data rolled-up or aggregated to a top-level team site from its child sub-sites.

If there is an easier way then please let me know, but here are the facts as I see them in SP2010:

1. DataView Web Parts & Linked Data Sources work for content within the SAME site NOT cross-sites

2. Content Query Web Part to query across sites but need to modify XML to show the needed values because the default display is only the Title field

3. Create a lookup to a cross-site list and display inside a new custom list using a Site Column assignment

4. SP Designer will NOT work as a development app for this solution

5. Visual Studio will work as a dev app for this solution

6. We can purchase a 3rd party app for cross-site list lookup/roll-up data

7. We can create our own custom code web part using Client Object Model

Of all these choices, I believe the "best" solution is to write custom code web part to give the easiest and most flexible results. Please tell me your opinion!

My attempts to create this custom code web part are as follows:

1. Query lists from the collection using SPSite, SPWeb and SPSiteDataQuery

2. Create a DataTable and retrieve the data using csWeb.GetSiteData(csDataQuery)

3. Add columns to the data table

4. Iterate through the rows of the data table adding field values

5. Then DataBind the values to the data table

Soooooo .... I've attached a code sample detailing my Visual Web Part and code behind. The web part errors out when added to a page with an "unexpected error".  Please let me know what I am doing wrong in the code. In this example, I am trying to return Announcements on sub-sites to display as a custom rollup on a parent TeamSite. Since Announcements use a SPListTemplateType of number 104 it is used inside the CAML. I have used examples from Vijai Anand on C-SharpCorner at URL which you may reference with this link.

In the end my question has several parts:

1. Is my analysis of cross-site list rollup and SharePoint capabilities correct?

2. Have I chosen the right solution to this problem (if not what do you recommend)?

3. Please analyze my code to flush out mistakes

Thank you

using (SPSite csSite = new SPSite("http://myrootsite/"))
                using (SPWeb csWeb = csSite.OpenWeb())
                    SPSiteDataQuery csDataQuery = new SPSiteDataQuery();
                    csDataQuery.Webs = "<Webs Scope=\"SiteCollection\">";
                    csDataQuery.Lists = "<Lists ServerTemplate=\"104\" />";
                    csDataQuery.ViewFields = "<FieldRef Name=\"Title\" />";
                    string where = "<Where><Eq>";
                    where += "<FieldRef Name=\"Body\" />";
                    where += "<Value Type=\"Text\" />";
                    where += "</Eq></Where>";

                    csDataQuery.Query = where;
                    DataTable dt = csWeb.GetSiteData(csDataQuery);
                    DataView dv = new DataView(dt);

                    foreach (DataRow csRow in dt.Rows)
                        DataRow dr = dt.NewRow();
                        dr["ListName"] = csRow.Title;
                        dr["ListType"] = csRow.BaseTemplate.ToString();
                        dr["CreatedOn"] = csRow.Created.ToString("dd/MM/yyyy");
                        dr["Items"] = csRow.Items.Count;
                    gvResult.DataSource = dv;
                    gvResult.Visible = true;

Open in new window

Question by:sr2007
    LVL 14

    Expert Comment

    If the data set is large, another option is to leverage search query for the roll-up.
    This should help:

    Author Comment

    The data set is NOT considered large. It is a relatively small number ~approx 5-20 items.

    Author Comment

    Help ... any other ideas?
    LVL 14

    Expert Comment

    Well, for those many rows, your code should work out fine.

    This should help in validating your approach:

    Author Comment

    my results are not correct. The grid is created and the headers are created, but the data row fields are not being populated.
    LVL 14

    Accepted Solution

    I would guess the CAML query to be the reason.
     string where = "<Where><Eq>";
                        where += "<FieldRef Name=\"Body\" />";
                        where += "<Value Type=\"Text\" />";
                        where += "</Eq></Where>";

    What exactly are you trying to filter? Your value filed is empty? look at this link for an example:

    Author Closing Comment

    Thank you.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Work Over Net is a new and very powerful collaboration product. With its new easy interface it is becoming very competitive to other similar products like webex and office interactive. WON 2010 have the standard business tools needed for multi-offic…
    When installing SharePoint 2010 RTM I came across a strange error, I was getting timeouts during the installation. I searched the web and found the best solution to be found here (…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now