Blogroll

Month: October 2011

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.

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:

Issues in Project Pro

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.

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
ForEach T In ActiveProject.Tasks
ForEach A In T.Assignments
A.Assignment_ID = A.Project_ID_Calculated & "." & A.Resource.Initials 'Assign new Assignment IDNext 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.)

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.

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:

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.

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).

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:

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.

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).

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:

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.