Project

The "Analyze Timescaled Data In Excel" add-in which ships with Microsoft Project has a couple of limitations, the first is that it is not easy to find, and the second is that it is task-based only, so if you want to export resource data you are out of luck. In this post I'll show how to write your own code to export resource timescaled data and use what you have learned to export almost any sort of timescaled data you can think of.

What is Timescaled Data?
Project is different from other applications like Excel because it has a dimension of time. Tasks have values for work and cost, but also contain the time dimension in the form of duration. On a specific task, the amount of work or cost may vary during the task instead of being spread evenly across the task. Because of this users sometimes need to view this information by day or week or hours. Within project they do this using the task usage view or the resource usage view and setting the timescale the way they want to see it. But sometimes they want this data outside of project and when they try and cut and paste, they find that it is not a simple matter. The view can not simply be copied and pasted. So to do this you need help from VBA.

Using VBA to Export Data
Underneath the surface of project is a powerful programming language called Visual Basic for Applications (VBA). It can be used to do just about everything that you can do manually in Microsoft Project but it can also do things which would be quite tedious or difficult to do manually. This example presumes you know have some understanding of VBA and if you don't I suggest you read the articles I've posted here (link to programming archive). VBA can open and operate other Microsoft Office applications. In this case we will open Excel and write data into it.

The TimeScaleData Method and the TimeScaleValues Collection
The TimeScaleData method is used to get a collection of timescaled values from tasks, assignments or resources which you can iterate through or read. The syntax for TimeScaleData method is a bit complex so it needs some explanation before we get into writing our code.

expression.TimeScaleData(StartDate, EndDate, Type, TimeScaleUnit, Count)

Where expression is one of the following:
An Assignment or Assignments Collection, A Resource or Resources Collection, A Task or Tasks Collection.

StartDate is the start date for the data. The twist here is that if the date you provide falls within an interval you are requesting, the StartDate is rounded to the start of that interval. For example if you are using TimeScaleUnits of months and enter a date in the middle of the month, StartDate will "round down" to the start of the first day of the month.

FinishDate is the finish date for the data. Like StartDate it rounds, but in this case to the end of the time interval you are using.

Type is the type of data. If you leave out this parameter the default is to return work. Even if you want work it is generally best to specify the type instead of leaving it blank. There is a long list of the defined types at the end of this article but to illustrate the type here are a few examples:

• pjAssignmentTimeScaledCumulativeCost
• pjTaskTimeScaledPercentComplete
• pjResourceTimeScaledWork

TimeScaleUnit is used to set the size of the time slice. By default weeks are used, but it is always good practice to set it explicitly. The possible values are:

• pjTimescaleYears
• pjTimescaleQuarters
• pjTimescaleMonths
• pjTimescaleWeeks
• pjTimescaleDays
• pjTimescaleHours
• pjTimescaleMinutes

Count is the final parameter. It controls how many timescale units are grouped together. Use it if you want to group the data by a timescale which is different than the ones available. For example if you wanted your data by half years, you could set TimeScaleUnit to pjTimescaleMonths and use a Count of 6.

The TimeScaledData method returns a TimeScaleValues collection. This is a collection which contains all of the timeslices and their values. We use the TimeScaleData method on an object and use the resulting TimeScaleValues as the source of our data.

Our Export form is built around this method. The key operation is: Set TSV = r.TimeScaleData(tbStart.Value, tbEnd.Value, TimescaleUnit:=cboxTSUnits.Value, 1) Now all we need to do is build a form which will supply the correct parameters and which will export the data to Excel.

Using a Form in VBA
Many of the examples on this site or my other site (link to masamiki.com) don't ask the user for much information, but the TimeScaleData method has a number of parameters that must be supplied. And some of those parameters are selected from a list. Because of this we need to move from the simple input box to a form. A form can have a number of different controls on it. This example is kept very simple. It has Start Date and End Date text boxes. Units and Hours or FTE combo boxes and a button which will run the code to export the data. A more fully developed version could have selections for Resource, Assignment or Task and could also have a list of all possible data types.

Here is the form I put together:

Behind the form there are a number of subroutines. The first one runs when the form is first shown.

