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.