Query to Sum Use Case Effort by Project Code

We start with a query that estimates the amount of effort that each project in the portfolio represents. The work we did to estimate effort for use cases will provide just what we need for this.

Figure C.1 is a Microsoft Access database query to sum use case effort by project code. A sample of the results from this query is provided in Figure C.2. This query is similar to the one we used earlier to total effort by project type (refer to Figure B.3).

Figure C.1. Query to calculate use case effort by project code.

Figure C.2. Sample results produced by query of Figure C.1.

Query to Prepare Data for Import to Microsoft Project

Getting the combined project portfolio and use case information into Microsoft Project so that it can generate a resource graph is straightforward. A database query is written to build a table with the information needed for MS Project to generate a schedule. That table is then imported into Microsoft Project; MS Project does the rest, i.e., generates the desired run charts.

Figure C.3 shows the MS Access query that will generate a table suitable for import into MS Project; it uses the query just created to sum use case effort by project code (refer to Figure C.1).

Figure C.3. Query to calculate data for import to Microsoft Project. Notice that it uses the query previously defined in Figure C.1. An example of this query's results is provided in Figure C.4.

A sample table created by this query is shown in Figure C.4. In the table, each row corresponds to one project in the portfolio database. Notice that the names of columns match the names of fields that MS Project uses for schedules, namely:

• Task Name Each project in the portfolio becomes one task in MS Project. The project code is used for the task name.

• Work This is the field MS Project uses for effort. For each project, we use the sum of effort of all use cases assigned to the project.

• Duration This is the duration, in calendar work days, as per the project portfolio. See Figure B.1.

• Start This is the date the project is scheduled to start as per the project portfolio. See Figure B.1.

Figure C.4. Table for import into Microsoft Project. This table was created by the query shown in Figure C.3.

To build a schedule, we simply import this table into MS Project. Figure 8.2 shows a schedule with resource graph generated from the import of the data of Figure C.4, which was produced by the query shown in Figure C.3.