Category Archives: KPI

Teaser: pocketSOAP, an ultra lightweight library to interact with SharePoint SOAP services

Posted on by

[6/13/2013] Update: I have changed the terminology, from Future back to Promise, to conform to the living standard.

Disclaimer: the solution presented in this article is part of SPELL, a coaching program offered by User Managed Solutions LLC. Access to the full set of SPELL solutions requires a paid registration. However, the program also includes free evaluation samples like pocketSOAP, all you need to do to get them is show your interest by registering to the SPELL newsletter.

The SPELL newsletter currently has ~180 subscriber companies. The issue featuring pocketSOAP is scheduled for the first half of June.

For years, the SharePoint SOAP services have been a favorite among power users and front end developers. Their popularity is due to an accumulation of factors:

  • They are supported across all SharePoint versions
  • They offer a wide range of features, from simply accessing list items to building sites or managing permissions
  • They work in client side environment, and don’t require server side access.
  • They are well documented, thanks in particular to the work of SharePoint bloggers (cf. references at the end of this post)

Since the SP 2010 release, Microsoft has been embracing more and more the client side, and has pushed additional tools like CSOM, REST (listdata.svc, _api), and client side rendering for list views (JSLink). At the same time SOAP services have been deprecated (but are still supported in SP 2013). Developers are faced with a difficult choice: they’d like to adopt new technologies, but at the same time these technologies don’t offer (yet) the same coverage as the legacy SOAP services. And of course they don’t work in older versions.

The purpose of pocketSOAP is to showcase how I dealt with this dilemma in SPELL. When you want to interact with half a dozen interfaces, as SPELL does, you can’t afford a 100 kb footprint just for one! So here we go, 5 kb of fully functional code, no dependency! The list of pocketSOAP ingredients:

  • SOAP: ~ 1 kb
  • Ajax: ~ 0.4 kb
  • Promises: ~ 1 kb
  • Templating: ~ 1 kb
  • Various helper functions: ~ 1.6 kb

A simple example

Note: pocketSOAP targets developers and assumes that you are comfortable with JavaScript patterns such as ajax, promises and templating. If you are an end user, you might be more interested in user friendly samples like the SPELL Tabs or the SPELL Charts (and of course the SharePoint User Toolkit).

ProjectColorFor my simple example,  I have just created a custom list called “Projects”, with 2 columns:

  •  Title is a text field for the item name (Project 1, Project 2,…)
  •  Status is a choice field for the item status: green, yellow or red.

Expected result (cf. screenshot): color coded indicators that displays the health of each project.

The code (pS is the shorthand for pocketSOAP):

var promisedItems = pS.soap({
 // Service info
 site:"https://usermanaged.sharepoint.com/TestSite",
 service:"Lists",
 operation:"GetListItems",
 // Service parameters
 listName:"Projects"
});

pS.when(promisedItems).then(function(request){
 // Apply template
 document.getElementById("results").innerHTML=pS.applyHTMLTemplate({
 template:"<table>[<|<tr><td><div style='width:12px;height:12px;background-color:|Status|;'>&nbsp;</div></td><td>|Title|</td></tr>|>]</table>",
 prefix:"ows_",
 data:pS.byTagNS(request.responseXML,"row","z")
 });
});

How the code works:

  • pS.soap() handles the SOAP request (ajax). It needs two sets of options: information about the service itself (in our case Lists/GetListItems) and parameters specific to the call (in the case of GetListItems: the list name, and maybe specific filtering options and a shortlist of the fields to retrieve).
  • pS.when().then() is a promises pattern. It ensures that the SOAP request is completed before we move to the next step.
  • pS.applyHTMLTemplate() runs the templating engine, in this specific example to output a html string. In the template expression, [< >] means that we are dealing with a collection of nodes. If we were using the new REST interface and retrieving JSON, we would simply use  [{ }] instead of [< >].
  • the prefix ows_ is just here to address an oddity with SharePoint Web services, where “ows_” is prepended to each field name. So for example to get the Title field, you need to retrieve the attribute called ows_Title.
  • the data is the result of ajax xmlHttpRequest. If you are familiar with the SOAP service GetListItems, you know that each item is returned as a namespaced “z:row” node.

How many SOAP services does pocketSOAP support?

