Presented by: Johnny Johnson
Mr. Johnson is an integration consultant for Micro-Frame Technologies, Inc, and President of Silverdale Software Systems, Inc. He has taught computer science at the US Naval Academy and business management courses at community colleges. He has developed large scale project management and database systems for the FAA, Air Force, Martin Marietta, as well as other government and commercial customers. He is a Microsoft Project Solutions Partner and Trainer.
Phone: (206) 598-5842
Fax: (206) 598-5843
CompuServe: 74415,1237
Internet: silvrsof@interserv.com
Since the CPM schedule and resource data is only part of the management problem, I almost always find that data from traditional project management systems needs to be integrated with accounting, text documents, pictures, charts, and other organizational data. This is where I find that a relational database environment is needed. Microsoft has provided this in Microsoft Access, Microsoft FoxPro, Visual Basic, SQL Server, and Visual Basic for Applications. Using these tools, we can create an integrated, distributed project management environment.
In Project Management integration, I see the relational database system as the central point of access to the data and to other software because:
I would like to concentrate this discussion on using Microsoft Access as the example database, knowing that there are other products that could also do the job. I particularly like to use Microsoft Access because of its ability to "access" other databases without importing data but by attaching to the database files via "ODBC" and using their data. We can still be maintain any current system by using the original database language then using Microsoft Access to create reports or even update data across several dissimilar databases. Additionally, we can liberally use Microsoft Visual Basic 4.0 and Visual Basic for Applications to create functions, forms, and add functionality.
I would like to discuss the organizational and philosophical implications of integrating Project Management data with database systems.
The first questions we should answer are:
Engineers at the detail level need more information than the supervisor or the supervisor's manager. This is always a hard question to answer because it involves organizational attitude about "Micro" management or to what level do we roll up data at each management level. Generally speaking, the less data depicted the better. You can always get detail data when needed. If all management needs, is to answer the question - "When will it be ready?", then just let them see completion dates (scheduled finish, early finish, or late finish data elements).
Many times I have seen organizations that say they must have "real-time" updates, yet they only update data weekly and report on it monthly. The costs in hardware, development, and security may be an unnecessary expense. The entire system development approach depends on how this question is answered.
What is the reporting cycle or when do reports have to be presented? Are they due daily, weekly, bi-monthly, monthly?
What is the best way to display the data? (graphical, bar chart, tabular, Gantt, Pert, etc.) Project schedules may make more sense displayed as Gantt Charts but at times a tabular list of finish dates may be more appropriate.
If I collect schedule data by the minute, that means my schedule could change every minute, and would require status to be updated every minute or my schedule would be wrong. This is appropriate if you are launching a space shuttle, but for most companies, trying to plan to that level is "cruel and unusual punishment" and generally not necessary. It has been my experience, that very little takes less than four hours, considering telephone calls, coffee, impromptu meetings, and other interruptions. I try to factor these considerations in when I estimate duration's of work. In some instances, if work duration's are very critical, some form of risk analysis could be used to define worst, best, and most likely duration's for a piece of work. Remember, data systems are supposed to save time but they don't guarantee that you will. If you find engineers spending more time entering or manipulating data, they are most likely spending less time on engineering, which is their primary function.
Is there significant data existing some where else? Can I attach or import that data? If I can, which data elements do I need? The trick here is to reduce data redundancy, keep data integrity, and keep down development costs. This is where Access's ability to attach to data is especially useful.
The hardest reports to do in Microsoft Project are those that require a "one-to-many" relationship, such as resources related to tasks and grouped by task. Microsoft Project 4.0 allows a direct export of all of its data to a Microsoft Access database including Task, Resource Pool, Resource Assignment, and Project Header Information. Four tables are created in Microsoft Access, allowing for easy linking of one-to-many data elements.
You may copy as many projects as you want into the Access database, as each project maintains a unique "key". Project may also load projects from this Access project database. This capability gives rise to several interesting implementations which I will discuss later.
In some cases we could send manually entered schedule dates from Microsoft Access to Microsoft Project to produce simple Gantt bar charts. This is especially useful for schedule data that requires no "logic" (dependencies) and we could simply send the task name and the start and end dates to produce bar charts.
Use Microsoft Access for integrated tabular and graphical reports. Examples of this could be: integrating resource schedule data with accounting data, time sheets, or for forecasting. We could also use this method for trend analysis or saving historical schedule data for later analysis or regression. Another report might be forecast versus actual spending curves.
Many users can update status or changes, such as actual start and complete dates, and percent complete, in the multi-user database. That data can then update schedule data in Microsoft Project.
In this approach, you could leave the schedule data in Microsoft Project, then through VBA start Microsoft Project with the corresponding schedule for the project you are currently viewing in the Access database.
With Microsoft Project 4.1, you may save a project to a Microsoft Access or ODBC database directly. You may have it automatically create a "new" database with the correct "project" structure or append or update a previously created "project" database. The Microsoft Project data transferred is captured in four Microsoft Access tables including: Tasks, Assignments, Resources (Pool), and the Project Header data such as Project Name, Manager, Company, and so on. Basically, all data needed for a project is transferred.
Before you save a project using the "Save to Database" utility, you need to set up a DSN (Data Source Name). This is done in the using the Control Panel's 32 Bit ODBC dialog. You can set up a Microsoft Project database container as follows:
Click on the appropriate Data Source Driver, then Setup
If you are creating a new database, click "create" and give the database a name, else use the "select" option.
Now you are ready to save to that data source. When saving a project to an ODBC database for the first time, you are prompted to select the appropriate data source name (DSN) to which to save the project. The project data will be appended to that database. If that project already exists in the database, it will delete the previous project data and replace it with the new data. Once you have saved a project to a database the first time, the project remembers where it was saved. The next time you save the project, using the "Save to Database" utility, it will automatically be saved to the database where it was last saved. Each project file holds this intelligence in the Text 1-4 fields of Task 0 (the Project Summary task).
The specifics of the Microsoft Access "project" database structure allow for the user to relate all data.
All table records are "keyed" to a unique Project Key. A sample of the "project" table data structure definition follows:
This is a partial listing of the "Project" table definition in Microsoft Access—fields unique to each project saved in the database.
Simple Relational Report Using Microsoft Project Data - Report Filtered on Carpenter
In Microsoft Project 4.1, you may "Open" projects directly from the Microsoft Access or ODBC "project" database. Since all project data is "saved" to the database, you can re-open a project at any time; you don't need to save the ".MPP" file unless you need to for local reasons. After Microsoft Project 4.1 re-calculates the data, changes the logic, and so on, the project can be "saved" back to the original database, thus updating the "master" project database.
There are two scenarios that come immediately to mind.
Both of these scenarios assume that everyone involved in updating a specific schedule are using the same calendar; otherwise the calculations of schedule dates will vary.
As in all distributed multi-user implementations, contention is an issue. Who has a "copy" of the project from the database on a local machine? If more than one person has a copy, there is a possibility of one person over-writing anther person's data in the database. These issues can be taken care of through policy, security, or an automated "check-out" procedure built into the ODBC database system.
The demo database, MFTITE96.MDB contains all of the demos shown and many sample forms, queries, and reports. Once you start Microsoft Access 7.0 and open the MFTITE96.MDB, go to the forms tab on the database menu and open "frmDemoMainMenu" which brings up a form that allows you to easily explore the demo forms, queries and reports. The demo database is intended to show you ways to properly link tables, make complex queries, design simple forms and produce complex reports across all of the projects in the database. The power of this approach is that it requires virtually no programming to create. Obviously, very sophisticated applications can be produced by using Access Basic and Macros.
Microsoft Access 7.0 Database Window
Demo Database "MFTITE96" Main Menu
The purpose of the Project Summary form is to demonstrate creating a main and subform linking them together with the ProjectKey, which is created when a Microsoft Project 4.1 project is saved to the database, using the "Save to Database" utility. The main form browses the "Project" table, which has one summary record for each project in the database. The subform, which is linked by ProjectKey, lists all tasks associated with the current Project selected in the main form. Using a Main/Subform metaphor is natural because of the one-to-many relationships that occur in project management models. Users may quickly browse the schedule data across all projects and need not go back to Microsoft Project 4.1 to view this data. Remember, the advantage of using Microsoft Access and Microsoft Project together is that every one can view project management data without having to be a project manager or have training in project management.
The Project Summary Form
There are times when you want to compare the same task across all projects. The "Select Specific Task" form demonstrates selecting a unique task name from a list box, then displaying all matching tasks across all projects. In this case the main form is based on a list of unique tasks. When you select a task, the subform, which is linked by the Task Name to the list box control name (the selected task), the subform updates the data to match the selected task.
Microsoft Access Form Design Mode Properties Dialog
The Select Tasks Form
To answer the question, "Who is working on a specific task on a specific project?" use the "Show Resources Assigned to Task" form. Again this form uses a main/subform construct linking this time on both ProjectKey and Task Name to select the subform data. Although it might be faster to link on TaskID, there is no guarantee that the same task name will have the same TaskID accross several projects. In Microsoft Project 4.0, it is very difficult to ensure the same TaskID across like work. If this is necessary, it might be better to create a standardized coding scheme and use one of the user text fields (Text1-Text10) and assign it there.
Find Resources Assigned to a Specific Task and Project
In this case the values returned by the list box controls are used to link to the subform. SelectedTask and SelectedProject are the names of the list box controls and assume the value returned for the bound column of the combo box.
Control Names Used to Link Main to Subform
Probably the most complex query you can make, using the four basic tables in the project database, is to find out which resources are assigned to which tasks on which projects. It is the one-to-many-to-many that makes this query interesting. The demo form allows you to select a unique resource name then links to the subform query by resource name. You cannot use resource ID's because you cannot guarantee that the resource pool across all projects is consistent. Each project, when is saved to the database, gets its own copy of the resource table (pool). There are ways of maintaining a common pool but it requires that you set up the system and procedures up front. In other words, set up and ensure that everyone uses the common resource pool from the outset.
All Tasks Assigned to Carpenter Across All Projects
Clicking on the "Send Resource Schedule to Project" will take the filtered data and send it via DAO to Microsoft Project and produce a new project from that data.
Code behind the button:
Sub btnSendSchedToProject_Click()
On Error GoTo Err_btnSendSchedToProject_Click
SendFilteredTasksToMSP41
Exit_btnSendSchedToProject_Click:
Exit Sub
Err_btnSendSchedToProject_Click:
MsgBox Err.Description
Resume Exit_btnSendSchedToProject_Click
End Sub
The Module Sub routine:
Public Sub SendFilteredTasksToMSP41()
Dim dbs As Database, MyQuery As QueryDef, MyRS As Recordset
Dim oProj As Object, ResourceToSend As String
Dim dbLocal As Database
Dim tmpID As Long
' Return Database variable that points to current database.
Set dbLocal = CurrentDb
ResourceToSend = Forms![frmTasksAssignedToResource]![SelectedResource]
MsgBox ResourceToSend
' Construct SQL statement including parameters.
Set MyQuery = dbLocal.OpenQueryDef("qryFilteredTasks")
MyQuery.SQL = "SELECT * FROM qryResourcesToProject WHERE [Resources].[Name] = """ & _
ResourceToSend & """ ;"
Set MyRS = dbLocal.OpenRecordset("qryFilteredTasks", dbOpenDynaset)
'create an instance of Project
Set oProj = CreateObject(Class:="MSProject.Application.4_1")
With oProj
.DisplayAlerts = False
.FileNew
End With
'increment the counter for the Task ID
tmpID = 1
Do Until MyRS.EOF
'use the settaskfield method to write data from Access 'record to Project
With oProj
.SetTaskField Field:="Name", Value:=MyRS![Tasks.Name], TaskID:=tmpID
.SetTaskField Field:="Start", Value:=MyRS![Start], TaskID:=tmpID
.SetTaskField Field:="Duration", Value:=MyRS![Duration], TaskID:=tmpID
.SetTaskField Field:="Resource Names", Value:=MyRS![Resources.Name], TaskID:=tmpID
End With
MyRS.MoveNext
tmpID = tmpID + 1
Loop
'Make project visible
With oProj
.Visible = True
.DisplayAlerts = True
End With
End Sub
The Resulting Gantt Chart:
Gantt Chart of all of the Carpenter's work.
The demo reports included in the database demonstrate how to create the underlying queries and group correctly to get resource and task data. The Resources by Task report groups on Project.Name and Task.Name and is sorted by early task start date as follows:
Sorting and Grouping of Resources by Task
Report Design Mode of Resources by Task Report
Totaling in a report can be done in the footer of the group you are totaling. In this case, I totaled the resource cost for each task by creating a text box with its control source equal to =Sum([Cost]) in the tasks footer. To get the total for the project, just duplicate the text box in the project footer and the total of all task costs gets added there.
Total a Field Using a Text Box
Resulting Resource by Task Report
This report is less complex than the last report because it only groups on resource name.
Tasks Assigned to a Specific Resource
The "Soon to Come Due 30/60 report uses a calculated field in the query to make the report group on tasks due from today + 30 days and tasks due 30-60 days from now. This technique makes "look ahead" reports very easy. First of all, the Due3060 query limits all tasks to those scheduled to finish from "Now" until 60 days from now. To do this, simply enter the Finish field's criteria as:
>Now() And <Now()+60
The DUE3060 Query - Design View
The trick in this query is to create a calculated field called "Due" then make it equal to a Boolean expression as follows, by Selecting Shift-F2 to zoom in so that it is easier to type in the expression. Basically, if a finish date falls between now and 30 days from now, Due is equal to "30" and if the finish falls between 30 to 60 days from now, Due is equal to "30 to 60". Now in the report group on "Due" and sort by ascending finish date.
Boolean Expression that Creates Virtual Field "Due"
Results of Due3060 Report * Note the Grouping Headers print out correctly with the value set in the calculated Due field in the query.
The purpose of this presentation was to show that by using both Microsoft Project 4.1 and an ODBC database together, one can not only create integrated applications but also make project management data available to a broad base of users without the overhead of project management training those who need only view the data. Additionally, I hope you can see the advantages from both a system and organizational perspective of using Microsoft Project 4.0 in large scale implementations.