The Importance of Consistency

This post comments on a number of elements of Project Professional 2013:

• Finish Variance
• Resource Substitution Wizard

Finish Variance

If I describe a number, x, as being the difference between two number, A and B, then I take it to mean that:

x = A – B

Finish Variance is defined in Microsoft Project Professional as being the difference between the Baseline Finish date of a task or assignment and the current Finish date:

In my mind that implies that:

Finish Var = Baseline Finish – Finish

However, the bottom of the tool tip shows:

Which I would describe as the difference between the Finish date and the Baseline Finish for a task or assignment.

Which is it?

A quick check of a Gannt chart shows that it is, in fact the second option:

Finish Variance = Finish – Baseline Finish

Why does it matter?

It matters because it is important that everyone understands terms like this to mean the same thing. Does a positive finish variance mean that a task is ahead of schedule or behind schedule?

It is apparent from the Gantt chart above that a positive finish variance means that a task is behind schedule, and that a negative finish variance means that a task is ahead of schedule.

Its when questions around this sort of thing appear in tests and exams that it becomes important to remember the definition.

Finish Variance = Finish – Baseline Finish

If Finish Variance > 0 then task is delayed

Resource Substitution Wizard

In Project Professional 2010, connected to Project Server 2010, it was possible for project managers to assign resources to tasks and then specify whether that assignment was a Request or a Demand, depending on their view of which resources could carry out that task. Running the Resource Substitution Wizard would then attempt to level resources.

The Resource Substitution Wizard is no longer available in Project Professional 2013, so one would assume that there would be no need to be able to specify a Request or Demand field for resource assignments. However, if we look at a Task Form this is exactly what we see:

The assignment of a named resource (e.g. Chris, or PM2) can be set to Request or Demand. However, the assignment of the generic Business Analyst can only be left blank.

It seems that selecting any of the options under the R/D column has effect on anything in Project Server 2013.

Using VBA To Check Task and Assignment Fields

I’m currently working on a project to create a macro for MS Project. The intention is that this macro will  fire either on Save or on the click of a button and will check to see if certain fields have been filled out for tasks and assignments. Any missing fields will be reported back to the user.

We use Project Server to generate a “Daily Log” where we can record work against tasks that have been assigned to us. In order to make the completion of the daily log as easy as possible for the end users and for the plan owner (not to mention our finance department), we want to be able to check that most of the required information is in place in the first place.

Task fields

It is relatively easy to loop through tasks and check for task level fields.

The easiest thing to do is to work with all the tasks in the current collection of tasks in the active project.

First we need to check that the task is actually a valid task (i.e. not a blank row) and that it is not a summary task.

Then we can populate a number of variables with the required values.

Dim ts As Tasks
Dim t As Task
Set ts = ActiveProject.Tasks
Dim tCPSScope As String     ' Task field In CPS Scope
Dim tAtCPS As String        ' Task field At CPS
Dim tTaskCat As String      ' Task field  Task Category
Dim tBillCat As String      ' Task field  Billing Category

For Each t In ts
    If Not t Is Nothing Then
        If Not t.Summary Then
            ' Check task level fields
            tCPSScope = t.GetField(FieldNameToFieldConstant("In CPS Scope"))
            tAtCPS = t.GetField(FieldNameToFieldConstant("At CPS"))
            tTaskCat = t.GetField(FieldNameToFieldConstant("Task Category"))
            tBillCat = t.GetField(FieldNameToFieldConstant("CPS Billing Category"))

 

Here we can see that VBA has a way for us to get the value of task fields.

Assignment Fields

Reading assignment level custom field values is a bit more tricky. If the name of the custom field is one word (i.e. there are no spaces), then according to various other web sites I have looked at, for an assignment a; “a.fieldname” will do the trick. Unfortunately, if the field name does contain spaces then this method will not work.

Since I was unable to resolve the issue by working in the background I decided I had to work in the foreground (i.e. by working with a view).

To make sure that I covered all the task and could work with all the required fields I wanted to do the following:

• Use a Enterprise view
• Remove any filters
• Display all tasks

This can be achieved using the following code:

'Switch to CPS Timesheet view
ViewApplyEx Name:="CPS Timesheet", ApplyTo:=0
TableApply Name:="CPS Timesheet"
' Remove Filters
FilterApply Name:="&All Tasks"
'Expand to show all tasks
OutlineShowAllTasks

 

