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.

VBA: Updating Assignment Level Fields from SQL

Tracking Actual Costs in Microsoft Project 2010 (Part IV)

If you read the first three parts of this series, you’ll see that I talked about the three options to control how Actual Cost is tracked within a Microsoft Project schedule.

image_thumb1

  1. Actual Costs are always calculated by Project – Covered in parts I through III of this series.
  2. Edits to Total Cost Will Be Spread to the Status Date – Today’s topic.  See below for more information.
  3. Default Fixed Cost Accrual – Covered in Part I, but to recap: This field sets the default for all new tasks, i.e. if I set the Default Fixed Cost Accrual to Start, then all new tasks created will default to the Start setting.  Existing tasks will not be impacted.

Let’s close out this series with a review of the effects of checking or unchecking Edits to Total Cost will be Spread to the Status Date on our schedule.  As I see it, there are at least two scenarios that we will need to examine:

  1. Updating Actual Cost for the first time on a task.
  2. Updating Actual Cost for the second (or third or fourth) time on a task.

What I want to investigate with the latter scenario is whether or not Project is sophisticated enough to handle differences in tracking timephased Actual Cost, i.e. if I book X in reporting period 1 and Y in reporting period 2, will that be tracked as X and Y, or would it average out the two over the period of both reporting periods.  (The answer: yes, it that sophisticated.)

Revisiting the first post, my goal in examining this functionality is to assess suitability of leveraging it to support an integration with a LOB accounting system.

 

The Initial Update

For this scenario, we’re going to create a new project.  The project will have a single task that will be four weeks in duration.  We will report on the task weekly.  Generally, this would violate my own rule of thumb on the maximum duration of a planned activity, but since this is for demonstration purposes, I’ll waive that restriction.

In case you’re wondering what that rule of thumb is, I generally try to follow the principle not to make a single task exceed the length of a single reporting period.  If I report weekly, then typically I would try to avoid making a task longer than a week or so.

Other rules you could apply:

  1. The 8/80 rule which says no task should be smaller than 8 hours of duration, or larger than 80 hours in duration.
  2. The 1/10 rule, which I believe I learned from Eric Uyttewaal, which states that no task should be less than 1% of the overall project duration, or greater than 10% of the overall project duration.

…but I digress.  To this 4 week long task, I add a resource with a rate of $1,000/day, translating into a total cost for the task of $20,000.

image

For the first test, I will leave the Edits to Total Actual Cost will be Spread to the Status Date option unchecked.

image

I enter the $4,000 cost in the Task Actual Cost field, and display the results in a split view with the Task Usage view at the bottom:

image

See how the Actual Cost is accrued in the first day of the task?

Now, I check the Edits to Total Actual Cost will be Spread to the Status Date option, and try the same experiment:

image

See how the Actual Cost is spread over the first week of the project?  In the Usage View at the bottom, I see an Actual Cost of $800/day for the first week.

Ok, so that was simple.  Toggling that option on or off will result in the cost being booked to the first time period of the project or spread using what’s been referred to as the “peanut butter approach” across the duration of the first reporting period.

The Second Update

For the second update, we set the status date to one week later and try the same experiment.  This time, for the second week, I book $10,000, bringing the total Actual Cost for the task to $14,000.

With the option unchecked…

image

…the total Actual Cost is booked to the first day of the task.  In fact, the update overwrites the value that was there before and simply resets the value on the first day.  Simple enough.

…and with the option checked….

image

Project allocated the additional $10,000 to the second week of the task, i.e. $2,000 per day.  Nothing was changed on the days from the first week.

So the moral of the story?  The option actually does pretty much what it says it does.  Even better, if I set up my integration to dump the actual cost from a LOB application on a weekly basis, then, assuming the status date is configured properly, the cost will be allocated to each of the timephased periods correctly.

Tracking Actual Costs in Microsoft Project 2010 (Part IV)

Tracking Actual Costs in Microsoft Project 2010 (Part III)

Yes, I know what you’re thinking.  At this point, I probably should be arm wrestling Brian Kennemer for the title of who“endlessly obsesses over Microsoft Project” more.  (And Brian, watch out, I’ll be looking for you in Phoenix next year.)  In this exciting post, I’ll talk about how Actual Cost tracking is impacted by the project status Date.  Woohoo!  Sure to be a fascinating beach read to close out the summer.

