Rollup Data View Web Parts Revisited
I posted back in February about using the Data View Web Part to do cross list rollups (See Rolling Up Content in SharePoint Using the Data View Web Part (DVWP)). Since then, I’ve done quite a few of these, and I’ve been meaning for some time to post this update. Not everything that I believed to be true in that earlier post is right all of the time.
It turns out that the CAML is the key to success with this type of DVWP. What it amounts to is something like the following in the selectcommand. (Note that you cannot have any of the white space or comments I show below to describe it if you want it to work!). This is from a client example, but you should be able to get the gist of it for your situation.
<View>
// This says to grab from all child webs recursively.
<Webs Scope='Recursive'></Webs>
// ServerTemplate 100 is 'General List', i.e., Custom Lists.
// See the 'Type' attribute on the page at http://msdn.microsoft.com/en-us/library/ms462947.aspx
// for descriptions of the various values that are available.
<Lists ServerTemplate='100'></Lists>
<View>
<Query>
<Where>
<Eq>
// Only grab items which have ContentType of 'Finding'. This isn't required,
// but if you do not make it explicit, you will also get any items
// which have all of the columns below in the <ViewFields>.
<FieldRef Name='ContentType'/><Value Type='Text'>Finding</Value>
</Eq>
</Where>
</Query>
// In a CrossList Data Source, the <ViewFields> are what matters
// when it comes to specifying columns.
// <DataFields> has no effect whatsoever, and can even be removed.
<ViewFields>
<FieldRef Name='ID'/>
<FieldRef Name='Title'/>
<FieldRef Name='AuditTitleCT'/>
<FieldRef Name='AuditorCT'/>
<FieldRef Name='CompleteDateCT'/>
<FieldRef Name='RiskCT'/>
<FieldRef Name='FileDirRef'/>
</ViewFields>
</View>
</View>
The DataSources section will end up looking something like below:
<DataSources><SharePoint:SPDataSource runat="server" DataSourceMode="CrossList" UseInternalName="true" selectcommand="<View><Webs Scope="Recursive"></Webs><Lists ServerTemplate="100"></Lists><View><Query><Where><Eq><FieldRef Name="ContentType"/><Value Type="Text">Finding</Value></Eq></Where></Query><ViewFields><FieldRef Name="ID"/><FieldRef Name="Title"/><FieldRef Name="AuditTitleCT"/><FieldRef Name="AuditorCT"/><FieldRef Name="CompleteDateCT"/><FieldRef Name="RiskCT"/><FieldRef Name="FileDirRef"/></ViewFields></View></View>" id="dataformwebpart2"></SharePoint:SPDataSource></DataSources>
One other thing to note: It didn’t make one bit of difference in my testing what the WebURL was set to or even if it was there. All items in the current Web (Site) and any below it in the Site Collection topology are returned. You could obviously control this further by either adapting your topology or adding more constraints in your Where clause in the CAML.
Note added 23 September 2008: When you add your columns to the ViewFields section, you may see that the first underscore character is encoded as _x005F_. In other words, a column named Expiration Date, which would normally have the internal name of @Expiration_x0020_Date is shown as @Expiration_x005F_x0020_Date. You actually will need to change the column references in your XML to reflect this oddity.
HI MARC
I am working on Creating Webpart for Content Rollup using DVWP and i Follow your Link
I have Created Datasource as per below
<Webs Scope='Recursive'
Project Team Member
but it does not return any Data even though i have data in it.
can you please advise whats wrong with this
Thanks
Unfortunately your code didn’t come through on your comment. (It’s a WordPress thing.) Can you try to send me your code through the Contact page?
M.
Hi Marc,
When I add certain fields in the tags, the data view displays “the server returned a non-specific error”
These are all site columns that are part of the site content type. Do you know of any reason why certain columns would cause that error and others don’t?
Thanks for all your contributions to the SharePoint world!
Odds are the you’re just typing something wrong. Make sure that your syntax is correct and the you are using the StaticNames for the columns. also note that your CAML must be escaped; what I show in the post is prettied up to help make it clearer.
M.
Hi Marc,
Have you had any success with paging in a cross-list? I’m returning a large number of items, and the basic paging setting doesn’t seem to do anything at all, even though i know my rowcount variable is returning the correct number of rows. I did notice that if i use RowLimit in the CAML query, that works to return the correct number of rows, but no paging. I’ve also tried RowLimit Paged=”TRUE” in my CAML, but that also does not work.
My view groups items first, if that makes any difference.
Thoughts?
Thanks,
Joe
Joe:
I rarely use the out of the box paging in my DVWPs anyway (I’m not a big fan of what it displays; it rarely makes sense to people), but I don’t think that it will work at all with DataSourceMode=”CrossList”. Under the covers, the retreival method is a bit different. I’d suggest that you build a more sensical paging approach into your DVWP yourself.
M.
Hi Marc,
I have been working on a requirement to roll up contents from subsites to the home page. I am using the DVWP with cross list query with almost same approach as you had detailed in the post.
But, in my case, the lists that I am trying to aggregate data from are huge and the total number of items when aggregated are more than 5000 items. So, I had indexed the columns against which I am querying (In some cases, have created compound indices). But, even after indexing and filtering based on these indexed column, I encounter the issue of the list threshold limit set by the administrator has been breached. The following is the query that I am using:
<Query><Where><Geq><FieldRef Name=’Created’/><Value Type=’DateTime’><Today OffsetDays=’-3’/></Value></Geq></Where></Query>
I also tried to narrow my aggregation to two large lists by using crosslist query with list GUIDs by the following:
DataSourceMode=”CrossList” SelectCommand=”
<Webs Scope=’Recursive’></Webs>
<Lists>
<List ID='{755dd753-a3af-4668-972d-4093cf7a399e}’/>
<List ID='{6C460254-5227-42F3-B837-5FBF6E876BA4}’/>
</Lists>
<Query><Where><Geq><FieldRef Name=’Created’/><Value Type=’DateTime’><Today OffsetDays=’-3’/></Value></Geq></Where></Query>
I had tried to reproduce the issue with some simple large lists. Whenever I am trying to use a CrossList query, the webpart seem to fail. The same webpart, if getting results from one single list with more than 5000 items seems to work fine and the issue seems to be with CrossList query only.
Have you noticed such behaviour? Can you point me in right direction on what I might be doing wrong?
Thanks in advance.
Selvaa
The following is the Lists Structure and the webpart aggregation logic:
Root Site
– Ideas List
Community 1 (Subsite)
– Ideas List
Community 2 (Subsite)
– Ideas List
Community 3 (Subsite)
– Ideas List
Aggregation webpart :
Latest 10 Ideas Webpart in home page (Aggregated from all the ideas list)
Latest 300 Ideas in an Aggregated Ideas page
Hi Marc,
Have you run across a circumstance where a data view that uses the cross list just stop working? I have been using this to roll up to a parent site data from a large amount of subsites. It suddenly stopped working with a generic error. (this is MOSS 2007 if that makes a difference)
Tim:
There were some problems with SharePoint 2010 (I know you said you’re on MOSS) caused by a timeout introduced by Microsoft for XSL transforms. I am not aware of any such limits in MOSS, but that doesn’t mean that they aren’t there. Does it now never work, or is it sporadic? Can you get it to work again by querying for fewer items?
M.
Hi Marc:
Is there a simpler Rollup solution? I’m not a developer and CAML is an animal.
Primarily, from your first post, I cannot locate the “/sitename/subsitename/”… it’s just not there.
Thanks-
Charlie:
The Content Query Web Part (CQWP) does rollups, but the display options are (to me, anyway) truly lacking.
I’m not sure what other post you mean, but something like /sitename/subsitename just looks like my way of giving an example of the path of wherever your subsite lives in the site topology.
M.