We can now loop through all the rows in the view. This is one area where I have used a bit of a hack. I haven’t found an easy way to count the number of used rows in the current view. However, I can count the number of tasks in the current project and I know that we rarely assign more than one person to a task, so the following should give me a high enough limit to count to.

tCount = ActiveProject.Tasks.Count
tCount = tCount * 2 ' Assume no more than 2 assignments on each task

' Loop through all rows
For r = 1 To tCount

 

The view I’m using is a Task Usage view and therefore contains the names of tasks as well as the resources assigned to those tasks. What I need to do is to differentiate between Tasks and Assignments.

Now I can use the SelectTaskField to select a specific cell in row r and column specified by a field name.

SelectTaskField Row:=r, Column:="Name", RowRelative:=False

 

SelectTaskField

A couple of important points about this command:

• Column – this should be the real name of the column as stored in Project, not the name shown in the view. Hence the use of Column:=”Name”, rather than Column:=”Task Name”
• RowRelative – the default value for this is True and will therefore select a cell r rows down from the currently active cell. Therefore, this should be set to False to ensure that it is always row r that is used.

Having selected the cell in the “Name” column of row r we can read the text in the cell using:

sTest1 = ActiveCell.Text ' Read the text in the Task Name column

Task.Name

It is also possible to query the name of the task associated with the currently active cell.

sTest2 = ActiveCell.Task.Name   ' For the current cell get the associated task name

And we can therefore compare sTest1 with sTest2. If they are the same then we are looking at a Task. However, if they are different then we are looking at a resource assigned to a task and the current row is an assignment. We can therefore pick up cell values from other columns in the row and compare them to expected values.

Final Outline

So, the final outline of the code looks like this:

Sub CheckAssignmentsBlogEx()
Dim r As Long    ' Row counter
Dim sTest1 As String ' String to test - taken from Name column
Dim sTest2 As String ' String taken from task name - compare to string 1
Dim iTID As Integer     ' Task ID
Dim FRS As String   ' Report String

' Project Server Custom Fields to test as Assignment level
Dim TBC As String, Billable As String, REx As String, RCR As String
Dim tCount As Integer   ' Count the number of tasks in the project

tCount = ActiveProject.Tasks.Count
tCount = tCount * 2 ' Assume no more than 2 assignments on each task

' Loop through all rows
For r = 1 To tCount
    SelectTaskField Row:=r, Column:="Name", RowRelative:=False
    ' RowRelative is false to ensure the code selects row number r, not r rows below the current one
    sTest1 = ActiveCell.Text ' Read the text in the Task Name column
    
    If sTest1 <> "" Then ' Only proceed if the cell contains some text (i.e. is not blank)
        ' Get the task ID for the selected cell - used for comparing assignments to tasks and creating final report
        iTID = ActiveCell.Task.ID
        If Not ActiveProject.Tasks(iTID).Summary Then   ' Check that the current task is not a summary task
            sTest2 = ActiveCell.Task.Name               ' For the current cell get the associated task name
            If sTest2 = sTest1 Then
                ' the task name is the same as value in the name column. Therefore,
                ' this is not an assignment
                    ' Do what we need to do with task level information
            Else
                ' the two strings are different and therefore,
                ' this is an assignment
                    ' Do what we need to do with assignment level information
            End If
        End If
    End If
Next r


End Sub

RAG Indicators

Problem

On a recent training course the client expressed the following requirement:

They have six project level RAG (Red, Amber, Green) indicator fields. Some of these are selected manually by the project manager, and some are calculated automatically from the Project’s Schedule, Work and Cost information.

From the six RAG indicators they want to generate one overall RAG indicator for the whole project using the following logic:

• If all the RAG indicators are Green then the overall RAG is green
• If there is one Amber RAG then the overall RAG is green
• If there are two Amber indicators then overall the RAG is Amber
• If there are three Amber indicators then overall the RAG is Red
• If there is one Red indicator then overall the RAG is Amber
• If there are two Red indicators then overall the RAG is Red

 

Possible solutions

At first glance this seemed like a realitively straightforward problem with a simple solution:

1. Calculate or input all six RAGs.
2. Work out how many of the six individual indicators are Red or Amber
3. Apply the supplied logic to calculate the overall indicator

 

Switch Statements

My first thoughts were to work out all the possible variations for the six indicators and then use an IF function or a Switch function to find which variation was the current one.

So I started to investigate the possible variations;

