Recently, I had a need to create a security-trimmed rollup view of list items from multiple sub sites. The specific scenario looked like this:
I wanted to see the latest Status Report from each site displayed on the Root Site. This was the desired end result:
Since this was being built on Office365 SharePoint Online (SPO), 3rd party web parts were not an option since none of them work on SPO, nor was creating a custom Web Part as the part of the OM needed to provide security trimming is not accessible on SPO. The only option was to try to use a Data View Web Part (DVWP) to create the desired result.
It turns out there is very little you can’t display with a DVWP if you are willing to enter the world of CAML queries, XSL tags and XPATH expressions; a world not for the faint of heart. However, I was convinced the solution to this problem was a Data View Web Part, and so my journey into the world of CAML, XSL and XPATH began.
My first stop was on Marc D. Anderson’s website where I purchased his eBook Unlocking the Mysteries of the SharePoint Data View Web Part XSL Tags – I highly recommend adding this to your reference library. Next, I created an Office 365 SharePoint Online site collection . . . . . I added a few sub sites (HR, Legal and Finance) . . . . . . .created a custom list on the Root site and each sub site called StatusReports (with two columns – WeekEnding of type DateTime and Discipline of type Text) . . . . and then added some sample items to each list. Basically, I created what I described in Figure 1 above.
Now here are the steps to make this all work.
- Open the Root site in SharePoint Designer (SPD).
- In the Design-mode window, place the cursor in the location where you want the new DVWP.
In the Ribbon, go to the Insert tab, select the Data View dropdown and select Empty Data View from the list (see screen shot below). If you select the StatusReports instead, SPD will insert a List View Web Part which doesn’t provide the flexibility we need.
*A caveat here is that we need to have a List on the Root site to create the initial DVWP. In the case where we don’t want a List on the Root site, after we complete configuring the DVWP, we can delete the item(s) in the Root site List and then use SPD to hide the List. DO NOT DELETE THE LIST AS IT WILL DELETE THE DVWP AS WELL.
- In the newly inserted data view click on the link labeled Click here to select a data source and then choose the StatusReports list.
- You should now see a new pane appear on the right of the screen displaying the StatusReports Data Source and all of its fields. Select the WeekEnding, then, at the top of the Data Source pane, select the Insert selected fields as… dropdown and choose Multiple Item View (see screen shot below).
- At this point, the Ribbon commands for this data view are active so we can start using them to add parameters as well as setup our Sort & Group. Click on Add/Remove Columns in the Ribbon. This will launch the Edit Columns wizard which you can use to add the Discipline column to our view (see screen shot below).
Your DVWP should look like this so far:
- As a best practice, we should only query the data source for content we will us in creating our view. To do this, we launch the Data Source Properties wizard by clicking the link just below Current Data Source in the Data Source Details pane. In the wizard we’ll click on the Fields button and remove all fields except for WeekEnding, Discipline, ID and Path (see screen shot below).
Your Data Source Details pane should look like this:
- So far, our DVWP is only pulling info from the Root site List. We want it to pull info from the same List that is on each sub site. In order to do this, we have to change the Mode and Select Command of our Data Source Control. The DVWP uses the SPDataSource control so we’ll need to find that control in the code view and modify the DataSourceMode property and the selectcommand statement. We’ll need to change the DataSourceMode from List to CrossList and we’ll need to add this tag – <Webs Scope="Recursive"></Webs> – to the selectcommand statement just after the View opening tag. It should look like this:
- Click in the design mode window to apply your changes and you should see your DVWP display all the items you’ve entered in the Lists contained in the sub sites – similar to this:
Now we need to figure out how to only display the newest status report from each list. By default, the sort order is by List Item ID. For our needs, we’ll first sort our data set by site (URL) and then by WeekEnding. This will essentially group the items by List and then order them by date, assuring we’ll always have then ordered correctly. After we get the data set sorted correctly, we can use an extremely useful function that is part of the ddwrt namespace called NameChanged to select the newest List Item for our view. It will make more sense after you see the code. Let’s get started.
- We can setup our sorting preference by using the Sort & Group command on the Ribbon. This will launch the Sort and Group wizard. We’ll want to add FileDirRef as our first sort order (Ascending is fine) and then add WeekEnding as our second sort order (choose Descending so the newest item is at the top).
This is what my DVWP is displaying after I apply my changes:
- Now we’ll go back to code view and look for the XSL template called dvt_1.rowview. This is the template used to select what will be displayed in the DVWP. We can add an if statement to this template so it only selects the first item from each list to display. Here is a screen shot of the code I added to the template.
Apply the changes and the DVWP should now look like this:
So what is going on here? Well, first we need to understand what this template is doing. The template is passed a row (List Item) and usually just displays whatever is in the value-of select statement from the row. In our case, we only want it do that when the row is the first row (Item) in each List from our sorted row set. We can do this by using the NameChanged function to compare the path string (@FileDirRef) to the value we are using to sort our data (in this case, FileDirRef – represented by the 0). FileDirRef is the path of the List Item, which will be the same for each Item in the same List. The first time this function is called, @FileDirRef is an empty string so the FileDirRef it reads in the first Item will be new and the function will return that value, which is greater than 0. This will allow the rest of the statements to execute causing the current row to be selected. The next row the function iterates through will only be displayed when the FileDirRef changes. You can read more about this function here – http://msdn.microsoft.com/en-us/library/dd583143.aspx#officesharepointddwrt_namechanged.
- The final step is to clean up our view so it doesn’t contain the Time in the WeekEnding field. We can use the substring function to do that. I also want to display the field as a link to the Display Item Form, so I’ll add that code as well. Here is what the cleaned up code looks like:
I can also apply some styling, such as bolding the column headings (do this in design mode using the Ribbon commands). Here is what my final DVWP looks like after I removed the Root site List item.
P.S. You’ll notice I created another rollup view of the Issues List from each site. The difference is that this time I wanted all items displayed and grouped by Discipline.