Private Sub UserForm_Initialize()
'set the start date textbox value
tbStart = ActiveProject.ProjectStart
'set the end date textbox value
tbEnd = ActiveProject.ProjectFinish
'call a subroutine to set values for Units box
fillTSUnitsBox
'call a subroutine to set values for the hours or FTE box
fillFTEBox
End Sub

The routine to fill the Units box:

Sub fillTSUnitsBox()
'sets Units constants
Dim myArray(5, 2) As String
myArray(0, 0) = "Days"
myArray(0, 1) = pjTimescaleDays
myArray(1, 0) = "Weeks"
myArray(1, 1) = pjTimescaleWeeks
myArray(2, 0) = "Months"
myArray(2, 1) = pjTimescaleMonths
myArray(3, 0) = "Quarters"
myArray(3, 1) = pjTimescaleQuarters
myArray(4, 0) = "Years"
myArray(4, 1) = pjTimescaleYears

cboxTSUnits.List = myArray
'use weeks as default value
cboxTSUnits.Value = 3
End Sub

The routine to set the Hours/FTE box

Sub fillFTEBox()
'sets choice of FTE or Hours
cboxFTE.List = Array("Hours", "FTE")
'sets to hours
cboxFTE.Value = "Hours"
End Sub

The code which runs when the button is clicked

Private Sub btnExport_Click()
exportResourceUsage
End Sub

And finally at the heart of it all the exportResourceUsage subroutine:

Sub exportResourceUsage()
'first define our variables
Dim r As Resource
Dim rs As Resources
Dim TSV As TimeScaleValues
Dim pTSV As TimeScaleValues
Dim i As Long, j As Long
'define excel variables
Dim xlRange As Excel.Range
Dim xlCol As Excel.Range
Dim xlRow As Excel.Range
Dim xlApp As Excel.Application

'open excel and set the cursor at the upper left cell
Set xlApp = New Excel.Application
xlApp.Visible = True
AppActivate "Microsoft Excel"
Set xlBook = xlApp.Workbooks.Add
Set xlsheet = xlBook.Worksheets.Add
xlsheet.Name = ActiveProject.Name
Set xlRange = xlApp.ActiveSheet.Range("A1:A1")

'start writing column headers
xlRange.Value = "Resource Name"
Set xlRange = xlRange.Offset(0, 1)
xlRange.Value = "Generic"

'use the dates from the project summary task TSV to set column headings
Set pTSV = ActiveProject.ProjectSummaryTask.TimeScaleData(tbStart.Value, tbEnd.Value, TimescaleUnit:=cboxTSUnits.Value)
For j = 1 To pTSV.Count
Set xlRange = xlRange.Offset(0, 1)
xlRange.Value = pTSV.Item(j).StartDate
Next j

'go to first cell of next row
Set xlRange = xlRange.Offset(1, -j)

'loop through all resources and write out values
Set rs = ActiveProject.Resources
For Each r In rs
If Not r Is Nothing Then
xlRange.Value = r.Name
Set xlRange = xlRange.Offset(0, 1)
If r.EnterpriseGeneric Then
xlRange.Value = r.EnterpriseGeneric
End If

Set xlRange = xlRange.Offset(0, 1)

Set TSV = r.TimeScaleData(tbStart.Value, tbEnd.Value, TimescaleUnit:=cboxTSUnits.Value)
'loop through all timescale data and write to cells
For i = 1 To TSV.Count
If Not TSV(i).Value = "" Then
'convert to FTE if FTE is set
If cboxFTE.Value = "FTE" Then
Select Case cboxTSUnits.Value
Case 0 'years
xlRange.Value = TSV(i).Value / (60 * ActiveProject.HoursPerDay * ActiveProject.DaysPerMonth * 12)

Case 1 'quarters
xlRange.Value = TSV(i).Value / (60 * ActiveProject.HoursPerDay * ActiveProject.DaysPerMonth * 3)

Case 20 'months
xlRange.Value = TSV(i).Value / (60 * ActiveProject.HoursPerDay * ActiveProject.DaysPerMonth)

Case 3 'weeks
xlRange.Value = TSV(i).Value / (60 * ActiveProject.HoursPerWeek)

Case 4 'days
xlRange.Value = TSV(i).Value / (60 * ActiveProject.HoursPerDay)

End Select
Else
'if not FTE, work hours are written
xlRange.Value = TSV(i).Value / (60)
End If
End If
Set xlRange = xlRange.Offset(0, 1)
Next i
End If
Set xlRange = xlRange.Offset(1, -(TSV.Count + 2))
Next r