RAG1	RAG2	RAG3	RAG4	RAG5	RAG6
Green	Green	Green	Green	Green	Green
Amber	Green	Green	Green	Green	Green
Green	Amber	Green	Green	Green	Green
Green	Green	Amber	Green	Green	Green
Green	Green	Green	Amber	Green	Green
Green	Green	Green	Green	Amber	Green
Green	Green	Green	Green	Green	Amber
Amber	Amber	Green	Green	Green	Green
Amber	Green	Amber	Green	Green	Green
Amber	Green	Green	Amber	Green	Green
Amber	Green	Green	Green	Amber	Green
Amber	Green	Green	Green	Green	Amber
Green	Amber	Amber	Green	Green	Green
Green	Amber	Green	Amber	Green	Green
Green	Amber	Green	Green	Amber	Green

 

I got this far and decided that any If function or Switch statement that looked at all the possible options would be far too long and that there had to be an easier way.

(I think that there are a grand total of 729 different combinations – 3^6. It should be possible to create a number of switch statements to search through all of these and might be worth doing to prove a point but I haven’t got that much time).

Substitute Function

The next option that came to mind was to use a trick that I had discovered for excel that uses the Substitute function.

The Substitute function has the following syntax:

=Substitute(Text, Old_Text, New_Text, Instance_Number) where:

Text = The text or reference to a cell containing text in which you want to substitute characters

Old_Text = The existing text you want to replace

New_Text = The text you want to replace the Old_Text with

Instance_Number = If ommitted, all instances of Old_Text will be replaced

Thus, if we have a string of text “abcabc” and we replace every instance of the letter “a” with nothing (i.e. “”) then we will end up with “bcbc”.

Since we know that the original string is six characters long and that the length of the new string can be found using the LEN() function we can calculate the number of letters that were replaced by substracting the result of the LEN function from six.

Putting this into practice for this exercise is done as follows in excel:

1. Create a named range to hold the text “Red, Amber and Green”. This named range was called RAG

1. Apply data validation to a range of six cells, to force them to choose from the named range created in the previous step.
   Row 1 are just labels.

 

1. For each RAG column, select a value for the RAG indicator

1. Use the LEFT function to extract the first letter of each of the six indicators

1. Combine all of the initial letters to create one six character RAG string

1. Use the substitute function described earlier to count the occurrence of each letter “R”, “A” and “G” in the RAG string

1. Use an IF function to apply the required logic to the number of Rs, As and Gs to calculate the overall RAG indicator

This worked perfectly in Excel.

The only problem is that the Substitute functionis not available in SharePoint, and therefore not available in Project Server, and therefore this solution can not be applied to the customer’s requirement.

I have documented it here in the hope that someone who uses Excel may find it useful.

Scores

I presented my findings so far to the client, who was impressed with the effort that I had put in to this problem overnight. The client then wondered what would happen if they assigned scores to the RAG values such that:

Red = 2
Amber = 1
Green = 0

I therefore applied the following logic:

1. For each RAG indicator, use an IF function to calculate an individual score

1. It is then possible to Sum all the scores to give a total

1. And then create another IF function to generate an overall RAG score

I’ve tested this on Project Server and it works fine.

Microsoft Office Survey

Introduction

As a Training Consultant for Corporate Project Solutions I spend most of my time delivering training on Microsoft Project, Project Server and SharePoint. In my previous role at European Electronique I delivered training and supported clients using Microsoft Office applications such as Word, Excel, PowerPoint and Outlook.

For several years now I have had a personal mission to enable people I come into contact with to get the most out of the technology that they work with. This ranges from quick tips on how to format headers in Word (Ctrl+Alt+1 for Heading 1) through to creating taxonomies for SharePoint libraries and lists.

Moving forward I would be interested in finding out how people use the various Microsoft technologies that they have available to them and I hope to create a series of articles that will help with the most common areas.

To get started I have created a short survey that I hope people will fill in. The aim of the survey is to record Microsoft product use by demographic data and identify the most common applications used.

The Survey

First some demographic information.

Office Version

Office Applications

Outlook?

Project?

SharePoint?

I shall post the results when I have a decent number of responses.

Future posts in this series will cover the following topics:

• Hints and tips for Word
• Hints and tips for Excel
• Simple Dos and Don’ts for using Project
• Ideas on how to use SharePoint for document management, creating an Intranet and creating a reporting solution

Please let me know if there are any other MS Office related topics that you would like me to cover.

Critical Path Analysis

ES	Dur	EF
	Task Name
LS	TS	LF
	=LF-ES-Dur

Early Start = EF of predecesor