I don’t know. So far I have only tested it for half a dozen services representative of my scenarios (90% of real life use cases are about getting or updating list items). I plan to document this as I and the SPELL followers continue testing more services.

Can I use SPELL/pocketSOAP to aggregate values from multiple lists?

Yes. Thanks to the promises pattern, the script can collect data from multiple lists in parallel, then trigger the rendering when all the items have been collected. The pocketSOAP documentation will include such an example.

Note however that for a large number of lists, client side code might not be the best approach and you’ll want to consider other techniques.

How do I make a synchronous call?

You can’t, this option has been removed from SPELL last year. The choice is to embrace a modern JavaScript approach where remote calls are asynchronous (the first “a” in ajax), and dependencies are managed via promises: when(this is completed).then(do that).

In SPELL, not only ajax calls, but also script loading, iframe loading, document ready, and some other operations are treated as promises.

Did I go too far with pocketSOAP?

The answer is… yes! If you play with pocketSOAP, keep in mind that it is a laboratory rat. The SPELL library – the production version – offers much better balance between the load effort  and efficiency at runtime.

How is SPELL different from pocketSOAP?

1/ SPELL is for production, while pocketSOAP is an experiment. For example error handling has been reduced to a minimum in pocketSOAP.

2/ SPELL supports half a dozen interfaces, SOAP just being one of them. Apart from SOAP ($P.soap), it also relies on the RPC method ($P.rpc), the REST services ($P.listdata and $P._api), etc.

3/ SPELL offers not only core features aimed at developers, but also end user solutions like the SPELL Tabs and mini-BI.

How come SOAP has never been mentioned before on Path to SharePoint?

Until recently, I have always considered that SOAP was a heavy solution and should only be used in large scale implementations, or when everything else fails. For  the samples showcased in this blog and in the SharePoint User Toolkit, my preference went to the RPC method, a more straightforward and end user friendly approach.

However when I started building SPELL two years ago, SOAP was an essential building block in a projet of that size. The decision to include SOAP was even easier after I reduced the code size to just a couple kb!

References

The official Microsoft reference for SharePoint 2013 Web services can be found here.

The first to publish a JavaScript library to wrap SOAP services was Darren Johnstone in 2008. His original site doesn’t seem to exist anymore, to get the files try an internet search for “SPAPI”.

SharePoint superstar Jan Tielens published in 2009 a series of posts to showcase the use of SOAP services in conjunction with jQuery.

That same year, Marc Anderson released SPServices. This remains to date the most useful and comprehensive resource on the subject. When people look for documentation on the SOAP services, I usually point them to Marc’s Codeplex site rather than the official Microsoft documentation.

SOAP services are also a favorite of Alexander Bautz, and he uses them in some of his published work.

Alexander’s blog drove me to another library called SharePointPlus, developed by DELL employee Aymeric Kodono and released under a GPL v2 license.

There are certainly others I am not aware of. For example I haven’t tested this framework that seems to deal with the SOAP services (and btw is not maintained anymore). If you have more information feel free to post it in the comments!

Teaser: real time Business Intelligence in SharePoint

Posted on by

StatesMap

I am making progress on my SPELL project. Its main component, the SPELL JavaScript library ($P), recently reached version 0.6 and has been implemented on a couple sites. I expect to reach version 0.7 by the end of the month and version 1 by the end of the year.

To showcase some of the capabilities of the SPELL library, I have set up a live demo featuring airline ontime statistics. In this demo, you can explore data across 3 dimensions: State (first level), Month and Carrier (second level). The third level are the list items themselves.

The first level is rendered via Google GeoCharts (for v1 SPELL will have other map options). The second level – matrix view – is a custom SPELL solution that mimics an Excel pivot table (much simpler though). You saw it in the slides if you read my previous post.

“Real time” refers to the fact that data is directly pulled from the SharePoint list. The charts always read the latest updates, as opposed to traditional BI patterns where data transfers are done at regular intervals. And there is no need for intermediate storage, as would be the case for Excel Services for example.

Note: for practical reasons the demo takes some shortcuts. For example the map doesn’t collect data directly from the list.

 I collected the airline data  (January to July 2012) from the Bureau of Transportation Statistics, I’ll add August as soon as it becomes available. Obviously interacting with more than 3 million items would not be reasonable, so I have done some pre-processing to aggregate the data by month and state. This brings the number of items down to ~500/month, for a total of 3,500 since the beginning of the year.