'some minor excel formatting of results
xlApp.Rows("1:1").Select
xlApp.Selection.NumberFormat = "m/d/yy;@"
xlApp.Cells.Select
xlApp.Cells.EntireColumn.AutoFit
xlApp.Activate
End Sub

To keep this readable I've left out all error handling and the like. It is meant as an example of how to use the TimeScaleData method and read from the TimeScaleValues collection.

If you hadn't noticed, the code which runs when the button is clicked contains only one command. It is possible to put the whole exportResourceUsage subroutine code under the handler for that button, but by putting it in a separate subroutine, it is easier to reuse the subroutine and keep the code readable.

Now, we have the form and the code (rightclick here to save a copy of the form to your computer). The next step is getting the form to display. To do this we need to write a single line of code.

Sub showExportForm()
exportResourceTimescaledData.Show
End sub

This macro can then be assigned to a button on your toolbar. Clicking the button runs the macro which shows the form.

The TimeScaleData Type Reference
There are a large range of TimeScaleData types available and which can be used to produce everything from Earned Value S-charts to cashflows or resource availability. The types possible depend on whether you are looking at Assignment, Resource or Task data. For example, Percent Complete is only available when looking at a task or task collection. WorkAvailability is only relevant to resources.

The constants to specify the Type always follow the same format, beginning with "pj" then the type of object you are referencing (Task, Resource or Assignment), then "Timescaled" and finally the data you are looking for. For example you would use pjTaskTimescaledDataActualCost for actual cost for a task, but pjResourceTimescaledDataActualCost for costs of a specific resource.

The following are available for Tasks, Assignments, and Resources

• ActualCost
• ActualOvertimeWork
• ACWP (actual cost of work performed - use in Earned Value calculations)
• BaselineCost (and Baseline1-10 cost)
• BaselineWork (and Baseline1-10 work)
• BCWP (budgeted cost of work performed - used for Earned Value calculations)
• BCWS (budgeted cost of work scheduled - used for Earned Value calculations)
• Cost
• CumulativeCost
• CumulativeWork
• CV (cost variance - used in Earned Value)
• Overallocation
• OvertimeWork
• RegularWork
• SV (schedule variance - used in Earned Value)
• Work

The following are available for Assignments and Resources only:

• PeakUnits
• PercentAllocation

The following are available for Resources only:

• RemainingAllocation
• UnitAvailability
• WorkAvailability

The following are available for Tasks only:

• ActualFixedCost
• CPI (cost performance index)
• CumulativePercentComplete
• CVP
• FixedCost
• PercentComplete
• SPI (schedule performance index)
• SVP

Last week was too wet to go out, but this Sunday morning we managed to catch a view of the Bay Bridge just before sunrise. This is the East span of the Bay Bridge. This cantilevered section is being replaced by a new cable-stayed span which is supposed to be more earthquake resistant.

You can see two of the piers for the new span in the shot below:

With a global stock meltdown on the rise, it seems a good time to launch my new occasional series of business poetry. Sorry, no rhymes or limericks today, but send them in and I'll publish it in the next round.

Deadwood
They are cutting the tall old trees
and the creatures in the underbrush
run searching for a home

Calling an All Hands
The leader claims
turning a battleship
in the open sea
requires 10.8 miles
It must suck to be a battleship

Procrustes
And tell me again
about your problem?
Old Procrustes asks.
I think we have
the solution.

Today is the shortest day of the year and for many cultures there is a celebration around this time, all springing from the fact that the light of the sun is returning to the world.

Microsoft has released Service Pack 1 for the Microsoft Office 2007 Suite.
Download it here:
http://www.microsoft.com/downloads/details.aspx?FamilyId=9EC51594-992C-4165-A997-25DA01F388F5&displaylang=en
The fixes cover a bunch of different applications and platforms, so it will take some consideration in rolling it out.

Good luck!

How the tables have turned. At the Seattle Project Conference, Microsoft hemmed and hawed about the release of Service Pack 1 mumbling about March and April. At the Madrid Project Conference word is that they came out and stated it will be December 11... this year. One can only hope that this was hastened by the questions posed at the Seattle conference, but in any case it is good news for those who are suffering.