This kind of reminds me of Take Your Daughter to Work Day a couple years back.  I brought my daughter to work and showed here what a consultant does.  Needless to say, she was bored stiff.

Manually Adjusting Actual Costs for Tasks with Multiple Assignments

So my goal with this post is to wrap up my treatment of manually editing Actual Cost for in progress tasks, specifically to see how Project allocates Task level Actual Cost to multiple resource assignments.

So I set up a simple project with a single task and two assigned resources.  Each resource is assigned to a 10d task at a cost of $1.000/d.

image

I change the Task Actual Cost to $10,000, and interestingly enough, the tool appears to allocate that evenly to each resource.

image

Very nice.  What about if the resources are unevenly allocated.  In the next test, I allocated one resource at $10,000 and one resource at $5,000.

image

Again, I apply the $10,000 to the Task Actual Cost field…

image

…and Project appears to allocate the Actual Cost according to the percentage that each assignment contributes to the Task total cost.  Again, well played Microsoft Project team: a subtle, but useful function that probably doesn’t get used all that much.

Minor caveat though.  When I hit Ctrl-Z to undo the changes, it looks like it undoes the task update, but not the resource update.

image

Next up….modifying the Status Date to impact cost accrual.

Tracking Actual Costs in Microsoft Project 2010 (Part III)

Tracking Actual Costs in Microsoft Project 2010 (Part II)

Continuing in the thread of documenting more on Actual Cost calculations than you ever wanted to know, let’s look at what happens when we manually edit the Actual Cost for a task on which a resource is actually assigned.

For Those of You Just Joining Us…

For review, what we identified in the last post is that each task appears to come by default with two assignments (even when no resource has been assigned), i.e the assignment when there is no assignment – perhaps demonstrating another of those peculiar intersections of Zen and Microsoft Project. 

One of those non-assignments tracks the task Fixed Cost, and one of those non-assignments tracks variable costs.  When there are no variable costs, that second assignment appears to do double duty by tracking adjustments associated with manually editing the Actual Cost field.

Below is an Access query against an MDB file derived from an MPP file with a single task and no assignments. See the two non-assignments, one not assigned to a resource called “Task’s Fixed Cost” and the other not assigned to “Unassigned?”

image

Tasks with Single Assignments

Now let’s take a look at what happens when we actually do add assignments.  Again we’re using my basic project schedule for illustration purposes.

image

For comparison purposes, here’s what my Access query looks like against that project.

image

Now let’s assign a work resource to the task.  I will set the resource cost to $1,000/day.  I also set the resource name as “Work Resource.”

image

Going back into Access, that looks like this:

image

Note that I now have a new unassigned assignment against the project summary task.  Not quite sure what’s happening there, but maybe I’ll come back and take a look at that in a later post.

Going back into Project, I make sure that the Actual Costs Are Always Calculated by Project option is unchecked.

image

Now I manually set the Task Actual Cost to $5,000.  Let’s take a look at what that does in Project.

image

First off, we see that the Actual Cost was allocated to the Work Resource.  I now see an Actual Cost of $5,000 on the resource – although the amount was not decremented against the Remaining Cost, so now I have a Total Cost of $15,000.  Fixed cost is still $10,000, so we can see that Fixed Cost + Variable Cost = Cost, or $10,000 + $15,000 = $25,000.

But let’s say that I don’t want the Total Cost to go up.  I want the Total Cost to remain the same.  Perhaps work was performed ahead of schedule.  Perhaps my resource incurred extra costs.  For whatever reason, I want to manually adjust my Actual Cost, but keep my Total Cost at the original $20,000 estimate.  So I change Total Cost to $20,000.  Interestingly enough, the Fixed Cost was decremented to $5,000, and the resource Cost remains at $15,000.

This means that any adjustment to the Task Actual Cost gets applied to the Resource Cost, whereas an adjustment to the Task Total Cost gets applied to the Task Fixed Cost.

image

Drop it into Access and here’s what I see.

image

This is where things get interesting.  Let’s try this on a task with no Fixed Cost.  I again assign my $1,000/d resource.

image

I set the Actual Cost to equal $5,000 and reset the Total Cost to equal the original $10,000.  That yields this:

image

