 |
|
 |
|
|
| Home | - | White Paper Index |
Microsoft Project Reports |
First published Sept 04 |
Neville Turbit - Project Perfect |
Rating |
 |
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.
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 AccessOpen a new Microsoft Access Database. Now you can link your Microsoft Project Database to Access. To do this, in Access
|
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.
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 |
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
You can open the table and look at the data to get a better understanding of what is recorded.
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.
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:
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:
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.
|
|
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. www.projectperfect.com.au/downloads/MicrosoftProjectReports.zip |
|
To date, 191 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. |
|
|||||||||||||||||||||||||||||||||||
