projectified-old

Work with the Timescaled Data Object in Project 2007

UPDATE:

I have updated the code in the file download link below. I don't have time to rewrite the post to explain the changes in full but I found an issue with the code that caused a problem. Basically, if the start date of an assignment slipped into the future then the code would not act on the original dates because it was setup to use the Assignment start date as the earliest point for acting on the assignment values. Well if the assignment moved (the baseline data was 'in the past') then the code would just not do what it was designed to do. The new code at the link below will work better. Again ALWAYS test your code in a bunch of different situations. This is a great object lesson in how a developer can get tunnel vision and write code for a specific situation that leaves out other possible situations. I was writing based on a quick situation I could create and recreate very quickly during the coding process and it obviously did not include the assignment start date moving. It only included the assignment finish date moving. :-)

Like all good VBA macros we have to start with a problem that needs to be addressed or a function that needs to be added. Our problem today will be the fact that if you add a new task to an existing baseline and there is a resource assigned to this task the Baseline Work for that assignment is not rolled up to the Resource level Baseline Work field. Once the task is added to the baseline the Baseline Work for the Resource does NOT show the additional Work added to the baseline by the new assignment. This is also true of the Resource level timescaled baseline work data. The VBA code we will review here addressed this issue and gives us an opportunity to look at how to use the TimeScaledData method to access the timescaled data for tasks, resources, and assignments.

The steps to reproduce this issue are as follows:

1. Create a task and assign a resource
2. Baseline the project
3. Create another task and assign the same resource
4. Add just this second task to the project by clicking Tools | Tracking | Set Baseline and selecting "Selected Tasks"
5. Go to the Resource Usage view and insert Baseline Work into the timescaled area

The example below shows how at the resource level Dwight still only has 64 hours of Baseline Work when in reality there should be 64 + 32. The timescaled area also shows only 8 hours of baseline work at the resource level for 19^th, 20^th, 23^rd and 24^th when there should be 16 for each of those days.

What is the TimeScaledData Method?

This method returns a collection of data that represents a specific type of data (Work, Cost, Baseline Work, etc) for a specific period of time (the duration of a task or assignment for example) that will be provided broken down by the specified time unit (days, weeks, months, etc). This collection can then be worked with to either read specific data about specific time slices in the collection or even to set specific data for a time slice in the collection. It is a very powerful feature of VBA but one that is not widely understood.

You can download the full macro here.

We will now review each section of the code and go over what it does:

The Variables

Dim R As Resource
Dim A As Assignment
Dim aBL As TimeScaleValues
Dim rBL As TimeScaleValues
Dim Counter As Integer

This section defines (or dimensions) the variables we will use.
R is an object variable that will represent one or more resources
A is an object variable that will represent one or more assignments
aBL is an object variable that represents timescaled data. It gets 'loaded' with data by using the TimeScaledData method. This object will represent the assignment timescaled values (hence the 'a')
rBL is just like aBL but it will represent the resource timescaled values (hence the 'r')
Counter is a variable that will be used to loop through the members of the collections when we need to access those values.

The Resource For…Next Loop

For Each R In ActiveProject.Resources
If Not (R Is Nothing) Then

Because we established above that 'R' was to represent a resource object we can use this variation of a For…Next to loop through 'each' resource (R) in the Resources collection of the active project.

The If…Then statement is testing to make sure that there are no blank lines in the resource sheet of the project. Blank lines cause a problem for VBA when dealing with the Tasks and Resources collection. If the loop above encountered a blank line an then the macro tried to access any property (the name, work or any other field for example) the macro would get an error. The test in the If…Then statement makes sure that the code 'under' it will only run if the object represented by 'R' is an actual resource.

Setting the Values to Zero

R.BaselineWork = 0

For Each A In R.Assignments
Set rBL = R.TimeScaleData(StartDate:=A.Start, EndDate:=A.Finish, _
Type:=pjResourceTimescaledBaselineWork, TimescaleUnit:=pjTimescaleDays, Count:=1)

For Counter = 1 To rBL.Count
rBL(Counter).Value = 0
Next Counter

Next A

The rest of our macro below will be recalculating what the Resource level baseline and timescaled baseline work data SHOULD be after a task is added to the baseline. This section of code is setting those values to 0 so that the code below can calculate the values correctly. This section shows us the first use of the TimeScaleData method.

