Financial Roll-Up Web Part
During one of the migrations of my blog several years ago, part of this original post got lost, but the images associated with it still exist. So, I don’t know what the post said (I wrote it almost 7 years ago), but here are the images that are recovered, and then the part where the text starts is the part of the original text that is still intact. Then, you’ll see that there’s also a link to an associated video at the very bottom.
in the front of it. That first digit is the item id. SO, what we need to do, is get this URL, but only use everything AFTER the # in there.
- Here’s how (Yes, I finally figured it out after much trial and error). The magic formula. Click to select a title field, and click to change it to be formatted as a hyperlink. Paste the following formula into the Address box for the hyperlink:
{concat(‘/’,substring-after(@FileDirRef,’#’),’/Dispform.aspx?ID=’,@ID)} - In the Text to Display part of the hyperlink settings, put the Title field. This is what it will look like:
You should have seen me when I finally figured out that formula. It was definitely a geek “Woo” moment. - After a little alignment and format tweaking, here’s the final product. A true rollup.
Here’s the video where I show how to do it:
It works. Thanks a lot :). Do the sites have to be in the same site collection?
Yes, the sites have to be in the same site collection.
Great post! You always amaze me with the innovative ways you take and configure SharePoint with real world solutions that are accessible to the SharePoint masses and not just developers. As always Laura…. you rock! 🙂 Michael Gannotti
Hi Laura, nice post…again (i’ve been watching your series for a while now, really instructive)! One thought related to the your step 22. haven’t you tried to replace the content with the ootb SharePoint:FormField control (just use the chevron from the field itself and in the Format As option choose List Form Field), but in DisplayForm (by default it just shows “Edit Mode”). This should keep you of the troubles if someone changes the default DispForm.aspx in your list with another custom display form (even though in regular cases it should get redirected still, but why another redirection?).
First… awesome post!! can this approach be used for rolling up document content types across various sites ? if so can you give some guidance on what changes are required ? –Gerald
c_marius, No, since that’s not one field, there are a couple inside that url, that’s not possible. gparish, I wasn’t able to accomplish this with documents, since I couldn’t get the URL to the document name to work.
Excellent article, Laura. I can hardly wait until tomorrow morning to try this at work. As usual, this is great work on your part. Thank you! Dan
Do you know if there is a way to limit the scope of the recursive call? For example, if I have a site collection similar to the structure below and just want to return all of the items from a particular content type under the Division 1 site. Is there a variable to limit the scope to that? I tried the weburl variable, but it didn’t seem to work. Site Collection -> Division 1 ->->Department 1 ->->Department 2 ->Division 2 ->->Department 3 Thanks, Eric
Eric, Just try setting a regular data view web part filter: Content Type= _____
Any idea when endusersharepoint will be fixed so I can view the screencasts? This really sounds like the solution I’ve been looking for, but this is also my first foray into Designer so I’m struggling. Thanks!
turners, Looks lik the endusersharepoint.com site will be back up on Monday, December 7th.
Great post- a good replacement for content query web parts, and this can be more flexible.
In order to get this to work with Documents, you have to add the . I think by default it only looks for list items and not in libraries. Happy New Year!
Kirk, You can easily get it to *list* all the documents. The hard part is being able to create the hyperlink. That’s the part I couldn’t figure out.
Just curiouse Laura whether you’ve gotten this to work on the Sharepoint 2010 Beta. If so, any tips? Thanks!
From a portfolio perspective, this is the first article that has provided some hope that “rolling up data from multiple projects” can be done without a third party solution or a lot of coding. I am much more a program manager than coder :). Before I go too far down the path, the issue I am trying to solve is slightly different. Each project has a task list. At time of project start, the project specific task list is created from a task list template with content. In those task lists there would be categories of tasks (purchases, expenses, etc). The project manager would use this list to drive project to completion. The roll ups would be based on the category in the task list (not individual task lists) so that the departments responsible for purchases, expenses, etc has a cross-portfolio view of the tasks they are interested in across multiple projects. Most departments only have 2-5 tasks per project, so it really doesn’t make sense for us to take a task list for each department approach. (We like the tasks lists due to the OOB emailing capability when tasks are passed back and forth between project teams and department representatives). Also, related but slightly different…we store top level project information in a sharepoint list (title, start/end date, along with a couple of key milestones). The details are elsewhere in task lists. When a task list is created from a template, is there a way for it to collect a project end date value from a that sharepoint list and calculate the due dates based on that value.
Hi Laura, I have this working on a site with several different types of lists but cannot seem to get it working with an InfoPath form library. I appreciate the problem with the links – in this case I do not even need the document, just some of the fields from the library – but as soon as I change it to CrossList then I cannot even see the data from my initial list. Have you tried with an InfoPath library? Thanks Dave
Hi Laura, I have used this approach in WSS 3.0 and MOSS, but I too am having trouble with getting it to work with SharePoint 2010. Have you come up with the magic for making this work in 2010? Thanks, Dave.
Laura, Have you been able to get paging to work in CrossList mode? My DVWP works fine in CrossList mode, but whenever I turn on paging, it just repeats the complete data set for each page. For example, if I have 7 records and set the paging to 5, it will show 1-7 on the first page, and then show 8-15 on the next page. It appears to ignore the paging limit and it also just repeats the same 7 records on each page. Thanks, Eric
Can you do this with the Events list, too> I am trying, and as soon as I change DataSourceMode to CrossList and the Select Command value, I stop getting any data coming in!!
Laura, This is fantastic! Can you do the same thing with KPI lists?
Hi Laura, Do you know is it possible to limit how many items are returned in a group? If I just wanted to return the latest item from Education, the latest item from Travel etc…? Thanks Ciara
Ciara, No, I don’t think that’s possible.
Incredible!
Hi Laura, I’m looking for a method to apply 2 filters on a list: 1. multi filter (where you can filter multiple items, like more than one department) 2. a single filter (a second filter on another column in my list) I manage to create a multi filter, or 2 single filters (with or without using web parts), but not the combination of both. Can anyone help me on this one? Thank you!! Guy
Laura, i have a date field which is showing up fine in the rollup but i am unable to apply any conditional formatting to it. The Less Than and Greater Than conditions are not available from the drop down and none of the other choices have any impact. Have you run into this before? Do you have any ideas how to fix this problem?
Laura’s instructions work great for a list. However, I have been unable to get this to work for a document library. (I’m not interested in the link back to source that Laura employed in this case.) Once I set the library DVWP to CrossList, it no longer shows me the contents of my library. Ideas?
Hi, came across but no time to go thorugh now, however I bookmarked it; this lady’s posts are as good as her smile 🙂
Dean, You may have to go into the “advanced” (XSLT) view in order to create your filter in the advanced screen, as more of a free-form formula. Dfwilcox, Try removing all columns from your view except for Title. See if this lets you see all the items. Then add new columns one at a time, to see which one is causing the problem.
It didn’t work for me. At step 13, you write, “Go ahead and add the Purchasing Cost field to the data view web part, and set up grouping by Cost Category.” What do you mean by go ahead and add the Purchasing Cost field to the data view web part? I am unable to add that field. Also, the second screen capture under 13 includes the Purchasing Cost column. I can only replicate this by adding title and Purchasing cost in step 9. Lastly, when I select Design mode in step 17, I receive the following error message: “The server returned a non-specific error when trying to get data from the data source. Check the format and content of your query and try again. If the problem persists, contact the server administrator.” I tried to follow your instructions to the letter; however, I keep getting this same error message.
Hi Laura, This would be incredibly helpful if it could be updated for SP2010 – this is exactly what I’m looking for, but these instructions start falling apart at step 15 at which point SPD starts giving an “unspecified server error”. How should the steps from 15 through to 17 go to achieve the same results? Thanks so much – this very very nearly got me where I needed to go! Adam
Hi adam, I haven’t been able to do this in 2010 either. It appears that some functionality that existed in 2007 has been removed in 2010. If I ever do figure it out, I’ll blog about it.
I am having problems. 1. My content type is built at the collection level 2. I want to roll up lists of the same content type from subsites 3. I am starting from a subsite of the collection. 4. If I try your example after I add Cross list the list is blank 5. if i try just a filter for the content type on a DVWP it only shows the items from the current site list , No subsite info. Please help.
All I know about this besides the steps I have listed, is to start very simple, and try only one or two columns, and then if when you get it working, then add more. I usually create the DVWP at the very top of the site collection, and then once it’s working, then export it and import it onto one of the sub-sites.
Great work here! A couple of notes and a question: Notes: -I have it working great in my 2010 environment. -You can pull in a huge number of columns, and lookup columns work, but you need to use formating to get rid of the characters before the item and the #. However, if you have a lookup column that brings in another column, that other column will break the DVWP. Question: – I can get it to work in the top level site , but not in the subsites. The code change of SelectCommand is fine, but as soon as the DataSourceMode is changed to CrossList it breaks and says that there are no items to display in this view and the datasource details pane blanks out. So I tried to export the webpart (both to my local drive and to the site gallery) but when I place it on the page the page goes blank and gives me something like “the page you are accessing has a list that has been deleted by another user…” Any suggestions on what I am doing wrong?
I tried this in SP2010. It says “The server returned a non-specific error…” Does this works in SP2010?
Shay and Prabhakaran, I have not gotten this to work at all in sp2010.