The snarky among us would say, "Install immediately, it CAN'T be any worse!" but I say, get those test cases ready.

The Instr function is used to find out if one set of characters (string) is contained in another. It can be used in VBA macros (in Word, Excel, Project etc.) and also in Microsoft Project and Microsoft Project Server Custom Field Formulas. In Project this can be used to find out if a task name contains some special coding. For example perhaps you have used a special naming convention to separate time tracking tasks from other tasks and you need to roll them up separately. By using Instr you don't need to have the coding in a specific position. You can put it at the beginning, middle or end of the name and Instr will still find it.

Instr is also useful in string manipulation because it returns the position of the first occurrence of a string in another string. Using it in conjuntion with the left function you can strip out leading characters.

The InStr syntax is pretty simple:

start is optional. The default is to start at the first character, but if you want to skip the first character then you can set it to another value. This could be useful for looking for the second occurance of the string you are looking for. You could feed in the result + 1 of another instr function to see if the string occurs again. Using this recursively you could count specific characters.

string_to_search_in is the string that will be searched.

string_to_search_for is the string to search for.

compare is optional. By default it does a text compare so most likely you can leave it out. The valid choices are: vbBinaryCompare, vbTextCompare and vbDatabaseCompare. I'm not even sure what vbDatabaseCompare is so don't worry about setting compare unless you are doing something a bit more advanced.

Here are a couple of examples:

• InStr(1, "This is a time tracking task", "tracking") would return 16.
• InStr("This is a time tracking task", "is") would return 3.
• InStr(10, "This is a time tracking task", "t") would return 11.
• InStr("This is a time tracking task", "abalone") would return 0.

From the examples, you can see when it CAN'T find what it is looking for it will return 0. This is an important point to remember. The typical test I'd use in a custom field formula would be to test the return from instr in an iif formula. Maybe something like this:

I visited Las Vegas for the first time this last week and my first impression was that it is a city that works very hard to keep you inside. Finding the exit to a casino is an adventure in itself. Walking along the "Strip" with its broken pavements, escort touts flapping hooker cards and caged-in walkways only encourages you to stay inside. And they spend a lot of money on making the inside attractive. Much of it is not to my taste, but the Chihuly light fixture in the Bellagio is quite wonderful.

Like an oyster, the pearl inside is more attractive than the shell, and like an oyster there is some risk in consuming it.

So... the good news is when pushed for an answer in front of his boss, Mike Angiulo finally gave a date for the long awaited release of Microsoft Office Project Server 2007. The bad news is that the date is in March or April of 2008, more than a year after the release of Project Server.