The For..Each…Next here is looping through all the Assignments (all the 'A' objects) in the Assignments collection for the resource represetted by 'R'. It is then setting the 'rBL' variable to equal the collection of timescaled data defined in the R.TimeScalData method. It sets the start date of the collection to equal the start date of the current assignment (A.Start) and the finish date to equal the finish date of the current assignment (A.Finish). The collection will contain Baseline Work because we are setting the Type to equal pjResourceTimescaledBaselineWork. The timescale will be days and the Count will be 1, meaning that each object in the collection will represent 1 day. If count were set to 2 then each object (or if you prefer 'time slice') would be 2 days.

Next we have a For…Next loop that sets the value for Counter to be 1 through the number of objects (time slices) in the 'rBL' collection we just created. This loop is letting us move through each slice so that we can do something to it, either read it or write to it. The line that reads 'rBL(Counter).Value = 0' is setting the value for Timescaled Baseline Work for the time each timeperiod in the assignment (in this case days) for the resource represented by 'R' to equal zero. Let's say that the assignment represented by 'A' started on Day1 and finished on Day 5. This would mean that the 'rBL' collection would have 5 members (time slices) each representing 1 day. This means that the For Counter = 1 to rBL.Count would loop through 5 times. On the first loop through the rBL(Counter).Value represents the value of timescaled Baseline Work for the resource for Day1. On the second loop through it is Day 2 and so on. This sets the values for resource timescaled baseline work to 0 for the duration of the assignment represented by 'A'. This will get repeated for each assignment for the resource represented by 'R'.

You may ask yourself why the code above is setting the Resource timescaled values to 0 since we are using the Assignment start and finish dates. You might wonder why we are not just setting the Assignment values to 0 since the code above would have us in many cases setting the same days values to 0 several times (in the case of overlapping assignments.) This is a good question. The answer is that the problem in Project that we are solving is one where the assignment values for baseline work are correct but the resource values are incorrect. If we reset the assignment values to 0 then we would have no idea how to set the resource values. We need the assignment values intact. The price we pay for this is needing to loop through some days at the resource level a few extra times.

Adding up and Setting the Correct Values

For Each A In R.Assignments

R.BaselineWork = R.BaselineWork + A.BaselineWork

Set aBL = A.TimeScaleData(StartDate:=A.Start, EndDate:=A.Finish, _
Type:=pjAssignmentTimescaledBaselineWork, TimescaleUnit:=pjTimescaleDays, Count:=1)

Set rBL = R.TimeScaleData(StartDate:=A.Start, EndDate:=A.Finish, _
Type:=pjResourceTimescaledBaselineWork, TimescaleUnit:=pjTimescaleDays, Count:=1)

For Counter = 1 To aBL.Count
If Not (aBL(Counter).Value = "") Then
rBL(Counter).Value = rBL(Counter).Value + aBL(Counter).Value
End If
Next Counter Next A

This section is in large part a repeat of the previous section with the addition of the setting of the 'aBL' collection that will allow us to read the assignment level timescaled Baseline Work data and insert it into the 'rBL' collection. So to that end you see the Set 'aBL' and Set 'rBL' lines. They are setting the collections for both assignment and resource based on the assignment start and finish because we are reading from the assignment. Next we have a slightly expanded 'For Counter' loop from the last section. It contains an If…Then statement we use to test to make sure that value of the aBL object currently being evaluated does not contain a null value. Project sets the Baseline Work for the default nonworking days of a calendar (Saturday and Sunday) differently than non-default nonworking days (days the user sets to nonworking via the Change Working Time dialog.) Saturdays and Sundays get set to Null values while nondefault nonworking days get set to 0. Testing to make sure that the value of the rBL object is not null saves us from getting an error.

The next line is the key to the whole thing. It looks at the value of the Assignment timescaled Baseline Work and adds it to the current value of the Resource timescaled Baseline Work. It does this for each timeslice in the aBL collection. This is repeated for each assignment at which point the resource timescaled baseline work is equal to the sum of the timescaled baseline work for each of the assignments for the resource.

Closing Out

End If
Next R
End Sub

The End If here is the closing of the "If Not (R Is Nothing) Then" test at the beginning of the routine.

The Next R is the Next for the "For Each R In ActiveProject.Resources" line in the beginning. Once the execution hits this line it has completed the rest of the code for one resource and if there are more than one resource it will repeat until it has run through for each resource.

The End Sub ends our macro.

Expanding on the Basics Above