See how the Fixed Cost is now suddenly showing a negative number?  That’s a bit weird.  The numbers all kind of work out, although now my resource is showing with a cost of $15,000 whereas before he only had $10,000.

Going back into Access, we see this:

image

So again the Actual Cost is added to the Remaining Cost for the resource.  When we modify Task Total Cost back to the original value, the offset is booked to the Task Fixed Cost, i.e. the reduced Task Total Cost appears as a negative value in the Task Fixed Cost.

Preliminary Conclusions

Based on these experiments, I might draw the following conclusions about projects where the setting Actual Costs Are Always Calculated by Project is unchecked.  The following assumptions are made based on a process where the user manually enters (or imports) Actual Cost data and then manually enters (or imports) Cost data to correct for the offset, i.e. a two step process.

  1. The Task Cost field will be accurate.
  2. The Task Actual Cost will be accurate.
  3. The Task Remaining Cost will be accurate.
  4. The Task Fixed Cost field will be incorrect, as it is adjusting for the Actual Cost number that was manually entered.
  5. The Resource Cost field may be incorrect, as it will include the Actual Cost manually entered plus the Remaining Cost.
  6. The Resource Remaining Cost field may be incorrect, as it will include the Actual Cost manually entered plus the Assignment Total Cost.

EVMS Implications

Thinking through the implications of this phenomenon on EVMS calculations, I find that it looks like EVMS will no be impacted.  Here’s a quick rundown:

 

  1. BCWS (PV) – is calculated based on the baseline cost values.  Presuming that the project is in fact baselined prior to manually editing Actual Cost fields, this value should still be valid.
  2. BCWP (EV) – is also calculated based on the baseline cost values.  Presuming that the project is in fact baselined prior to manually editing Actual Cost fields, this value should still be valid.
  3. ACWP (AC) – In theory this could be impacted by these values, insofar as the negative value in the Fixed Cost field may offset the Actual Cost incurred during the performance of the task.  In reality, this does not seem to be the case.  When I plot the projects out using the EVM Over Time Report, the ACWP appears to work fine.  I don’t see any of the negative values appear in the report.  It would appear that there is some logic built into the tool to preserve the ACWP values on tasks marked complete with negative Fixed Costs.  If needs must, I may revisit that specific mechanism in a future post.

Next up….how does this phenomenon impact tasks with multiple resource assignments?

Tracking Actual Costs in Microsoft Project 2010 (Part II)

Tracking Actual Costs in Microsoft Project 2010 (Part I)

I remember an incident in college when my old roommate, Neil, came back to the apartment and announced his new data entry job.  In his words, his new boss pointed at a stack of paper, then at the computer and asked him to “make them one.”  Well, that’s what I am looking at right now, except instead of a pile of paper and a computer, I am looking at a LOB accounting application and the Microsoft Project desktop application.

First order of business: in true epistemological fashion, identify what I know, what I don’t know, and what I think I know about how Actual Cost is calculated within Microsoft Project.  It turns out that there’s actually quite a bit that I didn’t know about the topic – or that I thought I knew which was actually wrong….hence the next couple of blog posts.

Review: Fixed vs. Variable Costs

Let’s kick this discussion off with a review of how costs are calculated in Microsoft Project.  Essentially, there are 2-5 task level fields that need to be considered:

  1. Cost (also sometimes displayed as “Total Cost”)
  2. Fixed Cost
  3. Actual Cost
  4. Remaining Cost

For what it’s worth, note that Actual Fixed Cost is a field that is available in the Task Usage view but not on a Task view.  Not sure why, but ok.

The rough calculations may be summed up as:

  1. Actual Cost + Remaining Cost = Total Cost
  2. Total Cost – Fixed Cost = Variable Cost (which is not an available field), where Variable Cost is the cost associated with the resource assigned to the task if there in fact is an assignment….

So the key takeaway from this discussion is that Variable Cost + Fixed Cost = Total Cost.  The corollary to that statement, is that if Variable Cost equals 0, then Total Cost – Fixed Cost also equals 0.  That statement is not true…well, sort of not true in some specific circumstances.

Configuration Options

Next, and this honestly was news to me, there are actually some configuration options which affect how Actual Cost is tracked.  In fact, there are three of them:

image

  1. Actual Costs are always calculated by Project – by default, this is turned on.  When this option is checked, actual cost is calculated by Microsoft Project.  In order for the user to manually enter Actual Cost data, the task would have to be 100% completed.   If the option is unchecked, users may enter an Actual Cost at any point in the task prior to completion.  This option is of particular interest to me as my tentative goal is to integrate this field in some fashion with an accounting application, i.e. to import the Actual Cost data from the external application.
  2. Edits to Total Cost Will Be Spread to the Status Date – This will be the subject of its own blog post, so stay tuned (for more Actual Cost excitement)!
  3. Default Fixed Cost Accrual – which really doesn’t impact Actual Cost, but it does impact how Fixed Cost is allocated across the task, so probably worth mentioning.  This field sets the default for all new tasks, i.e. if I set the Default Fixed Cost Accrual to Start, then all new tasks created will default to the Start setting.  Existing tasks will not be impacted.

For this and the next post, we’ll take a look at what that first option does.  To illustrate this, I have created a simple project with two tasks.  Each task is 10 days in duration and has a Fixed Cost of $10,000.  The project is baselined.

image

Actual Costs Are Always Calculated by Microsoft Project is turned on.

image

When I try to edit the Actual Cost, I find that I cannot.  The field is locked while the task is in progress.  If I mark Task 1 as 100% complete, the Actual Cost field will autopopulate with the Fixed Cost value I entered at the beginning.

image

Once the task has been set to 100% complete, I can manually go in and revise the Actual Cost for the completed task.  In this case, I change the Actual Cost from $10,000 to $15,000.  Note that the total Fixed Cost for the task is also adjusted upward to $15,000.

image

So far so good.  Everything is functioning as it should.

But what happens if you wish to update the Actual Cost in real time before the task is actually completed?  This would be the case if you (1) had tasks that extended longer than a specific reporting period or (2) wanted to disassociate the in-progress task from the automated calculation (which is essentially % Complete X Fixed Cost).

Let’s uncheck the Actual Cost calculation option.

image

The first thing we notice is that modifying % Complete does not in fact update the Actual Cost field.

image

…but I can manually edit the Actual Cost field at any stage in the task progress.  Hypothetically, I might do this if the Actual Cost for the task was actually being pulled from another application or spreadsheet.  In the below example, I set the Actual Cost to $7,500.  See how the different fields are calculated.

image

What’s worth noting is that the Remaining Cost is not decremented as I might have expected.  In this case, we had a Cost of $10,000 and an Actual Cost of $7,500.  I would expect the Remaining Cost to go down to $2,500.

That is not the case.  Instead, the Total Cost is increased to $17,500, i.e. the Fixed Cost + the Actual Cost.  What’s also noteworthy is that the Fixed Cost remains at $10,000.  The Total Cost field should display Fixed Cost + Variable Cost, where the Variable Cost is the cost associated with a resource assigned to the task.  But I don’t have a resource assigned to the task, so where is that cost being stored?  Where is the $7,500 within the system?  Call out the detectives (or since this is Texas, the horse-mounted posse), we have a missing value….surely, the varmint couldn’t have gotten very far.

To hunt down our elusive value, we fall back on an old trick that I’ve used in the past to troubleshoot schedules and/or figure out what’s going on behind the scenes.  We export the project file to an Access database, thus exposing some of the internal calculations to further scrutiny.

To export to Access, select the Visual Report option under the Project tab.  Select the Save Data option in the resulting dialog box.

image

Once I have the data in Access, I can build a quick query to look at the project assignments.

image

The query yields something like this:

image

Interestingly enough, I see assignments, even though I definitely have no resources in the schedule and certainly have not assigned resources to any tasks.  My conclusion from this is that behind the scenes, the costs for tasks are actually tracked in phantom assignments.  Those assignments lurk under the radar and are responsible for the behavior observed in the Fixed Cost fields when the Actual Cost is manually entered.

Hence, when I entered $7,500 as the Actual Cost for Task 1, I can see that was recorded in the “Unassigned” Assignment.  This then explains where the Total Cost of $17,500 comes from, $10,000 from the Fixed Cost and $7,500 from the “Unassigned” Assignment Variable Cost gives me $17,500..

So I have a hidden variable cost.  Next up….the plot thickens…

Tracking Actual Costs in Microsoft Project 2010 (Part I)