Crosstab Milestone Report

[Trent Helms]

I've researched here but haven't found exactly what I'm looking for so
I thought I would post this query to see if anyone could post the
proper approach.

I have a set of projects that each have the same major and minor
milestones. What I would like to do is create a crosstab report that
has all the milestones running horizontally in chronological order.
All Project Name would run vertically in alphabetical order. For each
intersection that was complete I would mark with an "X" indicating
completion. At the end I would have a nice tabular report giving a
snapshot status of all projects. I think the only way to go is to
write VBA to export to Excel using a predefined status template.

Has anyone seen or done anything like this? In other approaches other
than Excel?

Thanks,

Trent Helms
(e-mail address removed)

 

[John]

Trent,
You are correct that the only way to get there is by exporting the data
to Excel. Creating what you want can certainly be done using VBA but
there may be another method you can try although it will require some
manipulation of the data once it is in Excel.

First I would create a master file of all you individual projects by
going to the menu: Insert/Project. From the master file, set up filters
to single out Miletstones and the subproject names (Subproject and/or
Project fields). Then export the selected information to Excel using an
export table. Check the help file on how to export data. Just type
"exporting" into the help file answer wizard. Depending on how the data
is exported to Excel you could either probably use a pivot table to
arrange the data in columns and rows.

Another method you might try is to use the "export tilescaled data in
Excel" add-in found on the "Analysis" toolbar. Use the Task Usage view
and set the timescale details field to be "% complete". Again, filter
for milestones and then run the export. You will have basically what you
want although you will probably end up with several extraneous columns
showing dates with no milestone. By adjusting the timescale in the Task
Usage view, you can minimize the extra columns.

If you want to run a crosstab report on a regular basis and don't want
to be bothered with massaging the data on either end (i.e. Project and
Excel), I recommend a custom VBA macro be created. Using this method,
the whole process can be automated to run with a single click on a
toolbar button (for example).

Hope this helps.
John

 

[Trent Helms]

Thanks John for the input. You're right about option 1. So far that
is the better choice. What I ended up doing was creating a custom
table, custom filter, custom view and custom export map to an Excel
pivot table to make this work. It makes work everything. Once in
Excel I had to use a custom list to keep the sort in the pivot table
correct and wa la I have close to what I need. Each time this would
take a few mintues to do upon each report out.

I tried the second option but that gave me data in Excel that was
harder to deal with.

You're right that VBA will provide for the elegant solution.

There is one other option that I didn't think about at the time of
this post. I wrote a VBA script a few weeks ago that loops through
every folder directory and saves each project file to an ODBC source
(Access, SQL, Oracle). Once in the source I should be able to create
a pretty nice Access crosstab query and report that gathers all this
information in the format I need.

Thanks again for the thought provoking response.

Trent Helms
(e-mail address removed)