This is the downside of this solution: because it directly interacts with lists, it is subject to SharePoint’s usual limitations, for example the 5000 item threshold on views in SP 2010. Technically the tool could work with much bigger data sets, but this would require some adjustments and might not be desirable.

The solution showcased here has no ambition to compete with well established tools like PerformancePoint, SSRS or even Excel, but rather to offer a lightweight alternative. It presents a number of advantages that make it attractive :

  • Compatible with SharePoint 2007, 2010, Office 365 (evaluation of SP 2013 in progress)
  • Can easily be implemented by an experienced end user. No server side install, files are simply uploaded to a document library.
  • Direct, real time access to the SharePoint data (which means for example that it follows site, list and item level permissions).
  • Pure html (no Flash or Silverlight), accessible from both desktop and mobile devices.

This could for example be the ideal tool for a mid-size matrix organization, with multiple teams working on multiple programs. Managers could monitor the organization health, use the matrix view to analyze trends (what brings my organization up/down, a specific project or a specific team?), and finally access the items themselves.

A School Site to Track Schedules, Assignments and Grades (Part II)

Posted on by

Guest Author: Trudy Hutzler

Welcome to part two of the School Site Overview.

In the last article I gave you some background about why we created the School Site, and I showed how we tracked classes. In this article we start to get into more of the fun stuff as I walk you through how we track homework and assignments.

The Home Page

Again starting on the Home Page, where we have used Easy Tabs version 5 to organize our data, I want to start with the “All Assignments” tab. Here is a list of all assignments that are due for the current quarter and their status.

This shows whether an assignment is submitted, late and by how many days, or how many days until it is due. To evaluate how many days till due, or late against the due date I used Christophe’s new Countdown/Countup formulas.

The priority of the assignment is indicated by the font color of the Assignment Title. I used the Color Coded Calendar tool from the SharePoint Users Toolkit for this piece and just tweaked it a little for my use.

The progress % is a visual indicator showing the percentage of the assignment that has been completed. It moves from Red to bright green as it moves across the bar. The actual color changes in 10% increments.

With online classes, it is not unusual to have classes with over 100 students, so as you can imagine it sometimes takes the teachers a while before they grade all those assignments and post the grades. Until they are all graded the assignment remains blank or at 0% making it hard to know if the assignment was submitted and being graded or was late. So we added the Submitted field so we as parents know if the assignment was submitted, and my daughter can be sure she didn’t forget to turn in the assignment after she completed her work, which if you have teenagers you know can happen a lot.

The Due Today and Due This Week tabs are pretty self-explanatory they are filter views of the Assignments list which list all assignments that are due. The image below shows the Due This Week tab.

The Late Assignments tab is another filtered view which gives us a list of all assignments that are past their due date, and have not been marked as submitted.

The List

As I said in my last article the Lists are the real work horse of the School Site and the Assignments List is really the nerve center for the whole site, everything else is just for support of this one list. In this list all homework assignments for the current quarter are tracked, and the grades recorded. We even track how many days until it is due, or how many days an assignment is late. We also provide access to any links needed to complete the assignment, and scheduled time for completing the work. It all happens here, and it all gets integrated from here as well.

Keeping track of the assignments is only one part of process; you also need to manage your time so that you can actually get the work done and submitted.  But as we all know clicking around between your to-do list and your calendar is a pain, even for the most disciplined of us, but especially for a teenager.  I knew if it wasn’t easy to move between assignment list and calendar and somehow tie the two together it wouldn’t be long before my daughter gave up and went back to her old tried and failed methods of doing her work.   So we needed to find a way to connect her Assignments list with the Schedule calendar.  You may remember the Schedule calendar from the last article.

This calendar color codes the entries by changing the background color according to the category you choose.  If you look at the figure above you will see some of the entries have a green background, another entry has a red background, and so on.  When you create a calendar entry there is a Category field where you can choose a category like “Holidays-No School” which would give the entry a green background and “Report Periods” which would give the entry a red background.  The category you choose will determine the background color of the calendar entry.

But that’s not all, if you call in and order in the next 30 minutes you will also receive the added feature of the Assignment List integration.  You can link your calendar event with an item in your assignment list by using a hyperlink that when clicked will open up the view list item dialog box from the Assignments list.

 Let’s say you are checking out your schedule in the calendar and see an assignment you are supposed to be working on, but can’t remember what it is you were supposed to do for the assignment.  If you open the calendar event, it will look something like the figure below.   You can now scroll down to the Assignment field and click on the hyperlink, which for this example is titled “Assignment 4”.  