The example above works with assignment and resource timescaled baseline work. But the same concept can be used to work with any of the timescaled data in Project with just a few changes to the code we worked with. These concepts can be applied to working with task timescaled data as well. For example if we wanted to access task timescaled cost data the code might look like this:

Set taskBL = T.TimeScaleData(StartDate:=T.Start, EndDate:=T.Finish, _
Type:=pjTaskTimescaledCost, TimescaleUnit:=pjTimescaleDays, Count:=1)

This code would return a collection (in the object variable taskBL) of timescaled task cost data where each member of the collection was 1 day in duration starting with the start of the task and ending on the finish date of the task. If we wanted it in months instead of days we would change 'pjTimescaleDays' to 'pjTimeScaleMonths'. If we wanted it just for the first day of the task we would set the StartDate and EndDates to both be equal to T.Start.

One of the great things about VBA is that you don't have to remember all of the parameters or constants you need to use to make this kind of method work. The VBA Editor prompts you if you used named parameters. As soon as you enter the open parentheses after TimeScaleData the editor tells you the parameters for the method.

You could just type A.Start in the first position and then a comma and A.Finish but good coding practice dictates that you type StartDate:=A.Start. If you do this it makes your code easier to read and gives the advantage of being prompted with the constants for things like Type and TimeScaleUnit like below. This way you do not need to remember the many, many constants available to you.

So Why Would I Use This?

You might ask yourself why this would be important? Always a good question. This kind of code is essential if you want to look at or edit specific time slices of data about tasks, assignments or resources. This would include code to integrate a timesheet system with Project. For sure most companies that are integrating systems like SAP will be using Project Server and using the PSI but a 'timesheet system' might be as simple as a set of Excel spreadsheets you distribute to your resources for them to enter status data. If they do it in a timescaled way (number of hours of work done per day or per week then this code is exactly how you would base a macro to pull that data from Excel and put it into Project in a timescaled way. The TimeScaleData method is also the best way to create your own custom exports from your Project files into other systems that need data on a day by day basis. These methods are shown here using VBA native to Project 2007 but the same concepts can be used within Visual Basic to create more robust applications.

Disclaimer

This code is presented here for educational purposes only. Before such code is used in a production environment it should be seriously tested for all possible situations to ensure that it performs as expected. It's presentation here does not imply any guarantee or warranty on this code. Further, this code is not supported by Microsoft in any way. I am presenting it here as a means of education only.

Project Blog Search

Treb is a genius. He poked around with creating macros within the Microsoft Live search engine to create a special Live search page that searches just the blogs and other community sites that revolve around Project and Project Server. You can find it here.

Dieter Moving on but Replaced Well...And a New Project Server Blog!

Dieter is moving on to another position within Microsoft. Good for him, not so great for the rest of us! He has been a great and positive force within the Project Team for years! He will certainly be missed. He does mention his replacement Keshav though. This is good news for the community and the team. Keshav has been around for a long time and will be able to fill the big shoes for sure.

He also mentions his blog replacement: The Project 12 Blog. Welcome to the blog world Lidaine!

Glen is on a Roll!

Glen is in a groove riffing on Target Process’s post.

It may be the Target Process just needs to get a better marketing plan, cause from where I sit MSFT Project and other "big name" project management tools do everything they claim they do. They'll need some other way to differentiate their product.

I think this is the key point. I looked at their tool and I would not put them in the same category. Project is a scheduling tool and the Target Process tool seems to be a work management\team collaboration tool ala Basecamp. I don’t think these two tools are really competing. I dont see the average customer of their tool having to weigh their product versus Project. There is almost no feature overlap other than that they both contain things that could be called “Tasks” and those tasks are assigned to human beings.

All I can think of as the rationale for this is that it is fun to bash MS and agile dev people love to bash non-agile methods. This is a no brainer: a two bash for the price of one deal! They got to bash MS and non-agile methods at the same time! :-)

 

News of Project's Death with Regard to Software Projects Has Been Exaggerated. Again!

 So the guys over at Target Process just informed the world that Project is based on the waterfall process and is therefore unsuitable for use in managing a software project! LOL Wow that is news to me and the thousands of others that have used to manage iterative software development projects! :-)

I posted a comment on their post that talked about my disagreement and have had this response in draft form ever since and had forgotten about it until Glen posted on the subject. It was not until I read Glen’s post that I realized that Target Process markets a product that competes with Project! This certainly casts the post in a different light. When I first read it  I did so with the thought in mind that the writer was just not understanding how scheduling software really works or what the basis is for building and indeed using such software. But knowing that the poster works for a company that competes with Project it now just smacks of an intentional distortion of the facts with the end goal of boosting sales of their product. While this certainly may NOT be the case (I really want to believe that this was not the thrust of their post) it certainly starts to look that way.

