Microsoft Project Reports
|
First published Sept 04
|
Neville Turbit - Project Perfect
|
Rating |
|
|
Overview
Most of us have struggled with the limited reporting facilities on Microsoft
Project. You never seem to be able to find the report you want. There is another
way. By saving the Microsoft Project file as a Microsoft Access file, you
can write your own reports. Saving as Access does not replace your traditional
.mpp file. It creates a second file with an .mdb extension.
Note: Project 2007 does not provide this facility. You need to
be using an earlier version.

Save to Microsoft Access
Go to the "File" menu and select "Save As". Under "Save as Type" select "Microsoft
Access Database *.mdb". This does not replace the .mpp file. It creates a
second file.

Link to Access
Open a new Microsoft Access Database. Now you can link your Microsoft Project Database to Access.
To do this, in Access
- Go to "File", "Get External Data", "Link Tables"
- Locate the Project database (.mdb) file
- Go to Tables
- Select "All" and you are linked.
|
What linking does is that you have an Access file (your new database) where
you can write queries and reports, but the data is in another file - in this
case the Access version of the Microsoft Project file. You can tell it is
linked if you look at the tables. They have an arrow to the left of each table.
You can delete the links without deleting the files.
Now you are linked, you can write your own reports.

Key Tables
One of the key tables you will use is the Task table. MSP_TASKS contains
all the details regarding the tasks in your project such as name and duration.
It also contains milestones which are tasks with a zero time:
Fields are:
Name
|
Type
|
Length
|
RESERVED_DATA |
Text |
1 |
PROJ_ID |
Long Integer |
4 |
TASK_ACWP |
Double |
8 |
TASK_BCWP |
Double |
8 |
TASK_BCWS |
Double |
8 |
TASK_DUR_VAR |
Long Integer |
4 |
TASK_FINISH_VAR |
Long Integer |
4 |
TASK_OUTLINE_NUM |
Text |
255 |
TASK_START_VAR |
Long Integer |
4 |
TASK_IS_OVERALLOCATED |
Yes/No |
1 |
TASK_OVT_WORK |
Double |
8 |
TASK_VAC |
Double |
8 |
TASK_REG_WORK |
Double |
8 |
TASK_NUM_OBJECTS |
Long Integer |
4 |
TASK_TOTAL_SLACK |
Long Integer |
4 |
EXT_EDIT_REF_DATA |
Memo |
|
TASK_UID |
Long Integer |
4 |
TASK_ID |
Long Integer |
4 |
TASK_HAS_LINKED_FIELDS |
Yes/No |
1 |
TASK_IS_MILESTONE |
Yes/No |
1 |
TASK_IS_CRITICAL |
Yes/No |
1 |
TASK_IS_SUMMARY |
Yes/No |
1 |
TASK_IS_SUBPROJ |
Yes/No |
1 |
TASK_IS_MARKED |
Yes/No |
1 |
TASK_IGNORES_RES_CAL |
Yes/No |
1 |
TASK_IS_ROLLED_UP |
Yes/No |
1 |
TASK_IS_FROM_FINISH_SUBPROJ |
Yes/No |
1 |
TASK_BAR_IS_HIDDEN |
Yes/No |
1 |
TASK_IS_RECURRING |
Yes/No |
1 |
TASK_IS_RECURRING_SUMMARY |
Yes/No |
1 |
TASK_IS_EXTERNAL |
Yes/No |
1 |
TASK_IS_EFFORT_DRIVEN |
Yes/No |
1 |
TASK_IS_COLLAPSED |
Yes/No |
1 |
TASK_HAS_NOTES |
Yes/No |
1 |
TASK_IS_READONLY_SUBPROJ |
Yes/No |
1 |
TASK_LEVELING_CAN_SPLIT |
Yes/No |
1 |
TASK_LEVELING_ADJUSTS_ASSN |
Yes/No |
1 |
TASK_DUR_IS_EST |
Yes/No |
1 |
TASK_EARLY_FINISH |
Date/Time |
8 |
TASK_LATE_START |
Date/Time |
8 |
TASK_STOP_DATE |
Date/Time |
8 |
TASK_RESUME_DATE |
Date/Time |
8 |
TASK_FREE_SLACK |
Long Integer |
4 |
TASK_OUTLINE_LEVEL |
Integer |
2 |
TASK_DUR |
Long Integer |
4 |
TASK_DUR_FMT |
Integer |
2 |
TASK_ACT_DUR |
Long Integer |
4 |
TASK_REM_DUR |
Long Integer |
4 |
TASK_BASE_DUR |
Long Integer |
4 |
TASK_BASE_DUR_FMT |
Integer |
2 |
TASK_CONSTRAINT_TYPE |
Integer |
2 |
TASK_LEVELING_DELAY |
Long Integer |
4 |
TASK_LEVELING_DELAY_FMT |
Integer |
2 |
TASK_START_DATE |
Date/Time |
8 |
TASK_FINISH_DATE |
Date/Time |
8 |
TASK_ACT_START |
Date/Time |
8 |
TASK_ACT_FINISH |
Date/Time |
8 |
TASK_BASE_START |
Date/Time |
8 |
TASK_BASE_FINISH |
Date/Time |
8 |
TASK_CONSTRAINT_DATE |
Date/Time |
8 |
TASK_PRIORITY |
Integer |
2 |
TASK_PCT_COMP |
Integer |
2 |
TASK_PCT_WORK_COMP |
Integer |
2 |
TASK_TYPE |
Integer |
2 |
TASK_FIXED_COST_ACCRUAL |
Integer |
2 |
TASK_CREATION_DATE |
Date/Time |
8 |
TASK_PRELEVELED_START |
Date/Time |
8 |
TASK_PRELEVELED_FINISH |
Date/Time |
8 |
TASK_EARLY_START |
Date/Time |
8 |
TASK_LATE_FINISH |
Date/Time |
8 |
TASK_CAL_UID |
Long Integer |
4 |
TASK_DEADLINE |
Date/Time |
8 |
TASK_WORK |
Double |
8 |
TASK_BASE_WORK |
Double |
8 |
TASK_ACT_WORK |
Double |
8 |
TASK_REM_WORK |
Double |
8 |
TASK_COST |
Double |
8 |
TASK_FIXED_COST |
Double |
8 |
TASK_ACT_COST |
Double |
8 |
TASK_REM_COST |
Double |
8 |
TASK_BASE_COST |
Double |
8 |
TASK_ACT_OVT_WORK |
Double |
8 |
TASK_REM_OVT_WORK |
Double |
8 |
TASK_OVT_COST |
Double |
8 |
TASK_ACT_OVT_COST |
Double |
8 |
TASK_REM_OVT_COST |
Double |
8 |
TASK_WBS |
Memo |
|
TASK_NAME |
Text |
255 |
TASK_WBS_RIGHTMOST_LEVEL |
Memo |
|
TASK_RTF_NOTES |
OLE Object |
|
TASK_EAC |
Double |
8 |
TASK_PHY_PCT_COMP |
Integer |
2 |
TASK_EVMETHOD |
Integer |
2 |
|
|
|

