Setting Subproject Status Date with VBA

If you read yesterday’s post, you’ll remember that I was kvetching about how setting the status date in the master project didn’t set the status date for subprojects – and how this impacted both task updates and EVMS calculations in the master project.

I could have just left it there, but I figured I’d sit down with my newfound VBA skills and see if I couldn’t write a macro to update subprojects’ status date in a master project.

Well, I did and here it is:

Sub StatusDate()

    Dim SP As Subproject
    Dim ISMaster As Boolean
    Dim MasterStatusDate As String

    If ActiveProject.Subprojects.Count > 0 Then
        ISMaster = True
    Else:
        End
    End If

    MasterStatusDate = InputBox("Enter the status date for the project.", "Set Status Date", MasterStatusDate)

    For Each SP In ActiveProject.Subprojects
        SP.SourceProject.StatusDate = MasterStatusDate
        Debug.Print SP.SourceProject.Name
        Debug.Print SP.SourceProject.StatusDate
    Next SP

End Sub

Working with Master Projects in Project 2010

The first blog post I ever wrote was actually on this topic – as I struggled with the “insies and the outsies” of managing my own master project.  Seeing as I am currently proposing a master project solution to a client, I figured it was probably a good time to fire up my Hyper V machine and do a little research into how master projects work in Microsoft Project and Project Server 2010.

Note that 2007 may or may not be about the same.  Some of this seemed familiar to me, and some of it a little different than how I recollect 2007 working.  That being said, I never spent a whole lot of time with master projects in 2007, so couldn’t say if things have changed much.

This blog post is split into two parts:

1. Issues in Project Pro
2. Issues in PWA (Project Server)

Project Professional Issues

I am not entirely sure that “issue” is the right term to use here, but more “things to note.”  Since that doesn’t sound as catchy as “issue,” I guess that’s the term I’ll settle on.

Issue #1: Baselining the Project is Weird

In this first scenario, none of my subprojects have been baselined.  I have the master project open and select the option to baseline all.

I expand the sub projects and display the baseline fields to see if it worked…

…and it appears that only the subproject summary tasks were actually baselined.  Any subproject child task was not.  Expanding the subprojects doesn’t help either.

The trick appears to be that you need to highlight everything and then, when baselining, choose the option to baseline selected tasks.  (Note in the following example, that I only have one project expanded.)

That yields the following results.  See how the second subproject was not expanded, and therefore did not get updated.

Moral of the story: each subproject needs to be expanded, and the Set Baseline option must be applied to selected tasks to work.  Baselining each subproject individually works, but that won’t roll up to your master project baseline very well for things like cost or work values.

At the end of the day, it would seem to make sense to create the master project, then baseline from the master project while ensuring all subprojects are expanded and the selected tasks option is applied when setting the baseline.

Issue #2: Status Dates are Weird

If you’re using the status date functionality in Project (and you should be), then you’ll need to appreciate that status dates are a bit weird when it comes to master projects.

Essentially, the status date in the master project does not apply to the subprojects.  The opposite is not quite true however, as the status date in the subprojects definitely impacts the master projects.

To illustrate how status dates work in master projects, I have set the status date for the master project as “10/28/2011.”  Additionally, I opened Sub Project 1 and set the same status date.  Then I added a new custom field to each of the projects called TEMP_Status which is set to display the Status Date at the task level.  See the results:

So the status date is applied to the summary tasks but not the subtasks.  The implications of this include the fact that the Advanced Scheduling options may no longer apply to tasks updated within the context of a master project.

Issue #3: …Which (kind of) Breaks EVMS…

Veteran Microsoft Project users will know that EVMS doesn’t work without the status date as most of the EVMS calculations are a snapshot in time as of the status date.  In the following example, tasks have been progressed, but only the first subproject has been opened and had its status date updated.

 

The subproject summary task appears to be calculated properly, but the subtasks within each schedule are displaying the ACWP as of the project start – which is the default date for the status date.

As a result, the subtask figures do not total up to the amount displayed in the subproject level.  In the case of subproject 2, 600 + 600 + 280 + 280 decidedly does not equal 8,800.

Moral of that story: the subproject summaries calculate more or less correctly, but the subtasks do not – which makes the reporting a bit confusing..

(Stay tuned tomorrow for a bit of VBA code to address this issue.)

Issue #4: The Project Update Stops Working

Refresh your notes from the Set Baseline issue, because the same thing is at play in the Update Project dialog box.

If you use the Update Project button, make sure to set it to update selected tasks (not the entire project), then expand the subprojects and highlight all tasks to get it to work.

PWA Issues

