Project Perfect
PROJECT  PERFECT
                 Project Management Software
                          Specialists in Project Infrastructure

Home- White Paper Index

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.

Get Organised with Project Administirator Software

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

Project Management Software

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, 356 people have rated this article. The average rating is 2.84 - 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
1 2 3 4 5

Return to the top