After reading about the scenario and watching my live demo in part I, it is now time for you to try out KPI roll-ups for yourself.
Note: You’ll need site owner permissions on your site collection to set up the demo.
I am providing all the templates needed to replicate my demo in the download section. This includes 4 files:
Projects_List.stp (List template)
Each program site has a projects list. This is where the project managers will update their project status: progress and 4 health indicators.
Projects.webpart
This is a Data View Web Part that displays the visual indicators, progress bar and traffic lights.
Placed on the home page, it will find all the project lists in the sub-tree. For example, if added to the home page of BU2, it will find all the project lists in BU2, Program 2.1, Program 2.2 and Program 2.3.
Projects_with_Overall_Status_=_Red.webpart
Similar to the previous Web Part, but with an additional filter that only selects the projects in poor health (overall status = red).
Program_Site.stp
A site template to create program sites. The template includes both the Projects list and the Project DVWP.
So you have the choice: either use the site template, or use the list template with the DVWP template. See below detailed instructions on how to use the files.
A couple comments:
- I used a site content type to manage centrally the project items. It is excluded from the templates, as the SharePoint UI doesn’t provide a way to export/import content types.
- the DVWP includes two grouping levels that are based on the site collection hierarchy.
What’s next?
I am really looking forward to your feedback. I think it is a very convenient implementation of the “HTML Calculated Column”, as it doesn’t rely on a Content Editor Web Part. Also, the method works on both wss and MOSS, gives you access to a large choice of visual indicators, and doesn’t require images.
If everything works as advertised, please leave a comment here! If not, you may also leave a comment, but more importantly contact me so that I can help you out.
Also, let me know if you’re not clear about the scenario and the benefits of such an implementation.
In part III I’ll explain how I built the projects list.
Detailed instructions
Start by downloading the templates from the download section, under the topic ”KPI roll-up”. To download a file, right click on it and select “save target as”.
To use the Projects list template (you need site owner permissions):
- On your top level site, go to the List template gallery:
Site Actions | Site Settings | Galleries | List templates
- Select Upload
- Upload the Projects_List.stp template
You can now create lists based on the ProjectsList template, on any site of your site collection:
Site Actions | Create | Custom Lists | Projects List
To use the Data View Web Parts:
- On your top level site, go to the Web Part gallery:
Site Actions | Site Settings | Galleries | List templates
- Select Upload
- Upload the two templates Projects.webpart and Projects_with_Overall_Status_=_Red.webpart
You can now add them to your Web Part pages like any other Web Part.
Alternately, you can use the Program_Site.stp site template that contains both the projects list and the Data View Web Part (you need site owner permissions):
- On your top level site, go to the site template gallery:
Site Actions | Site Settings | Galleries | Site templates
- Select Upload
- Upload the ProjectsList template
To create a site based on the template:
Site Actions | Create | Web Pages | Sites and Workspaces
Good luck!
27 comments
Comments feed for this article
January 13, 2010 at 5:29 pm
Tweets that mention KPI roll-up in SharePoint (Part II) « Path to SharePoint -- Topsy.com
[...] This post was mentioned on Twitter by Christophe Humbert, Matt B.. Matt B. said: RT: @Path2SharePoint KPI roll-up in #SharePoint, part II: your turn, download the templates! http://tinyurl.com/SPkpi2| dl'ding right now [...]
January 15, 2010 at 1:01 am
Greg
Hi Chris,
This is a great idea and extension of your series on Calcualted column and Text to HTML. Is there a way to do a ‘vertical’ calculation on grouped items and display a count, average, max or min and have the corresponding KPI show on the header even with the groups collapsed?
Thanks,
Greg
January 28, 2010 at 12:31 pm
Christophe
Greg, this is possible and I have demo’ed it at SharePoint Saturday EMEA (the recording should be available soon). But in this case you’ll have to re-create the KPI in the Web Part header.
January 16, 2010 at 3:27 am
uberVU - social comments
Social comments and analytics for this post…
This post was mentioned on Twitter by Path2SharePoint: KPI roll-up in #SharePoint, part II: your turn, download the templates! http://tinyurl.com/SPkpi2...
January 16, 2010 at 6:58 am
Smart TextToHTML « Path to SharePoint
[...] Right, most of the examples you’ll find on my blog rely on the TextToHTML script, embedded in a Content Editor Web Part. But there are other ways to render the HTML. In particular, the Data View Web Part, used in crosslist mode, or the Content Query Web Part can directly do the rendering. For more information, see these series: – for MOSS KPI roll-up in MOSS – for SharePoint 2007 (applies to both wss and MOSS): KPI roll-up in SharePoint (Part I) KPI roll-up in SharePoint (Part II) [...]
January 19, 2010 at 11:58 am
KPI roll-up: looking for feedback! « Path to SharePoint
[...] 19, 2010 in Uncategorized I am still hoping to get some comments on the KPI roll-up templates I provided last week. If you have tried them out, I’d be interested in your feedback, as [...]
January 19, 2010 at 2:24 pm
Jerry
Christophe,
We are trying to add some columns and personalize the column names. However, we are having no success. Can you explain how we might be able to do that?
January 20, 2010 at 2:25 pm
Jerry
Christophe,
Great work! We have begun to try to implement it on our site. What we are running into is a problem trying to either:
A. Change the names
B. Add columns for display.
Any pointers you could give us?
January 28, 2010 at 12:32 pm
Christophe
Jerry, I’ll provide more step by step instructions in the next episodes.
January 26, 2010 at 9:27 pm
Paul
New to Sharepoint, I was excited to get Calculated HTML to work. My admin has severely limited the type of webparts and lists in MOSS 2007. No KPI webpart for instance and no ability to load site templates. Can I still use the Projects – KPI if I created the list manually?
Thanks
January 28, 2010 at 12:35 pm
Christophe
Paul, do you have the ability to create site content types and load list templates? If so, this will work for you. If not, you can still do it but it’ll be painful to create/maintain the lists individually for each bottom site.
January 28, 2010 at 3:21 pm
Paul
Christophe, thanks for the response. I work for a very large company (>200k employees) and they have chosen to emasculate sharepoint. I cannot add content types or templates. My corp PC won’t even install sharepoint designer. I have two sites to maintain.
Oddly, I can import webparts and have access to CEWP, so calculated HTML has been great.
Thanks again
January 30, 2010 at 4:17 am
Christophe
Paul, feel free to contact me directly if you’d like to discuss this further. Data View Web Parts might work for you, even without SharePoint Designer.
February 1, 2010 at 3:48 pm
Lee Seidel
Christophe….as always a great and useful tool and information. I have been playing around with this in my spare time to get a feel how it would work in our environment. Below is some feedback.
First tried this on a collection defined under a wildcard (i.e. /sites/test site)….The webpart looks at the wildcard (/sites/) as the top level site so you lose a whole level when you use this under a wildcarded site. Perhaps add something to the webpart that lets you pick what level to start as the root? This would also help with utilizing it in other sub-site scenarios.
Then I defined the collection as an explicit site. This worked fine recognizing the top site as the root and the sub-sites that had the KPI list in them accordingly.
In our environment our taxonomy is setup such that we would not normally have a division as the top level site for projects. We have a top level site for all projects and then underneath that there are divisions and then departments under that division. The current webpart (auto-pulling) from the root does not afford us the separation that we would need to use this more effectively.
Here again…..Having the ability for the web part to allow the setting of the root site to start building its sub-site roll-up would be ideal.
I am not seeing the star image on my site…..I see a black square with a colored edge instead. Any ideas what I am missing that is not showing the starburst?
February 1, 2010 at 5:51 pm
Lee Seidel
As a follow-up note to my post….what I am seeing on our site is not what is happening on your live demo site.
Wherever I use the web parts on our site it pulls from the top root site down…..whereas on your live demo site the webparts appear to pull from whatever site they are located on downwards.
Example….
On your http://www.pathtosharepoint.com/pages/dashboard.aspx you have the page with your two webparts.
On your http://www.pathtosharepoint.com/BU1 site you have the web part and under the BU1 site you have two sub-sites with the defined project list.
The webpart on the BU1 site starts with the BU1 site and then list the two sub-sites underneath it.
The webpart on the dashboard page shows BU1 & BU2 and their subsites.
When I try the same thing on our site the webpart displays the root site as the first site everywhere.
http://www.oursite/projects/ Explicitly defined site collection (=division in your example)
http://www.oursite/projects/pages/default.aspx updated with your two webparts.
http://www.oursite/projects/BU1 Sub-Site of projects (=Business unit in your example)
http://www.oursite/projects/BU1/project-A Sub-Site of BU1 (=Program Team in your example)
(also same as above for BU2)
Placing the webparts on BU1 (or BU2) site the renders the results tree as follows:
-Projects
+BU1
If I have everything setup correctly I think I should be seeing:
-BU1
+project-A
Placing the webparts on default.aspx page of the projects collection the rendered results tree looks as follows:
-Projects
+BU1
+BU2
If I have everything setup correctly I think I should be seeing:
-BU1
+project-A
-BU2
+project-A
March 3, 2010 at 12:28 am
Dag Myrdal
Hi,
I have had the same issue as you seem to have. Have you had any success?
On my site, we have actually two pre-defined levels that should not be part of the levels in the roll-up web-part:
http://team.eu.enet/sites/SLS-Norway/projects/P1
ie, all sub-sites we want is below the /sites and /sites/SLS-Norway.
The web-part has then two levels of emptyness and then all KPI’s listed on the same level under that.
One small improvement I have made on the “Project List” template is to have a “Link” column, and use it for a hyperlink in the traffic light for “Overallstatus-KPI”. The “Link” is typically used for Project Managers to insert a link to background information (typically a regularly updated powerpoint file stored somewhere with more figures, trends and S-curves).
The updated calculated column for the OverallStatus-KPI field is then:
="<span style=’background-color:lightgrey;font-size:24px;’><a href="&Link&"><span style=’padding:-10px;color:"&IF(OverallStatus="Green","green;’>â?¹","gray;’>â?¹")&"</span><span style=’padding:-10px;color:"&IF(OverallStatus="Amber","RGB(255, 191, 0);’>â?¹","gray;’>â?¹")&"</span><span style=’padding:-10px;color:"&IF(OverallStatus="Red","red;’>â?¹","gray;’>â?¹")&"</span></a></span>"
(disregard my previous attempt at pasteing in html codes…
March 22, 2010 at 10:51 am
Lee Seidel
Unfortunately, I have not figured this out and Christophe has not provided any insights or updates.
February 26, 2010 at 3:47 pm
Paul
Christophe,
Lacking proper development environment, I signed-up for a hosted account so I could test and develop free of the restrictions imposed by my company. Unfortunately, my new hosted provider will not let me upload templates. RATS! Looking forward to any update like Lee and seeking a new hosting provider.
March 3, 2010 at 10:28 pm
geekminer
Chris, I uploaded the Projects_List.stp template to my top level site and I can see the file there. My problem is i cant see in it the list of templates when I go to another site in the collection. I must be missing something…Thanks
March 4, 2010 at 1:14 pm
geekminer
My bad – i had too many windows open. Once I closed all of them and reopened, it was there. Thanks
March 28, 2010 at 1:39 pm
Christophe
@Lee and Dag: the values used for grouping are extracted from the item URL. If you open the DVWP in SharePoint Designer, you can see the formulas I used for grouping and tweak them.
May 3, 2010 at 2:40 am
Wayne
Hi Christophe
With DVWP how do you show an item from a list for just the current month? I have setup the KPI rollup similar to what you have suggested except for the following:
- Each item in the list is a month (so you can track changes over a year)
- Each project is a single list
The problem I have of course is having the same date across multiple projects but that is mitigated by using another field for the project name so in the DVWP I can use the project name (rather than the title field) but stumped on the filter. Many blogs show how to do countdowns etc but we just need to show the latest item in the list is the current one…
Cheers
Wayne
July 23, 2010 at 2:10 pm
AP
Christophe,
I can see the ‘Red’ status rollwebpart on your page – http://www.pathtosharepoint.com/Pages/ProgramsDashboard.aspx
has summary count showup in header row. This feature is not available in webpart I can download from your downloads page. could you please post the latest code for this webpart on your downloads page?
July 23, 2010 at 3:20 pm
AP
Just noticed that this rollup doesnt work for more than 2 level. Is this correct?
July 23, 2010 at 4:41 pm
Christophe
The roll-up will work for the whole site collection, not just two levels.
July 26, 2010 at 2:11 pm
AP
For some reason it is not. I see you have BU1 and then Pgm1.1. are you able to create a site beneath Pgm1.1 and show all KPIs on top?
July 29, 2010 at 2:22 am
Christophe
I just created a sub-site of Pgm1.1, called Pgm1.1.a, with 3 projects (1.1.a.1, 1.1.a.2, 1.1.a.3). They are displayed in the roll-ups of their parent sites as expected.
I added the summary count later for a quick test, it is not in the Web Part available for download.