Key Fields
It is not quite as daunting as it looks. You will find most of the fields
are blank when you look at the table. A few key fields are
- TASK_NAME which is the name of the task
- TASK_OUTLINE_NUM which has the number of the outline just as you might
do with numbering headings in a report e.g. 2.2.3.
- TASK_START_DATE
- TASK_FINISH_DATE.
You can open the table and look at the data to get a better understanding
of what is recorded.

Create a Query
By just using the query wizard in Access you can write a report that lists
all the tasks in finish date order.

In Criteria, we put 0 under TASK_IS_SUMMARY and <>100 for TASK_PCT_COMP.
In other words, the task is not a summary task. and it is not 100% complete.
SQL
The SQL statement looks like this:
SELECT MSP_TASKS.TASK_START_VAR, MSP_TASKS.TASK_IS_SUMMARY, MSP_TASKS.TASK_OUTLINE_LEVEL,
MSP_TASKS.TASK_START_DATE, MSP_TASKS.TASK_FINISH_DATE, MSP_TASKS.TASK_NAME,
MSP_TASKS.TASK_PCT_COMP FROM MSP_TASKS
WHERE (((MSP_TASKS.TASK_IS_SUMMARY)=0) AND ((MSP_TASKS.TASK_PCT_COMP)<>100))
ORDER BY MSP_TASKS.TASK_FINISH_DATE;
For those who are not familiar with SQL, the key components are that you:
- SELECT a number of fields to output for use in a report. Each field is
separated with a comma
- Define the condition WHERE. The condition is that the field TASK_IS_SUMMARY
is 0 which means it is not a summary task. The second condition is that
TASK_PCT_COMP is not 100%. It is not complete.
- Set the ORDER.
Write the Report
Now you have the query, you can write the report. Use the report wizard and
you can easily put together a report. This report is useful when you want
to see on one page, what is due for completion in date order. It will help
you focus on the immediate tasks. I have not been able to find anything in
Microsoft Project that gives me this simple information. The report might
look like this:


Conclusion
Many people complain about the limitations of Microsoft Project in terms
of reporting however by saving it as an Access database, you can do whatever
you like with reporting. Whilst this simple report only uses one table, you
can link tables and do all sorts of complex reports to suit your own requirements.
Become a Contributor
I would love to hear from anyone who would like to contribute to our
own free "open source" Access database containing reports that are useful
to a Project Manager. I am happy to host the download so send me an
Access database (turbit@projectperfect.com.au) with your queries and
reports and I will roll it into a common file to download. As a start,
I am including the file we used to illustrate the example above.
|
|

To date, 159 people have rated this article. The average rating is 4.35 - Add your rating. Just select a rating and click the button. No other information
required.
Only one rating per person is allowed.
|
Microsoft Project Reporting
|
Return to the top

|