Early Finish = ES + Duration

Late Start = LF-Duration

Late Finish = LS of Successor

TS =LF-ES-Dur

Hammock Tasks in Microsoft Project

What the ** is a Hammock Task?

A question that I have been asked a couple of times now is this:

Is it possible to create a task that spans the duration of the project and which has a duration that changes automatically if the overall duration of the project changes?

An example may be a management task.

The answer is yes, it is possible, and the way it is achieved is through the use of a hammock task.

We start with the above scenario.

Task ID 1 (Project Starts) is a milestone at the beginning of the project.

Task ID 4 – Task ID 7 (Tasks 1 to 4) make up the body of the project, with Task 1 having the Project Start as a predecessor.

We need Task ID 2 – the management task to start on the same date as the start of the project and end on the same day as the end of Task 4, no matter when that may be.

Paste Special – Link

The trick is as follows:

1. Select the Start of Task ID 1 and Copy it
2. Select the Start of Task ID 2 and, from the Edit menu, select Paste Special
3. Select Paste Link and click OK
4. Select the Finish of Task 4 and Copy it
5. Select the Finish of Task ID 2 and, from the Edit menu, select Paste Special
6. Select Paste Link and click OK

The Gantt chart should now look like this:

 

Now, if we need to change the duration of one of the main tasks, say increasing Task 2 from 2 days to 10 days

The duration of the Management task automatically increases

More information can be found in the Microsoft Knowledge Base, http://support.microsoft.com/kb/141733

Project Work Equation – a Mnemonic

During the Traing the Trainer course last week, we talked about mnemonics and acronyms to remember information. I have therefore been trying to come up with something to help me remember the Project Work Equation.

 

How about this:

Projects are all about getting things done. Therefore a short way of saying that I , and my team, work together to achieve the required outcomes is to say:

 

We Do

 

This could be written as

 

W e D U

 

and still sound the same

 

Now all that is needed is to expand this into word:

 

Work Equals Duration x Units

 

Work = Duration x Units

 

And there you have it.

Microsoft Project Work Equation and Task Types

Microsoft Project Work Equation

Introduction

When assigning resources to tasks, Microsoft Project uses a simple formula to calculate how long each task will take, given the specified number of resources. This formula can be written as:

Duration = Work / Units

Where:

Duration

The calculated amount of time it will take to complete the task.

Work

The amount of time (usually expressed in hours or days) that it would take one person to complete the task if they were working at it full time every day.

Units

If a person is said to spend 100% of their time at work then this is the amount of time that they spend working on this project. Units are expressed as a percentage or a decimal. Thus if they are available to spend their whole time working on task then Unit would be 100% or 1.0. If they are only available to spend half their time on a project then Unit would be 50% or 0.5

Thus if it takes one man working full time 8 hrs to build a wall the duration of the task will be:

Duration = work / unit = 8 hrs / 1 = 8 hrs

Duration = 8hrs

Whereas if he is only available to spend 50% of his time on the task

Duration = work / unit = 8 hrs / 0.5 = 16hrs

Duration = 16hours or 2 days

And if there are two men working full time then

Duration = 8 hrs / 2 = 4 hrs

Task Types

Microsoft Project gives us some control over what happens to tasks when we change Duration, Work or Units. There are three task types:

• Fixed Units
• Fixed Work
• Fixed Duration

In these instances the word Fixed means that, of the three variables, Project will keep one constant, so that if we change the second Project will automatically change the third to ensure that the formula remains correct.

For example if a task is Fixed Duration, with a Duration of 8 hours, Work of 8 hours and Unit of 1.0, then we know from the above formula that

Duration = Work / Unit

8 = 8 / 1

If we now change the number of units available to work on the task to 0.5 then the amount of Work needs to change, because it is a fixed duration task.

Since the duration must stay the same at 8hours

8 hours (Duration) = ? hours(Work) / 0.5

Work = 8 (Duration) * 0.5 = 4

Work = 4 hours

Summary

The relationships between the task types and the impact of changing one variable on the other, is shown in the following table.

Task TypeYou ChangeDurationUnitsWork Project will change Fixed DurationWorkWorkUnitsFixed UnitsWorkDurationDurationFixed WorkUnitsDurationDuration

Project Levelling

I went down to London on Tuesday to deliver some Project 2007 training to a customer of ours. As usual there was some confusion when we started  talking about Levelling.

By complete coincidence I received this newsletter today that helps to clarify what all the options are all about.