?
Solved

How to cross site list rollup in SharePoint 2010 custom code

Posted on 2011-10-12
7
Medium Priority
?
1,360 Views
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 http://www.c-sharpcorner.com/uploadfile/anavijai/6336/?login=true&user=alexdove@comcast.net#ReadAndPostComment 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);
                    dt.Columns.Add("ListName");
                    dt.Columns.Add("ListType");
                    dt.Columns.Add("CreatedOn");
                    dt.Columns.Add("Items");

                    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;
                        dt.Rows.Add(dr);
                    }
                    gvResult.DataSource = dv;
                    gvResult.DataBind();
                    gvResult.Visible = true;
                }
            }

Open in new window

0
Comment
Question by:sr2007
  • 4
  • 3
7 Comments
 
LVL 14

Expert Comment

by:abhitrig
ID: 36956020
If the data set is large, another option is to leverage search query for the roll-up.
This should help: http://www.sptechcon.com/SanFrancisco2011/PDFs/SFCOURSEMATERIALS/108BECKETT.pptx
0
 

Author Comment

by:sr2007
ID: 36956199
The data set is NOT considered large. It is a relatively small number ~approx 5-20 items.
0
 

Author Comment

by:sr2007
ID: 36957184
Help ... any other ideas?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 14

Expert Comment

by:abhitrig
ID: 36957490
Well, for those many rows, your code should work out fine.

This should help in validating your approach:
http://blog.mastykarz.nl/performance-of-content-aggregation-queries-on-multiple-lists/
0
 

Author Comment

by:sr2007
ID: 36965657
my results are not correct. The grid is created and the headers are created, but the data row fields are not being populated.
0
 
LVL 14

Accepted Solution

by:
abhitrig earned 2000 total points
ID: 36968817
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:

http://darrinbishop.com/blog/2007/04/spsitedataquery-for-cross-site-and-cross-list-searches
0
 

Author Closing Comment

by:sr2007
ID: 37073186
Thank you.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Summary In SharePoint 2010 it is easy to create custom color themes to jazz up a site. Theme colors can also be created in PowerPoint 2010 with a few clicks. But how do the chosen colors actually look in the SharePoint site? The attached PowerPoint…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Integration Management Part 2
Suggested Courses

850 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