Issue #1: Assignments Don’t Appear in PWA

So I created my master project.  I assigned resources, and then I proceeded on to create an assignment view within Project Server.  Interestingly enough, nothing showed up.

It turns out that assignment level data doesn’t appear within Project Server.

Here’s what the subproject looks like in Project Center:

…and the master project….

See?  No assignment level data.

Issue #2: The Project Name Keeps Changing

This actually appears to be an issue in Project Server – as opposed to a thing of note.  Presumably, it’s a minor bug that might cause some end user confusion but is probably manageable.

Here’s my default Tasks Summary view in PWA with each of the subprojects minimized.  Note the project names.

When I expand the project, see how the summary task changes name?

Minimize the project and the new name sticks (at least until the next time you access the view).

The name is being pulled from the Title field in the File Properties box within MS Project Professional.  Make sure to change the name there when the sub project file is created.

Once the name has been changed, publish the sub project and the issue shouldn’t happen again.

Issue #3: You Need to Show Subprojects in Project Center

Not really an issue, but from an end user perspective, make sure to train them to click on the Show Subprojects button in Project Center to actually show subprojects.

This always comes up as support questions in Project Server implementations.

VBA: Updating Assignment Level Fields from SQL

Rod Gill’s book on VBA for Project 2007 has been sitting on my bookshelf for a couple of years.  I finally decided to open it up again last week to see if I could use it to sort out some issues for a client prototype.

This post, and probably some upcoming posts will focus on specific issues I ran into that I was able to resolve, but that didn’t appear in the search engines at first glance.  In this post, I’ll talk about how to merge SQL level data into a Microsoft Project plan. 

There’re a lot of examples on the Web and in Rod’s book on how to add assignments and tasks from SQL, but it took a bit of finding to determine how to merge SQL data with the specific assignment in Project.

The Challenge

In this case, we have a pretty typical challenge.  The invoiced amount for a specific cost code is kept in a LOB application.  Whenever the user decides to update the schedule, they also need to import the invoiced amount, and use that as part of their status reporting metrics.

The Set Up

There’re a couple of ways of skinning this cat.  The way I decided to go about it was to create a unique ID for each of the assignments in the schedule.  I did this by concatenating a custom field, ProjectID and the Resource Initials for the resource.

Why Resource Initials, you ask?  Well, Resource Initials are one of the five or so fields that appear in the Resource Pool, but that also appear as task level data.  Off of the top of my head, the other fields that work in this way include the Resource Name, Resource Group (which I was using elsewhere), Resource Phonetics, and well, I think that’s pretty much it.

For the Project ID, I created a project level enterprise custom field in Project Server, then created a second task field called Project_ID_Calculated that equaled the Project ID and rolled down to the assignment level.

The assumption by the way, is that each resource is assigned once and only once in the project.

At the end of the day, this gives us a unique ID for each assignment, something I am storing in the Assignment_ID field.  Feel free to use your own field to assign a unique ID as works for you.

Creating the Assignment ID

Once I have that set up, populating the Assignment_ID field is a matter of including the following code in my macro.

    'Update Assignment ID as the concatenation of Project ID Calculated and Resource Initials'

    Dim T As Task
    Dim A As Assignment

    For Each T In ActiveProject.Tasks
        For Each A In T.Assignments
            A.Assignment_ID = A.Project_ID_Calculated & "." & A.Resource.Initials 'Assign new Assignment ID
        Next A
    Next T

 

Querying SQL

Assuming I have a SQL database up and running with data corresponding to these Assignment IDs, I then have to run the following code to create the connection. (Tip of the hat to Rod Gill for instructions in his book.)

    'Create connection to SQL table.'

    Dim Conn As New ADODB.Connection

    Conn.ConnectionString = "Provider=sqloledb;" _
    & "Data Source=SQLInstanceName;" _
    & "Initial Catalog=Staging;" _
    & "Integrated Security=SSPI;"

…and the following code to query the database and update the Project plan.

    Dim SQLInvoiced as String

Conn.Open

    For Each T In ActiveProject.Tasks
        For Each A In T.Assignments
            If Not Conn.Execute("SELECT Invoiced From dbo.SQLTable WHERE [AssignmentID] = " & A.Assignment_ID).EOF Then '...only for codes found in the db
                    SQLinvoiced = Conn.Execute("SELECT Invoiced From dbo.SQLTable WHERE [AssignmentID] = " & A.Assignment_ID).GetString 'Gets the actual data
                    A.Invoiced = SQLInvoiced 'Update Invoiced custom field for resource assignments
            End If
        Next A
    Next T