This will open the View list item dialog box for this homework assignment from the Assignments list, which you can see in the figure below.  The Assignment List item will even open in a new window so you don’t have to navigate back to the Calendar page again. 

But wait that’s still not all, did you notice the Assignment Page link at the top of the calendar page?

Click that link and go straight to the All Tasks view of the Assignments List, as shown in the figure below.

The All Task, as seen above, is the default view for this list. Notice that in this view we are using the Title field that is linked to the edit dialog box. This gives us quick access to edit the item and update its information. We can easily see how much progress has been made on the assignment by the progress bar. If you click on any of the links in the Scheduled column it will open the dialog box for the calendar item associated with this assignment.

So if you need to reschedule your time to work on the assignment or extend it to another day you can do so easily from the list item itself without having to navigate away from the page. But what if no work time has been scheduled for this item yet? No problem, to schedule time to work on this item, simply select the assignment in the list, hover your mouse over the title of the item then click on the down arrow to open the drop down list item menu. There we have added a new action called Schedule Work using SharePoint designer.

Click on Scheduled work and you will be redirected to the Schedule Calendar where you can schedule time to complete this assignment. Then simply hit the back button on your browser, or click on the Assignments Page link at the top of the page to return to the Assignment list. Finally notice the last column Late Status, this is our newest addition to the site and is based on the Countdown/Countup Formulas Christophe debuted in his blog not long ago. Once I saw them I just had to have them, and Christophe was kind enough to let me beta test them. I of course altered them just a tiny bit to better fit my purposes. The formula you are seeing in this view is the system time formula; I will show you the Internet Time formula in just a little bit. I have used both for demonstration purposes.

So basically that is the list, I have used views to change the way the list looks and functions based on what I needed to accomplish. For example, to track late assignment I created the Late Assignment view that you saw on the Late Assignment tab on the home page. Or I have created a calendar view to show when each assignment is due on a calendar. Some views have a few more visual indicators, like the Details view.

I wanted to be able to see which assignments were more heavily weighted and therefore, should have a higher priority, but I didn’t want to add another column when I was already struggling to keep all the information from running together in a hodge podge. So I made a dual purpose field that would display the Title of the assignment and then color code its priority level by changing the color of the font. May not be real fancy, but it is effective.

Remember when I told you we would be seeing the Internet Time based countdown/countup formula later, well here is in the IsLate column. If you need to create more visual impact this is the one for you, it combines the countdown/countup information with an icon image to really get your attention. I also tweaked this formula a tiny bit to make it more suitable for my needs, mainly by changing the text.

As I have said before replace classes with projects, assignments with milestones, and grades for progress or performance indicators and you can see how these same concepts can be adapted to the business world.

In my next post I will cover how we track grades, because in the end it’s all about the grades.

My solutions spotted in the blogosphere

Posted on by

My solutions for SharePoint end users are regularly relayed on forums, twitter and SharePoint blogs. The past two months have been particularly active, and I have identified 15 posts that mention Path to SharePoint. I am listing them below, some of them are definitely worth a look.

The SharePoint User Toolkit

The SharePoint User Toolkit: a first step toward advanced SharePoint customization
Get the Point is the official blog of the Microsoft SharePoint End-User Content Team. This article written by me describes the main solutions currently available in the Toolkit, and what’ s special about each. Big thanks to Renée Smith for giving me this opportunity to spread the word!

http://sbelskiy.spaces.live.com/blog/cns!BB6AC8CA5EB9828E!4455.entry?wa=wsignin1.0&sa=88791362
In Russian, a review of the solutions available in the Toolkit.

The Easy Tabs

The popular Easy Tabs have seen a new release compatible with SP 2010 this Summer.

Easy Tabs Rock – v5 Beta is looking really good
A very well written post, by SharePoint MVP Sean Wallbridge, with lots of screenshots – definitely worth checking out! Note that v5 beta has since become the official v5.

Use Easy Tabs 5.0 to consolidate lists, Web Parts, and calendars
Obviously Renée Smith thinks that Sean’s explanations are better than mine. Oh well, she is right!