Microsoft Project is not based on “Waterfall”
It just isn’t. Neither are the scheduling engine components of Primavera, Niku (sorry I meant CA), Artemis, Planview or any other major scheduling application that I have seen. You can MAKE them do waterfall. You can make any application do waterfall. I can make Basecamp do waterfall and you could likely make Target Process’s tool do waterfall too no mater how centered it’s design is on Agile. Hell, I can make 3x5 cards like the ones used in XP\Scrum do waterfall!! Waterfall is a process for planning a project. It is not tool specific. It is project environment specific. Waterfall can work as a method for scheduling software projects. Not many in my opinion but they are out there. The method you use to plan your work (be it a software project or the creation of marketing materials or the building of a dam) should be context sensitive. If you have the requirements up front and they will not likely change through the course of the project then waterfall might be the thing. In a case like this an iterative approach might add loops that are a waste of time given the locked down nature of the requirements. If you are building something there the requirements are fuzzy and likely to change over time then the obvious choice is a method that is iterative at it’s core (whichever method that might be for your case.)

As I mentioned in my comment on the original post I agree that the Help file that comes with Project should address other planning processes. In fact in my Outlook outbox (I’m on a plane right now without Internet access) there is currently an email to several people on the Project development team suggesting just that. However, the mere fact that the Help only mentions waterfall type methods does not mean it is based on waterfall nor does it mean that you cannot use Project to do iterative projects. To suggest otherwise is like saying you cannot toast a bagel in your new toaster because the instructions only mention bread and English muffins. :-)

Project IS “Connected”
These comments really just seem like marketing spin to me and have been covered in my comments on the original post and in Glen’s post. Suffice to say that Project Server addresses these comments have little to do with the suitability of Project as a scheduling engine for software projects. A deck of 3x5 cards used to organize SCRUM or XP tasks are not ‘connected’ either since only one person can ‘hold’ them at a time so give me a break. :-)

 

Iterative Design\Build Loops in the Configuration of Project Server: First Pass and Initial Thoughts

I have always been a fan (and in fact a major proponent) of the idea of iteration in the long term deployment and roll out of Project Server across an organization. Myself and Russ Young of QuantumPM were one of the first ones to talk about this in the Enterprise Project Management Deployment Guide way back during the Project Server 2002 days. Our work in this area was based on ideas that were widely held not only in the Project Server consulting world but in almost all enterprise software organizations. These iterations were fairly large scale ones dealing with the staged, incremental roll out of Project Server to the many organizations within a larger organization as opposed to the massive one-time dropping of Project Server on the entire company. This approach started with a small but representative (as much as possible) group or small organization and then using the lessons learned from this group the system would be adjusted (new fields added, new views, server architecture adjusted, etc) and then another group would be added. This process would repeat until the entire organization was using the tool. Each iteration added not only new people but also new or fixed functionality in the form of views, security, process, etc. This method is now used by virtually every consulting company doing Project Server deployments.

The kind of iteration I’m talking about here happen on a much, much smaller scale and deal with the actual design and production of the configuration of the Project Server system for a given organization. Instead of dealing with the successive roll out to new organizations I'm now talking about the iterative design\build loops around adding new fields, views, security configurations within any one of those larger iterations.

Understanding of the Environment and the Problem(s) to be solved
This includes the collection of what the users want the system to do for them in addition to the observation of the system at work so that you as the consultant can help make suggestions.

Configuration\Build
From the knowledge gained so far the system is configured to the best of your ability. Fields, views, security, etc.

Simulations
most organizational uses of tools like Project Server revolve around the capture and editing of project data and then in the use of that data to make specific business decisions that effect the ability of the organization or project team to function with regard to projects. I look at kernel, the very core of Project Server’s use in an organization as the viewing of data via views or reports by decision makers. Everything else that project Server does supports this in some way or another. With this driving principle in mind the check or test of the configuration\build ‘stage’ is a simulation where decision makers use the tool as it is currently configured to simulate the decision making processes they must go through to manage their work\projects\portfolios\etc. These simulations have the decision makers using the tool in a conference room with the design team and other decision makers up on a projector. Talking through the decisions they need to make and what data they would need to see to make them. How it would need to be displayed, grouped, sorted, how it should be secured, who can see it, who can’t see it, etc.All effort toward a better understanding of how the tool will be used and what decisions it will be used to support.

