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

Google GMail SMTP Sending Email

Send an email using

set mm = CreateObject("ChilkatMail2.ChilkatMailMan2")
mm.UnlockComponent "test"

'     SMTP server:
'     Port: 465.
'     Require SSL/STARTTLS: Yes.
'     Require SMTP authentication: Yes.
'     User name: your Gmail email address.
'     Password: your Gmail password. 

mm.SmtpHost = ""
mm.SmtpPort = 465
mm.SmtpSsl = 1
mm.SmtpUsername = "****"
mm.SmtpPassword = "****"

set email = CreateObject("ChilkatMail2.ChilkatEmail2")
email.Subject = "test from Chilkat"
email.Body = "this is a test for our customer"
email.AddTo "Chilkat Support",""
email.From = SmtpUsername

mm.SendEmail email

MsgBox mm.LastErrorText
mm.SaveLastError "error.txt"

A code/script template

June 3, 2010

‘—————— Script Remarx ——————-

‘ Script : [script name].vbs
‘ Author : [author name]
‘ Created : MM.DD.YYYY
‘ Description :

‘——————- Code Header ——————–

‘——————- Code Footer ——————–

Function Host()

Dim WSHShell
Dim objNTInfo

Set objNTInfo = CreateObject(“WinNTSystemInfo”)
Host = ucase(objNTInfo.ComputerName)

End Function

Sub Email

Set objEmail = CreateObject(“CDO.Message”)
objEmail.From = “”
objEmail.To = “”
objEmail.Subject = “”
objEmail.Textbody = “”
objEmail.Configuration.Fields.Item _
(“;) = 2
objEmail.Configuration.Fields.Item _
(“;) = _
“[mail server address goes here]”
objEmail.Configuration.Fields.Item _
(“;) = 25

End Sub

Oracle Business Intelligence (BI) Publisher utilizes a Quartz, an open-source job-scheduling system. To setup the BI publisher scheduler you are required to install the scheduler tables to a database option of your choice IBM DB2, Microsoft SQL v2000 or v2005, Sysbase Adaptive Server Enterprise, or Oracle v9i, Oracle 10g, Oracle 11g.

In my scenario, we were using IBM DB2 via z/OS platform. When the ‘install schema’ task was attempted we identified an issue and became unsuccessful installing the job scheduler schema tables.

According to the application error : the Schema installation failed while creating tables. Schema may already exist. Please remove the existing schema or choose another database and try again.

According to the DB2 Database Errors : the installation script is trying to use the column data type of BIGINT which is NOT supported for DB2 on a z/OS platform. As I understand it from our Database Analyst, BIGINT will ONLY work on DB2 UDB. The documentation recommends that the IBM DB2 v8.1 & v9.1 are supported and recommended. However there is no specific designation on DB Platforms support documentation. 

In our case we had to change our platform option to Oracle because we did NOT have DB2 on UDB (only on z/OS)

Oracle Support confirms the issues we experienced and why – this is their response :

See the Certification Guide:
We only support DB2 UDB:
These are the only two DB2 versions supported for scheduler:
IBM DB2 for UDB EE 8.2, 9.5

Hope this helps others who are experiencing the same issue.

Thank.You – Josef

REM Start of Script

@echo off
REM – File: [File Name]
REM – Description: [Description]
REM – Author: [You]
REM – ADD REM comments if you don’t want to restart any
REM – of Services
echo Restarting Services…
echo ======================================================
net stop “[Service Name]”
net start “[Service Name]”
echo ======================================================
echo Services Restarted


REM End of Script

NOTE :You can add above script into a *.bat file and then run this adhoc or using Windows Scheduled Tasks

Delete a Windows Service Using the Command Line Developing and debugging Windows Services in Visual Studio 2005 usually means creating setup projects to install and uninstall them. One of the quirks of using installers, however, is that sometimes the Windows service fails to uninstall.

Below is the manual way of deleting or uninstalling a Windows service using the command line in Windows Vista and XP. C:>sc delete [service name] The utility also has other functions relating to Windows Services. Check them out.

Music | Reviewing the Basix

November 26, 2009

Diving back into the basix of Music. . . great chance to review the fundamental make-up of sounds from the aspex of : notation/syntax, time signature, format.

Here’s a scanned copy of my notes. . . more will follow as I continue reviewing (and learning). So why the interest? Well I honestly grew up playing Brass (as 1st Slide Trombone) and played everything on the basis of slide placement and merely associating the notes by mere bi-produce of placement/sound/and visual placement of notes on the staff. You may laugh, but I could never tell you the difference between an A to Am on paper – only by playing. So I want to change that for (2) reasons.

I want to speak to what I’m playing in words, then the play/demonstrate approach. The other reason? So I can transition into the string-side of instruments. Particularly the Piano and Electric Guitar. Having both and not knowing how to play anything beyond chop stix or Mary-Had-A-Little Lamb disappoints me. I have wanted to play – the Mat Kearney, the Foo, and Ben Folds – for way to long, it’s time I learn to understand music once and for all.

