As a new way of managing projects for an enterprise, a project team that I work with is embracing the concept of Earned Value (EV). You know… the measuring of current performance to forecast, measure progress, and schedule the overruns at the earlier stages in a project; a basis for what future performance could look like. It’s a comprehensive analysis technique you’ll find a lot of PMPs living, bleeding, and breathing today.

With that, I’ve been asked to introduce the technical elements that embrace this methodology into a one-place-fits-all solution for our executive management, project leads, and general staff who are contributing to the overall project goal(s). Survey Says? Sharepoint!

The purpose and intentions for this collaboration surround the activity of project leaders reporting on high level tasks that are outlined as key deliverables. Giving an quick dashboard to Project Managers on how their projects are progressing, to project teams, and most importantly to the executive management / sponsorship of projects they approved. It’s a vehicle to communicate static and dynamic content as the earned value methology goes to work. Requirements? Glad You asked…

One Sharepoint List — Project Status

Columns : (Required — Req)

  1. Project    (Single Line of Text) – Req
  2. % Complete (Number as Percentage)
  3. Assigned To (Person or Group)
  4. Planned Start Date (Date & Time)
  5. Planned End Date (Date & Time)
  6. Actual Start date (Date & Time)
  7. Actual End Date (Date & Time)
  8. Team (Choice – Dropdown) – Req
  9. Comments (Multiple Lines of Text – 6 lines)
  10. Last Status Update – (Date & Time)
  11. Status/Planned (Calculated)
  12. Status/Project Lead (Calculated)
  13. Status/Planned Calc (Calculated)
  14. Status/Project Lead Update (Choice)
  15. Today (temporary use-case: I’ll explain later)
  16. Last Updated By (Person or Group)
  17. Created By (Person or Group)

A content web part with script used for the above calculated fields (will provide the scripting per request) to render  the following color indicators on the conditions of data fields.


Everyone will have different requirements, but You get the idea of what can be accomplished using Sharepoint as the medium to conveying results. The bulk of the work (aside from various Sharepoint list views with sorts, filters, and group-by settings configured) is in the Calculated fields; And quite frankly should be!

To generate the (2) color indicator fields (Status/Planned and Status/Project Lead columns) there were (2) other columns used, but hidden from the typical user views. These hidden columns did the underneath-the-scenes work and then other (2) exposed calculated fields did the color rendering on the basis of output generated in the hidden columns. The coloration does require source script to be in a content web part, and on the same page as the web part rendering the list of content (using the calculated fields).

The only one that I want to illustrate as an example of color-coded calculation is using the Status/Planned (exposed, calculated column) and the Status/Planned Calc (hidden, calculated column). The designated color spectrum means something to the business in terms of progress and/or risk. So starting with the Status/Planned Calc the following psuedo code needs to be developed…

  • Project Completion is 100% – Green
  • Project Completion is 0% ; Planned Start is greater than or equal to Current Date – Green
  • Project Completion is o%; Planned Start is less than Current Date – White
  • Project Completion is between 0% and 100%; Planned End less than Current Date – Red
  • Project Completion is between 0% and 100%; Planned End greater than Current Date – Green
  • Else Project is Orange

We then have a series of IF Statements to programmatically validate the above conditions.

 =IF([% Complete]=100%,”Green”,IF(AND(AND(Today>[Planned Start],Today<[Planned]),[% Complete]=0%),”Orange”,IF(AND([% Complete]=0%,[Planned Start]>=Today),”Green”,IF(AND([% Complete]=0%,[Planned Start]<Today),”Red”,IF(AND(AND([% Complete]>0%,[% Complete]<100%),[Planned End]<Today),”Red”,IF(AND(AND([% Complete]>0%,[% Complete]<100%),[Planned End]>Today),”Green”,”Orange”))))))

NOTE : In the above formula, there is a subtle issue with how You can get this calculated field to work without Sharepoint throwing an error (on save) regarding a subtle formula syntax. Microsoft does NOT allow you to introduce the [Today] function (aka Current Date) as a reference into any calculated field. Technically You can, but here’s the key to making it work. You have to throw Sharepoint’s attention off by creating another column in your list called “Today” and then create your formula with the [Today] reference made. At save, Sharepoint will NOT throw an error and will commit the formula to the calculated field. Now, you have to go back into the Sharepoint List Settings, and delete the [Today] column. And then the formula You wrote will no longer reference a column but instead the [Today] function. And if you find yourself in need of modifying the formula again, you’d have to re-introduce the [Today] column in the interim to do so. Just a tip worth noting. 

Now that you have your color calculation field defined, and working. It’s time to work on the Status/Planned (exposed, calculated field) for apply the approriate style tags for the users to see. This is where we introduce the javascript source code (available at request) into a content editor web part page (CEWP) that will live on the same page that loads the actualy Project List. Now we introduce the calculation material that will reference the Status/Planned Calc output that designates which color the, in this case, bullet point will be.