SharePoint filtering with web part connections
The Easy Tabs in context: Kerri Abraham shows how to combine Web Part connections and Easy Tabs to make content more accessible. A very useful post!

Using EasyTabs with Filtered DVWPs to Make Data Manageable
Another example from EndUserSharePoint.com, by Jim Bob Howard, where filtering and Easy Tabs combine for better readability. Note that the post is actually part of a long series on the Data View Web Part.

The HTML Calculated Column

KPI’s without SharePoint Enterprise (on a budget)
A review of KPI options for SharePoint if you don’t have MOSS Enterprise.

How to create a Gantt chart in SharePoint
Two years ago, I showed how to build simple Gantt charts using the HTML Calculated Column. Linda Chapman describes a more complete solution in her post.
This week, Ben Schlaepfer left a comment on my blog about another solution using the same method (Ben, we are looking forward to your article!).

HTML in een berekende kolom
The HTML Calculated Column explained in Dutch by Gene Vangampelaere.

http://vipnetty.wordpress.com/2010/09/03/sharepoint_list_color/
Another translation, this time in Thai (?).

Item id in display and edit form

Showing the records id on the view and edit forms
A jQuery script, by Ryan Wheeler, who only discovered afterwards the JavaScript version I wrote 18 months ago.

Other

SharePoint Kaffeetasse
SharePoint MVP Michael Greth regularly mentions my solutions in his daily SharePoint Kaffeetasse. Last time he did was… today, about my Print Preview bookmarklet.

SharePoint 2010: Recopilatorio de enlaces interesantes
Juan Carlos González Martín regularly publishes compilations of interesting SharePoint links.

http://idubbs.com/blog/?p=241
Gathered by Wes Preston, a list of useful SharePoint links, including the SharePoint User Toolkit.

http://www.janecerdenola.com/?p=86
A mention of my solutions, as workaround when you don’t have server access.

My slides from SharePoint Saturday EMEA

Posted on by

My slide deck from the SharePoint Saturday EMEA conference  is now online. You can find it on Slideshare, or get it from the download section of my Website (under KPI roll-up). The recording of the session should be available soon, watch out for updates on EndUserSharePoint.com.

SharePoint Saturday EMEA was a fantastic event, kudos to Mark Miller and the organization team! As Mark explained in his post mortem, “there were a couple of roadblocks to making this thing actually work. First was that Europeans treasure their weekends and we had to assume that many people would not attend because it was scheduled to be a Saturday event.” Well, the Europeans proved that they had the motivation to dedicate their Saturday to SharePoint. And the Americans proved that they are always ready for action, even at 3 am!

Smart TextToHTML

Posted on by

First things first: if you don’t know what I mean by “TextToHTML “, you won’t get much from this post. In this case, I recommend that you start with this introduction.
In short, the TextToHTML script has two roles:
1/ find HTML strings in a SharePoint page
2/ Convert these strings into actual HTML

Yesterday, I came across a discussion between @EUSP, @webdes03 and @ebrackley on Twitter:
“TextToHTML can be a bad performer when tasked with lots of HTML; also research DVWPs”

This is a timely comment, as I am about to present at SharePoint Saturday EMEA. Let me expand on this, and provide a few hints on how to better use the HTML Calculated Column.

Remember: TextToHTML is not the only way

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)

The latter will be the theme of my presentation at SharePoint Saturday.

Get a faster browser

Not always a choice, but if you can, upgrade your browser to the latest version. These days, browsers are improving by leaps and bounds, with Google leading the charge.
My tests show that the TextToHTML script is four times faster in IE 8 than in IE 7. That’s huge! Some other browsers offer even better response time.

Faster TextToHTML

If you are using the TextToHTML script, you’ve certainly got the current version from the download section. What I am making available over there is a generic script, for both SharePoint 2003 and 2007. If you are on SharePoint 2007, you can make it more specific and thus faster.

Focus on the main content

As is, the script will scan the whole page to find table cells (TD elements):

TextToHTML(document.getElementsByTagName("TD"),regexpTD);

This is a waste of time, as you just need the main content, excluding header and Quicklaunch. You can easily do this by replacing the above line with the following code:

var theMainContent = document.getElementById("MSO_MainContent");
TextToHTML(theMainContent.getElementsByTagName("TD"),regexpTD);


Use selectors