Revisiting the build stage…
At this point we jump into a loop of Configuration\Build and Simulation loops that successively improve the suitability of the Project Server configuration to the needs of the decision makers that will be using it to run the business\projects\work.  If this means new views or fields then so be it. If it means configuring and rolling out the use of timesheets to those doing the work or the development of custom OLAP cube building routines to provide company specific data in Portfolio Analyzer views then that is fine too.The point is that the design\configuration happens based on simulated use of the product as it is configured currently.

___

This process can take place either prior to the system ‘going live’ or as part of the addition of new users to the system. Also, a variation on this process happens on an ongoing basis while the system is being used by the organization. As people touch the system and use it as part of their project management\portfolio management processes their new experiences and new expectations become, often, new requirements for the system. The administrator of the Project Server system should constantly collect suggestions and new requirements and evaluate them with the team for inclusion in the system. This constant updating of the product’s configuration ensures that it keeps up with the most current processes in use within the organization.

___

Obviously this is not rocket science and I'm not claiming to have ‘invented’ it by any means. I’m sure that many others are using similar processes for their Project Server (or other PM software) roll outs. I should also mention that this process is only suitable where the organization is able to devote several weeks (at least 3 or 4) to this initial design process. Often organizations that are rolling out Project Server have only a very short time to do their initial design and configuration. In cases like this the process laid out here may require too much time. But it should be said that while this process requires more time than many methodologies it is much more likely to produce a usable system. Shorter term processes get you up and running very quickly but at the cost of fully understanding the processes and needs of the organization.

This is just an initial layout of rough thoughts on this process. It is by no means final.

 

OK, Maybe Not Worst but Now Certainly the Most Incomplete...

The other day I posted about a Churchill quote and Glenn has some interesting insight to it’s value.

Glen mentions the Apollo space program as being built upon a series of failures to eventually produce a great series of successes. I agree with Glen in his comments that and quotes about learning from failures. That is certainly a key part of any project, no, any LIFE.

However…the quote mentions nothing of learning from mistakes and failures. It instead defines success itself as the enthusiastic movement from failure to failure. I think we can all agree that the movement from failure to failure that Churchill describes is not only NOT a definition of success but it is in fact, without a major (MAJOR!) success at the end, a pink slip waiting to happen.

So that said, it seems that to make the quote what Glen is suggesting would mean adding something like: “…leading up to a success based on lessons learned from the failures provided of course that the positive value of the success outweighs the negative value of the string of failures.”

New Project Blog

Larry Duff from Microsoft has a blog that is mostly about developing in and around Project and Project Server. It is a MUST read for those doing Project Server development, particularly when the next version hits. The insights he will have since he has been messing with the new PSI stuff will be HUGE!

The Project Conference

The conference, as usual, was very well done. The only substantial complaint I heard was that the most popular sessions filled up too soon and some were not repeated. I can understand this but for sure it is hard to know which ones will be in the highest demand. You can have a good idea going in but there are always surprises once the sessions get going.

Treb and Dieter have already gone into the new features so I will not repeat their fine work.

I am excited most about these though:

• Segmented databases (Working, Draft, Archive and REPORTING)
  This is going to make life very nice for those working with custom reports and also solve some issues around archiving
• Project as a WSS application
  Having Project Server as an integrated part of the WSS ‘framework’ will make building customisations much easier and addresses some of the UI consistency issues that were around from before
• Server-side Scheduling Engine
  VERY cool stuff. The ability for a PM to accept timesheet\status updates and have those updates put right into the schedule without the PM having to be at a machine with Project Pro installed! It will also allow for custom applications to add data and have the project recalculated based on those changes without having to automate Project Pro.
• Deliverables
  This is the feature where a task from a project can be ‘published’ and then subscribed to from other projects reducing the need for the ‘old’ cross project dependency links between tasks. A PM ‘publishes’ a task and then any other PM can ‘subscribe’ to that and make it the predecessor to one of his own tasks in his project. Basically it is creating a cross project link without having to have both projects open at the same time to do it.

How UMT gets integrated into the product is another obvious area of excitement. I am very anxious to see how it will play out.

More later…

Blogging the Project Conference

Starting tomorrow the Project Conference in Seattle is on and I (along with others) will be blogging the high points. I will try to distill what I’m seeing and hearing into something like readable nuggets for those not able to attend.