Status/Plannedformula is =”<DIV style=’font-weight:bold; font-size:20px; color:”&[Status/Planned Calc]&”;’></DIV>”

Once these (2) fields are configured, start entering data and start moving the values around to validate it is working as you’d expect. My hope is that You found this article useful and that it may have answered a number of questions You were looking to answer. If you have any questions, please let me know.

Thanks. Josef


If you want to maintain a healthy sharepoint environment, it takes requirements determined by the business and the system administrators. If you haven’t taken time to evaluate your user’s environment, start asking questions. Sharepoint will take planning.

Evaluate the business. Understand the users actions; how the environment works.  Physical and logical contraints need to be known. When you think about configuring the network, hardware, application, database, and disk storage layers of the environment. Define your sharepoint deployment strategy at the physical, process, logical, and site-level perspectives.

On a spectrum of Isolation to Scalability, consider the following:

  1. Physical Isolation : separation of networks for each site collection — not practical for most environments
  2. Physical Isolation : seperation of servers for each site collection — not practical for most environments
  3. Process Isolation : seperation of application pools for each site collection — Internet/Extranet, or Sensitive Data
  4. Logical Isolation : seperation of web applications for each site collection — Internet/Extranet, or Sensitive Data
  5. Logical Isolation : seperate host names for each site collection — Internet/Extranet, or Sensitive Data OR Internet (non-interactive/readonly) or Intranet
  6. Logical Isolation : seperate site collections for each site — Internet (non-interactive/readonly) or Intranet
  7. Sharepoint Site Isolation : multiple subsites, and workspaces in a site collection — Intranet Only
  8. Sharepoint Site Isolation : specific security for a list or library within a site collection or subsite — Intranet Only

Though measuring the isolation and scalability variables, think about how they determines your ability to administer this.

  • Will it be easy for you to limit the exposure of failures?
  • Will security be easy to manage?
  • If you’re introducing a highly-scalable environment you’re also inviting complexity — increasing risks to break.
  • If a user’s team loses their site, what will your degree of restoration be? Item level? Site Collection Level? Database Level?
  • If the architecture approach is #7 the site collections can grow (if you place no quota contraints) with many groups using sub-sites within a single site collection (a top-level site / logical partition within a contentdb). To restore a previous version of a particular sub-site may impact the all teams within this site collection, creating noise, possible data loss, etc. This is assuming you’re not using a technology like Microsoft DRM that will manage all this for you — however this solution requires large amounts of storage equating to more costs.
  • What about security management? Do you want to commit to managing item-level security? Do you want to manage multiple subsite permissions that are unique within a parent site collection? Wouldn’t it be easier to set the permissions at the site collection level and then require all subsite’s to inherit the parent-level perms?

So isolate the architecture as best you can, but keep it flexible to scale it to the degree your company intends to take the technology. Every business will have a different.

Easy. Right?

Tune in next for information on factors to consider when you’re putting data in Sharepoint.

  • How storage quotas will help. . .
  • Versioning is a convenience to user’s, right? What about additional storage factors?
  • What about disk inflation? Why is 1GB of RAW Fileshare Storage inflate to 1.2 GB of storage in Sharepoint?

I’ll have more on this topic, and it will certainly help plan for the needs of your business.

Sharepoint Favorites?

April 28, 2009

I’m constantly being hit with emails, phone calls, and IMs requesting a sharepoint url they have forgotten to thumb in their favorits. Ugh. . . Do I have time for this? Do you?

Sharepoint user’s adopting the web-share approach need a way to track down and it loox like Black Knight has the answer (

You can download the solution from HERE

It addes a new menu item to the Welcome Menu (upper-righthand corner) called ‘Show All Sites’

It will then build a tree-view structure of all sites (YOU) has access to. If you have access to large number of sites, I hope you know the site by name because all the sites are listed by their given name hyperlinked to the URL.

A scenario I want to demonstrate to our internal user group is how we can build an application that will record Annual Individual Success Plan goals for each employee and Manager to have touch-base meetings to review their progress for the year. As a way to make it easy for data entry into a form, I want to remove extra work by the user(s) to entry their Windows AD Attributes, by making calculated fields that will query this data based on the ‘User :’ field.

For example, I would like to create (3) new calculated fields for this list called [Department], [Job Title], [E-Mail]. I want to make a calculated statement for each field to retrieve the respective data from Windows AD based on the ‘User :’ field.

So for ‘Department’. . . I want to select [Department] from WinAD where [User :] LIKE ‘xyz’
And ‘Job Title’ . . . select [Job Title] from WinAD where [User :] LIKE ‘xyz’
then ‘E-Mail’ . . . select [E-mail] from WinAD where[User :] LIKE ‘xyz’

Ideally if the [User :] changes to ‘123’ the [Department], [Job Title], and [E-Mail] fields will update with new content.

If this approach isn’t possible, is there a way to recommend Auto-Populating another List Called [User List] updating on a daily/hourly basis from WinAD and referencing calculated fields to that?

Thanks – J. Wright