Instead of grabbing all the cells in the document, try to restrict your scope to the cells that may contain HTML Calculated Columns.

For example, Paul Grenier proposed a jQuery version of my script, which allows you to grab only certain cells. If HTML Calculated Columns are only in List View Web Parts on your page, the simple $(“td.ms-vb2”) selector should be enough. For better performance, you may want to combine it with my first advice, and focus on the main content: $(“#MSO_MainContent td.ms-vb2”).

Here again, a modern browser will give you better performance (for example if it has native tools to find elements by class name).

Place your script close to the point of consumption

Imagine that you are displaying 10 lists on your page, but only the third one is using HTML calculated columns. If you place the CEWP that contains TextToHTML at the bottom of the page, the script will go through the 10 lists. If you place the script right below the third list: when the script runs, it will be faster as it only sees 3 lists, the 7 others being displayed later.
btw placing the scripts close to the point of consumption is a good practice that also applies to other scripts, not just here. In traditional Web design, you would find all the scripts under the head section of the page, but this practice has evolved now that pages are more dynamic.

If you have other ideas, feel free to share them with me and the other readers! I’ll push some of these performance improvements in the next release of the TextToHTML script. I am also looking for volunteers to test the beta versions…

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!

KPI roll-up in SharePoint (Part I)

Posted on by

Do teams in your organization need to report on the status of their projects or action items? Are managers and executives  looking for a way to aggregate and synthetize this information, to help them focus on key issues?

On January 23rd, at the SharePoint Saturday EMEA event, I’ll present a session about “KPI roll-up in SharePoint 2007”.

Last year, I already published a series about KPI roll-up, but it only applied to MOSS, and relied on the Content Query Web Part. This time, I’ll show you how a similar result can be achieved with the Data View Web Part and applied to any SharePoint 2007 configuration (wss v3 and MOSS).

As usual, no action is required on the server side. All the customizations will be done via the SharePoint UI. We’ll also use SharePoint Designer to configure the Data View Web Part, although this is not mandatory and could be one with a text editor.

Our tool for building the visual indicators is my “HTML Calculated Column” method. If you’ve already used it before, you know that it is usually associated with a client side script (“Text to HTML”). Well, here is some good news: in this specific case, we don’t even need the script, SharePoint will do all the work for us! btw this is also how it worked with the CQWP in last year’s series.

In this article (part I), I am going to describe the business scenario, with the support of a live demo.
In part II, I’ll provide the templates I used for the live demo. This will allow you to test them in your own environment (wss or MOSS).
In parts III and IV, I’ll explain how I did it, using calculated columns and the Data View Web Part.
In parts V to X… you tell me how you’ve taken advantage of the method in your own environment, and share your findings and customizations with me and the other readers!

The scenario

An organization is divided in business units, each one gathering multiple program teams. Each program team manages several projects.

Each level of the hierarchy needs visibility on the projects under its supervision. So for example:
– the program team 1.2 monitors all projects 1.2.x
– Business Unit 1 monitors all projects 1.x.y
– the top management monitors all projects

Information architecture

The SharePoint architecture follows the organizational structure: the collaborative space is a site collection, where each business unit is a sub-site of the top level, and each program is a sub-site of the business unit site.

Program team level

Note: click on a screenshot to access the live demo.

In my example, I use a custom list with 5 indicators to monitor the projects.

The budget, quality and schedule indicators track the project health: good (green), average (amber), or poor (red).

The overall status is a global indicator based on the 3 others. For example:
– Green+Amber+Amber –> Amber
– Red+Amber+Amber –> Red

To make the table easier to read, the text is converted into visual indicators: progress bar for the % complete, and traffic light for the health indicators.

 

Business unit level

 The business unit dashboard gathers all the projects under its responsibility:

For the demo, I have shown all the indicators. At this level, we could actually have restricted the view to the progress bar and the overall status, for a lighter display.

Top level

At the top level, we are collecting information from all the projects in the organization. To avoid an overwhelming amount of data, the list is filtered to only display the projects we want to focus on (in my example the ones with a red overall status).

In the next episode, I’ll share the list template and Web Part I used for the demo. A key point with this method is that it is the SAME Web Part that is used at all levels of the hierarchy to render the visual indicators. At each level, the Web Part is smart enough to only select the relevant information, i.e. the projects in the sub-tree.