KPI roll-up in SharePoint (Part II)

Posted on by

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!

32 thoughts on “KPI roll-up in SharePoint (Part II)”

  1. Pingback: Tweets that mention KPI roll-up in SharePoint (Part II) « Path to SharePoint -- Topsy.com

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

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

  3. Pingback: uberVU - social comments

  4. Pingback: Smart TextToHTML « Path to SharePoint

  5. Pingback: KPI roll-up: looking for feedback! « Path to SharePoint

  6. 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?

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

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

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

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

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

  10. 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?

    • 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

      • 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;’>â?&sup1;","gray;’>â?&sup1;")&"</span><span style=’padding:-10px;color:"&IF(OverallStatus="Amber","RGB(255, 191, 0);’>â?&sup1;","gray;’>â?&sup1;")&"</span><span style=’padding:-10px;color:"&IF(OverallStatus="Red","red;’>â?&sup1;","gray;’>â?&sup1;")&"</span></a></span>"

        (disregard my previous attempt at pasteing in html codes…

        • Unfortunately, I have not figured this out and Christophe has not provided any insights or updates.

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

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

    • My bad – i had too many windows open. Once I closed all of them and reopened, it was there. Thanks

  13. @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.

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

      • 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?

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

  17. Christophe,
    firstly thanks for this fantastic piece,

    Secondly wonder if you could aid me with an issue i’m encountering,
    the company I work within use an address format along the lines of – ‘http://domain.com/sites/[name]/[name]’

    and I believe your tool uses the url to provide the roll-up titles,
    where within the webpart could a change be made to take the values after ‘/sites’?

    Thanks for any help in this matter,

  18. Christophe (et al),

    This code has resolved many issues I was having on program level sites – the only problem I’m having, as I am NOT a developer, is that 1) I would like to comment out grouping as a roll-up and just show the current project and 2) I would like to change the roll-up titles as DP noted in his thread.

    Any help is much appreciated – THX!!

  19. Christoph, I have installed the projects lists template and DVWP and the projects rollup correctly. The only issue I have is that the grouping is incorrect.

    I have the following URL structure /teamsites/division/BU/Program/project/subproject

    I have added the list template in Program level and below and would expect the rolloup to group by project and subproject.

    At present it groups by Teamsites and Division. What do I need to change in the DVWP to start from Program

    Thanks in advance and keep up the great work

  20. In answer to the questions about grouping:
    in my example, I am using the top level and first sub-level in the sites hierarchy for the grouping. If you need different rules, you can edit the Web Part in SharePoint Designer.

  21. Please, could you or somebody else use the Designer to publish a version with a slightly different starting point than the top level of the site hierarchy? I have tried but I am not able to find the correct place to manually edit in a top level being “/sites/” rather than just “/”.

    Thanks!

Comments are closed.