Conn.Close

…note the “If Not” clause to avoid errors potentially caused when the field is null or nonexistent in the SQL table.

Documenting Lookup Tables with Playbooks

It slices!  It dices!  Not only does it move Project Server settings from one environment to another, but it also provides an easy way to document some of the configuration settings you made in your environment. 

This is a trick that I’ve used for years.  Typically, I like to deploy a TEST/DEV environment pretty early in the deployment cycle.  Then I’ll work through that environment with different client groups to get it configured exactly how I would like it.

Sooner or later, I need to take that environment and roll it into some requirements document that I can leave behind when I leave the customer environment.  This is generally to avoid what I like to refer to as the “Planet of the Apes” deployment scenario where a couple of years later, everyone who implemented the system has left the company, and the folks who are left have been bequeathed with a system that nobody understands and that has been configured according to logic that nobody knows.

At that point, the customer will call in a consultant such as myself, and they’ll be forced to unravel the contrived logic that drove the original design – hypothetically.  I, of course, have never had to do anything like that….

So here’s how you can easily pull your Lookup Table values from the Playbooks tool….

First, run the Playbooks tool to generate an XML file of all (or at least most) of your Project Server settings.  Once you’ve done that, fire up Excel and open the XML document within Excel.  This kicks off the Open XML dialog box.

Choose the option to “Use the XML Source task pane.”  Navigate through until you find the Lookup Tables.  Select the options for the LT_UID and the LT_Name.

Drag those over to your Excel chart.

Highlight the two empty cells, right click, and choose XML > Import.  That will prompt you to open the file again, which then imports the UID and the Lookup Table name.

Now do the same with the Lookup Table values.

That gives you a table like this.

Add a field in the middle.

Now use a vlookup formula to populate the blank field with the data from the original table.  In my example the formula looks like this:

=VLOOKUP(A27,Table1,2,FALSE)

…and there you are.  I notice that I somehow ended up with a LU Table called “WokflowStages.”  Not sure what happened there, but I am pretty sure it’s user error.

Three Perspectives on Project Size

There is a discussion that comes up often in different client engagements: how do you define a project as “big?”  Something happened recently which caused me to reconsider different perspectives on project size.  You see, my daughter broke her arm.

When we saw a specialist to get the cast on, he informed us that she would need relatively minor surgery on her elbow before he could put the cast on.  What’s informative are the perspectives at play here:

1. The doctor does this thing all of the time.  He probably performs the same surgery 3-4 times a week.  To him, this is a small project.
2. This is my baby we’re talking about.  They’re going to knock her out and open her up.  To me, this is a large project (which requires a communication plan to ensure the stakeholders, i.e. grandparents, are all kept properly informed).
3. My daughter has no clue what she’s getting into.  She simply assumes it’s a routine doctor’s visit because that’s her only frame of reference.

…replace “operation” with “project” and you have an informative metaphor.  The answer to whether or not a particular project is “big” within your organization is really subjective and highly dependent on:

1. How does the proposed scope compare to a project scope that you are accustomed to delivering?   It’s small to the expert, but big to the dilettante.
2. Have you really assessed the scope of the project to identify whether or not it falls within the “usual” size of a project? (Clearly, my daughter hadn’t.)  How do you know the assessment was correct?  At what point would you revisit that assessment?

…or, to put this in a different, and perhaps more telling light, the project appears the same to the person at the level of conscious competence (the doctor) and the person at the level of unconscious incompetence (my daughter). 

Interesting side note: according to the doctor, the local Gypsy population typically opts out of this surgery.  Apparently, there’s a fair sized Roma population in rural Texas.  Who knew?  (Link to the Texas State Historical Association site on the topic.)

The BEI Report Revisited

A couple of weeks ago, I wrote up instructions on how to create a Baseline Execution Index Report.  It’s a pretty nifty concept, but in retrospect I think it was missing a key element, a cumulative record of the task backlog in the portfolio.

Here’s what I offered in the original post:

In that report, you’ll see that we do not display the cumulative backlog.  That could give you a misleading view of the portfolio, by implying that the later period of the report is on track, when it really isn’t.

In this report though, you’ll see that even though our BEI may be improving, we still have an accumulated backlog of tasks.

The Query

To generate this report, I had to rework my query.  It now looks like this:

SELECT     Set1.TaskIsMilestone, dbo.MSP_EpmProject_UserView.ProjectName, Set1.TaskFinishDate AS PlannedFinish, NULL AS BaselineFinish,
                      TaskActualFinishDate AS ActualFinish, DATEADD(day, 0, DATEDIFF(day, 0, Set1.TaskFinishDate + 1-DATEPART (dd,Set1.TaskFinishDate))) AS TotalDate, 1 AS CumActualSource, Null AS CumBaselineSource