Here’s to something new. . .    =    )

. . . and the update came sooner, than later. . .
I can now play the Mat Kearney Song ‘Chicago’ on the Piano.

  1. Verse (G, D, Am, and C) x 3
  2. Pre-Chorus : (G, D, Am) x 3 . . . then to C
  3. Chorus : (G, D, C) x 2
  4. Verse : (G, D, Am, and C) x 3
  5. Pre-Chorus : (G, D, Am) x 3 . . . then to C (repeat)
  6. Chorus (G, D, C) x 4
  7. Closing with G, D, Am, C

Look the lyrix up! And if you get a chance – catch one of his concerts. Just went to the HOB in San Diego (2) weeks back to watch him perform, along with opener – Vedera. Both artists will rock your sox off!

Enjoy    =   )    – J

It’s happening as we speak. . .  in email, fileshares, enterprise repositories, even social networks (FB or Twitter). People are exchanging data, information, ideas. And as we do – we likely give context, association, and meaning to user-to-user, or user-to-audience, collaboration. I’m talking about sharing data about data -> Metadata.

Facebook (FB) User ? A socially interactive form of metadata. Browse the People : status updates, friends/networks, pictures, and events. All the conversations, relationships, and interests are attributed to the content. Social metadata.

Business Scenario ? Glad you asked. If you’re delivering an email to your team you’re putting context around the message so the audience can understand what they have at the inbox : who is it from, the subject line, and what does the message body say about the attachment. All metadata? Kind of. Let’s break this down in a # of ways.

Information Breakdown

  1. Email Message – conversation from User to Audience
  2. Content – Metadata (who emailed it, subject, message body)
  3. Attachment – Unstructured Content (picture, excel file, etc)

These are all normal actions we understand and exercise on a daily basis.

Technology Breakdown

  1. Email Platform (lotus, exchange, gmail)
  2. Data Repository (hard drive, data file share, website)
  3. File Type (word, adobe, etc)

Fundamental compenents that make-up the communication process.

Now the big question. . . How would you manage that content : Delivery? Search? Share? Review the information breakdown(s) above and take a moment to think about it.

  1. How would I find a conversation buried in email? (Search by Sender, Subject line, or keyword and phrase)
  2. What about files that are stored on a team member’s workstation? What if he/she is out for the week on vaca? How will your team get that file, or know where it is?
  3. Welcome the new-hire. If he/she looks at a data file share they see a list of files and types. Where is that metadata, contextual meaning, and understanding? In those emails he/she never had.

You see how information breakdown works? Understand the technology layers involved? And how complex it is for users to retrieve the data about data?

That’s why I think solutions like Sharepoint will change the course for many users in the business environment. It enhances how we collaborate, centralizes the data, and helps that new user understand the data captured with context, association, and meaning. It changes our Technology Breakdown. Returning the purpose of email into a messaging system (not a content repository), you’re capturing metadata around a document (otherwise lost in email conversations that aren’t captured in the file repository), and the ability to find those and share those require pairing up emails with fileshare documents; there is no easy way to auto-connect the metadata and unstructured content (files) together.

Let’s take a document library for example. You have the ability to add columns (metadata categories) used to attribute characteristics to a document. (assuming the fields are required vs optional). When you upload documents into the library you are then requested to attribute the upload with context. (name, activity, strategic function, assigned to, versioning, etc). It’s no different than uploading a pix to a photo album on facebook, describing the album, adding captions to the pictures, and then tagging people in them.

In the instance that you’re a company uploading (tranferring) documents from a data fileshare, you are mostly applying a metadata framework with all your files. Think about how you’re storing content on the file share. You probably organize your folders by year, business area, projects, departments, etc. If you drill down through these folders to a particular file, take note of the folders you went through to find that file. If you want through something like this . . .

Customer > Operations > Department > Team > Project > Phase I > project_plan.pdf

Each Folder would be an example of a Sharepoint List Column you would create to translate the metadata principle into a function of filtering/sorting all files in a single list. I strongly encourage you to adopt this approach and DO NOT create folders in document library lists because you will cripple the intent and purpose of a sharepoint list in the first place.

You now have consolidation of content, captured metadata, and greater efficiency to finding (and learning) about the business you work in. And remember – It’s happening as we speak. . .  in email, fileshares, enterprise repositories, even social networks (FB or Twitter).

Think about how Sharepoint will help People exchange data, information,  and ideas. Embrace the technology and fabricate your collaboration framework into a solution that you can apply greater context, association, and meaning to collaboration. I’m talking about Sharepoint. Metadata. Folders vs Columns.

Another great article on metadata is with @maish at ->

– J. Wright


Get every new post delivered to your Inbox.