Details and a critical (but I'm hearing accurate...) write up of the announcement and the Project Conference can be found here

I hope that Mr. Angiulo will exceed expectations and deliver early. It would be a celebration for applause.

This jelly fish spends its life in a glass box in the Monterey Aquarium.

I spend my life in a box that has fabric panels that you can stick pins in.

The jelly fish gets more admiration.

But it is basically the same feeling of weightless otherworldliness we must both experience when swimming at night.

Miscellaneous notes on aquarium photography:

• Many museums do not allow tripods or monopods.
• Go mid-week when there are no crowds.
• Hold the camera close against the glass to avoid reflections.
• Don't use flash on Jellies. They look best back-lit.
• Use the fastest lens and camera speed (ISO) that you can.

I heard my blog doesn't look as good as it used to so I'll post a few pictures while you are all waiting for my next post on formatting your gantt chart.

This is the Golden Gate Bridge in fog taken on my way home from a company dinner a few weeks back.

There is often some confusion about the difference between a view and a table in Microsoft Project. This should help clarify the difference between the two.

A view consists of a number of potential elements:

A screen (the right side typically, but may include splits. Also includes any special formatting for that screen such as modified barstyles etc.)
A table (the data displayed on the right side
A group (optional)
A filter (typically "All tasks" but may be anything you want)

Different views may access the same table or group or filter or screen but may use a different combination.
Modifying a table will affect all views which use that table.
For this reason, and because I frequently add/remove columns, it is good practice to build a specific table for each view. For example you might build a presentation view which uses a table and filter customized to show only the elements you want to present, or a print view etc.

The data displayed in the table or view does not belong to the table. It is present in the file and is only displayed in the table, so deleting columns in the table does not delete that information. Deleting a row however will delete the information.

This information is the same for all versions of Project, with the exception of Project 98 and earlier which did not have grouping.

I wrote this email to someone who is considering starting a blog.

Building your blog:

If no one reads your blog you will give up on it eventually. There is no point writing something that no one reads. So you need to have some way for people to find it. Search engines are the main way to get traffic. This requires that you have something worth reading on your site first.

85% of the people who arrive at my site found it through Google. Maybe 5% use Yahoo and 1% use MSN. The rest are from links here and there. This article gives some pointers on making your blog search engine friendly:

Here are my top ten pieces of advice:

1. Include your blog URL in your newsgroup signature and if you have already solved a problem then point people to the in-depth article about it.
2. Don’t submit your site to the search engines! Rumor is that they put you in a sandbox for a few months if you do so. If you have good content you can get people to link to your site and Google will follow those links and index your site. The more links the more important Google thinks your site is. But go slow on getting other people to link to you. Steady growth is rewarded. Fast growth looks like spam to the search engines.
3. Don’t go too wild with categories. I think I have too many, but after a while you have too much stuff to bother reorganizing.
4. Keep focused on one main topic which you care enough about to spend time on. I throw a lot of different sorts of crap in my blog and I don’t think it does much to help me. I noticed I was posting enough about other things that I created a second blog (http://zo-d.com/stuff) to handle miscellaneous postings. But then I also created a few other blogs I completely abandoned so don’t go overboard.
5. Don’t bother creating daily or weekly or monthly or yearly archives. They will confuse the search engines and no body cares about them anyway.
6. Don’t expect much for 6 months or so. Traffic is roughly proportional to the amount of content you have. The more you write, the more it grows.
7. Keep articles short. Attention spans for online reading last about a single page at most. Unless you are a brilliant writer, most people just want an answer to their question or some new knowledge. They won’t finish reading something that is too long. You can always break long articles up into a series of smaller ones.
8. Start any post with a attractive and relevant description. The first sentence often shows up in the search results so don’t be writing things like “I was at the coffee shop with Blaise and I spilled a frappuchino on my pants” and then launch into how to set up a workflow in Sharepoint. The Sharepoint readers will be disappointed and so will the coffee on the pants fetishisti.
9. If there is something you have taken the time to research and understand, share it. For example, I’m probably putting this list on my blog.
10. Ignore this advice and try to have fun with it. I disregard any of these rules if they interfere with my enjoyment. The rewards of blogging are subtle so if you don’t get enjoyment from the process, then you will soon realize you are wasting your time.

Projity is introducing a new free MS Project compatible scheduling program which they bill as "a complete open source desktop replacement of Microsoft Project". I have tried the beta briefly (https://www.projity.com/openproj/) and I like what I see. While it looks very much like a clone of MS Project, it strips down some of the complexity from the interface and makes some common tasks easier (viewing a stacked resource graph for example). It opened my MS existing MS Project 2003 .xml files with no visible problems - though even MS Project has some quirks in handling .xml files, so it will pay to double check this before using for a real schedule.

I think this is the first MS Project viewer which will really make inroads against MS Project. This is true for a number of reasons:

• It is free
• Look and feel similar to Project (maybe too similar... but let the lawyers decide)
• File compatibility
• Ability to edit and create schedules
• Cross-platform compatibility (Mac users will rejoice after being denied the latest versions of Microsoft Project)

The developers of OpenProj appear to have come from Scitor (now Sciforma) which was known for their "Project Scheduler" software, the latest version of which is PS8. PS was always an also-ran in comparison to project because of Microsoft's millions of installed versions of Office and associated enterprise licensing. By focusing on compatibility and on Linux/Mac and by providing the software for free, Projity may have a better chance of initiating cracks in the marketplace. The ultimate goal for them must be in project hosting or selling enterprise-level features (multi-project support etc.).

On the other hand, I think that desktop scheduling products are fairly dormant. Microsoft has been moving away from them by adding things like server-side scheduling and the ability to create and maintain projects through Project Web Access. Meanwhile, new features on the desktop are very slow in coming.

The next battleground will be online scheduling and by providing a full featured client for free, Projity is signalling their readiness to fight. It should be interesting to see how things go. Competition in this space will drive Microsoft harder and that should be good for everyone.

I'll try and get to a more in depth review when I have a chance.