FROM         dbo.MSP_EpmTask_UserView AS Set1 LEFT OUTER JOIN
                      dbo.MSP_EpmProject_UserView ON Set1.ProjectUID = dbo.MSP_EpmProject_UserView.ProjectUID
WHERE     (TaskBaseline0FinishDate <> 0) AND (TaskActualFinishDate <> 0)AND (TaskActualFinishDate < (GETDATE()-(DATEPART (dd,GETDATE())))) AND TaskIsMilestone = 0 AND TaskIsSummary =0  
Union ALL
SELECT     Set2.TaskIsMilestone, dbo.MSP_EpmProject_UserView.ProjectName, NULL AS PlannedFinish,  Set2.TaskBaseline0FinishDate AS BaselineFinish, NULL
                      AS ActualFinish, DATEADD(day, 0, DATEDIFF(day, 0, Set2.TaskBaseline0FinishDate + 1-DATEPART(dd,
                      Set2.TaskBaseline0FinishDate))) AS TotalDate, Null as CumActualSource, 1 as CumBaselineSource
FROM         dbo.MSP_EpmTask_UserView AS Set2 LEFT OUTER JOIN
                      dbo.MSP_EpmProject_UserView ON Set2.ProjectUID = dbo.MSP_EpmProject_UserView.ProjectUID
WHERE     (TaskBaseline0FinishDate <> 0) AND (TaskBaseline0FinishDate < (GETDATE()-(DATEPART (dd,GETDATE())))) AND TaskIsMilestone = 0 AND TaskIsSummary =0  
ORDER BY TotalDate

Which does a couple of things:

1. Eliminates the need to perform grouping in Excel – which breaks our pivot table cumulative calculations – by converting all dates to the first of the month.
2. Adds a value of 1 to the CumActualSource field for all completed tasks.  We will use this to keep our running total of tasks that finished.
3. Adds a value of 1 to the CumBaselineSource field for all baselined tasks.  We will use this to keep our running total of tasks that should have finished.
4. Filters out all entries for the current month.  As everything else is grouped by the month, showing partial results for the current month may skew the tail end of the chart.

Once you’ve got all of that in an ODC file, create the pivot table.  Configure it as follows:

Now, we need to create three custom fields.  Click on the pivot table, and select the option to create a new calculated field under the Options tab.

Field Name	Formula	Description
TargetSource	=1	Used to display the target BEI.
BEISource	= ActualFinish/ BaselineFinish	Calculates the BEI for a given time period.
BacklogSource	= CumBaselineSource- CumActualSource	Calculates the backlog of incomplete tasks (compared to the baseline)

The table now looks like this:

Change the names to something more user friendly, convert the BEISource field to a two digit decimal, and set the Backlog to a running total.

Add a PivotChart.  Select the column chart type.

Right click on the Backlog and Target series to set them as a line chart data series.  Set the Backlog to plot on the secondary axis.

Configure the primary Y axis as follows….

…and the X axis like this.

…and there you are:

Filter on a specific project to yield something like this:

Scheduling Milestones for Specific Days of the Week

Here’s a little trick that may come in useful when developing your schedules.  The scenario here is that you have a milestone such as key meeting or review or training kick off or go-live date, but that event has to happen on a specific day of the week.

Let’s take a typical consulting example.  Let’s assume that the consultant is traveling to the client site, and generally works Monday through Thursday on-site.  The hypothetical consultant returns home and works remotely on Friday.

Now let’s say that the consultant wishes to schedule a key internal review of the project work.  He doesn’t want to use precious on-site time to have that conversation, instead choosing to schedule that discussion on a Friday. 

We can first set up that discussion as a milestone.

Create a new calendar that we will call “Off Site.”  Set every day but Friday to be non-working time.

Apply the calendar to the milestone. Set the option Ignore Resource Calendar to “Yes” if you have a resource assigned.  That’s not required in this case, but it could be if the resource wasn’t scheduled to work on Friday, or as more often happens, the task falls on a weekend.

Note that the milestone seems to ignore the calendar.  This is because the milestone, having no duration, is not subject to the task calendar.

Now, set the milestone to a duration of 1 minute.  The task moves to a Friday.

Check the option to Mark the Task as Milestone.

You now have a milestone that is only going to happen on a Friday.

Have a whole string of tasks that have to start on a specific day of the week?  Just lead off with one of those 1 minute milestones, then add a bunch of successors.