Using a Data View Web Part to create a cross-site lists rollup view (Office 365 SharePoint Online and SharePoint 2010)

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 TagsI 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.

  1. Open the Root site in SharePoint Designer (SPD).
  2. In the Design-mode window, place the cursor in the location where you want the new DVWP.
  3. 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.

  4. In the newly inserted data view click on the link labeled Click here to select a data source and then choose the StatusReports list.
  5. 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).

  1. 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:

  1. 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:

  1. 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:

 

  1. 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.

  1. 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:

  1. 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.

  1. 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.

About these ads

About slalomedd

Ed Dumas is a Technical Consultant with Slalom Consulting in Dallas, TX. He focuses on SharePoint-based solutions architecture and development.
This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

31 Responses to Using a Data View Web Part to create a cross-site lists rollup view (Office 365 SharePoint Online and SharePoint 2010)

  1. Sand Lapper says:

    Hi-
    Great post – I was able to get my roll up working without a problem.

    I am running into an issue where the items I place in my roll up aren’t hyperlinked – so, in your example, it looks like Week Ending and Discipline are hyper-linked out to those specific items in the list – is that the case?

    If so – what step might I be missing?

    • slalomedd says:

      Yes, they are hyperlinked to the actual item display form. There are a few different ways to accomplish this and I finally ended up using this code:

      code

      Let me know if you have any other questions.

      Ed

  2. Johanna says:

    Hi, I am having some problems with your post.

    In the part where you put the code in the SelectCommand-statement “<Webs Scope="Recursive"></Webs>” – I can’t see all items I’ve put in my lists. It all goes blank and I can’t see the entire code from your screenshot either.

    The webpart is still there but the item that I had from my root-site is gone and I’ve got a bunch of rowes with no information on them.

    Do you know what my problem is? :)

    • slalomedd says:

      Hi Johanna,

      I’m on a project right now which is keeping me very busy. I’ll send you my email address so you can send me your data form webpart code. I’ll take a look at it this weekend.

      Ed

  3. Johanna Forsberg says:

    Hi Ed,

    I figured it out with the code-part. It was my own fault :)
    But thanks for your reply!

    //Johanna

    • Les Zatony says:

      Johanna – I’ve experienced the same issue. Can you tell me what you did to correct your problem.

      • Johanna Forsberg says:

        @ Les

        For me the problem was that I had to configure the columns after adding the WebPart.

        When you have added the WebPart, you click the name of your List in “Current Data Source”.
        Click “Fields”, and then remove all the Fields that you don’t need.
        If I remember right you need to keep the ID of the list, otherwise SharePoint might freak out.
        After doing this you should see a whole lot more things in your row where the selectcommand starts.

        After that I just copied “<Webs Scope="Recursive"></Webs>” without the ” -marks, and inserted it in selectcommand, right after <View>

        So it should look like this when you inserted Ed’s code:
        <View><Webs Scope="Recursive"></Webs> and then it just worked.

        Let me know if it works :)

        // Johanna

      • Les Zatony says:

        Thanks, I was able to resolve. Now if I could only figure out how to enable dynamic column sorting and filtering (where the user can click on each column heading to either sort or filter).

        Thanks again

        LZ

      • slalomedd says:

        In SPD you should be able to enable sorting & filtering on headers by selecting the web part, click on the “design” tab in the ribbon under “Data View Tools” then check the box labeled “Sort & Filter on Headers”. Save your work and test it in SharePoint.

      • Les Zatony says:

        Thank you. As you can see, I’m quite the noob here with SharePoint. It’s a humbling experience coming to the MS world after many years as a Lotus Notes developer… finding blogs like yours is invaluable to someone like me.

  4. Isha says:

    This is brilliant! Thank you! I’ve been hitting my head all night trying to get the cross site data view web part to work. It was constantly showing blank rows. Blog after blog post danced around this but so many had it wrong/incomplete. And all it turned out was that I’d put the scope string in the wrong place. I put it before the and not after.

  5. Bill says:

    I am trying to do this on a Task list. After I set every thing up and make the changes to the code, a bunch of blank rows pop onto my screen and all of my fields in my data source disappear. I am left with:ID, ListId, and WebId.Any ideas?

    • Bill says:

      Ok, I found the issue but I am hoping you have a better fix than mine. When I take the
      “Assigned to” row out it works. I really need that piece of info. Any way to keep it??

      • slalomedd says:

        @Bill – Assigned To is a tricky column to work with. You may need to edit the XSL to ensure it is using the internal column name “Assigned_x0020_To”. Let me know if that works.

      • nishita says:

        hi, im also facing the same issue with Assigned To field. i have applied sorting and filtering on column headers. but im unable to see lookup column data (actual items) in dropdown of filter. can anybody help me to solve this issue?

  6. Isha says:

    I’ve expanded on this technique a bit with filtering and grouping and conditional formatting but I’ve run into an odd thing with filtering through headers. It seems that if you enable sort and filtering on headers the only items that are visible are the fields from the “placeholder” list. And nothing if there are no items in that list. While you can filter ascending and descending on all items you can’t choose to display one item if that item comes from a child site. The option to do so isn’t there.

    For example I have a column called fruits. In it I have Apples and Oranges in the “placeholder” list at the top level site and Bananas and Pears in two separate lists on two separate child sites. The filter drop down only shows Apples and Oranges as filtering options but no Bananas or Pears.

    Have you run into this?

  7. veeraj says:

    Hi Isha,
    I am having the same problem. not able to filter rows which are from subsites. Have you find any solution for this?
    Or anyone knows how to filter rows from sub site roll up?

  8. Waldemar says:

    Hi Ed,

    Many thanks for your post. It seems that this one has brought me the closest to using the DataForm/ViewWebPart as a roll up mechanism.

    However, I’m not quite there yet. After going through your steps I still get this error (and no data is shonw):

    “The server returned a non-specific error when trying to get data from the data source. ”

    It happens after changing “List” into “CrossList” (the code does accept the bit).

    Do you have a clue where this message could come from?

    Also what I find a bit strange is that the web part remains to be a DataFormWebPart an not a DataViewWebPart, but I suppose that is not really a problem (I did choose “Insert Selected Fields As Multiple Item View”).

    Hope you have a solution.

    Kind regards,
    Waldemar

    • slalomedd says:

      Apologies to all for not replying to comments but I’ve been swamped on a project and will continue to be for the next few months. If I have time I will try to help out on some of the questions.

      @Waldemar – did you add the “webs scope” tag to the selectcommand?

  9. preissuche says:

    Appreciating the time and effort you put into your
    blog and detailed information you provide.

    It’s good to come across a blog every once in a while that isn’t the same out of date rehashed material.
    Excellent read! I’ve bookmarked your site and I’m adding your RSS feeds to my Google account.

  10. kesava says:

    Hi, I changed the source from List to CrossList after that, DvWP stops pulling the data even from the root list. I am trying with Pages library. Can you think what can be the problem

  11. Kirsten says:

    Hi Ed,
    Many thanks for this excellent article!

    We have implemented a DVWP to rollup multiple lists of the same type from a SharePoint 2010 site collection using your additions:
    DataSourceMode=”CrossList” SelectCommand=”<View><Webs Scope="Recursive"></Webs>

    It worked fine until the number of sub-sites exceeded 72 (with 75 sites in Site Collection), when it threw this error:
    “The query cannot be completed because the number of lists in the query exceeded the allowable limit. For better results, limit the scope of the query to the current site or list or use a custom column index to help reduce the number of lists”.

    It is the number of sites, not the number of items in the lists being queried, that causes the error – there are mostly 0 items, in the queried list in the 73 sub-sites.

    Blogs which mention this error message all refer to the Content Query Web Part or custom code, not the Data View/Form Web Part. They all refer to the SPSiteDataQuery and say the fix is just:
    But I can’t see any reference in the DVWP to SPSiteDataQuery nor any line resembling in SPD…..I am wondering if the DVWP calls SPSiteDataQuery at some lower level…..but in any case, have you any idea how to remove this error and to rollup/query at least 200 sub-sites?

    Many thanks for your blog and help!

    • Kirsten says:

      Hi Ed,
      I found the fix. The query needed to be limited by the Lists ServerTemplate tag. That reduced the number of lists being queried and removed the error when 200 sites with this list type were queried.
      I hope that helps someone!

  12. gsouthards says:

    I have a solution in SP 2010 where I have created a Project Library site, and the subsites are the actual projects. Each project subsite has a project information list, and a project tasks list. I have used the above technique in rolling up the project information list information to the parent project library site, and it works great.

    However, I now have a request to include the associated incomplete project tasks in the same view. So, there could be multiple tasks per project. I tried creating a linked data source for the project information list and the project tasks list, and it works per site, but I can’t get the data for the linked data source to rollup to the project library site for all subsites.

    I need all this information in one view if possible.

    Any help would be greatly appreciated!

    • slalomedd says:

      Gabe, I apologize but I simply don’t have time to look into. Hopefully someone else will see your comment and chime in. As soon as I have time I’ll take a look. If you get this resolved, please share via a comment and I will post it. Thank you.

  13. Shajan says:

    thank you for your great post
    I was trying to implement this for my list name calender1. But it is showing items from all list created from calender-template.(whereas I am expecting items only from calender1). Is it possible.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s