|< Day Day Up >|
Who Does What Report
Used to save an HTML table that lists resources and their task assignments.
Compare to Baseline
Used to export a table that lists all tasks with scheduled and baseline values.
Cost Data by Task
Used to export a table that lists task costs.
Default Task Information
Used to export or import the basic task fields that are included in the Task Entry table.
Earned Value Information
Used to export the task earned value fields.
Export to HTML Using Standard Template
Used to export basic task, resource, and assignment values to an HTML document.
Resource Export Table Map
Used to export all the fields in the predefined resource's Export table.
Task Export Table Map
Used to export all the fields in the predefined task's Export table.
Task and Resource PivotTable
Used to create Excel PivotTables for tasks and resources.
Task List with Embedded Assignment Rows
Used to export an HTML table of tasks and their assigned resources.
Top Level Tasks List
Used to export a table with data for tasks at the top outline level.
The following sections explain how these maps were created and how they can be used most effectively. All the predefined maps were designed for exporting selected data from Project to another file format. Some were designed with specific file formats in mindfor example, HTML or Excel PivotTablesbut any map can be used to export to any of the formats. Maps
Note that in all cases, the exported values for the duration and work fields are exported as text (not as numeric data), with the time unit attached as part of the text value, such as 10d .
Although some maps are designed for exporting and some for importing, any map can be used for either operation. Be very careful, however, when using a map to import data into Project because the result might not be what you expect.
When you use an Import/Export map to exchange data with a file that has another format, the external file that
By default, Project's predefined maps all place imported data into a new Project document that is created on the fly. Therefore, clicking the file listing causes Project to create a new document that has the Excel data in it. You should close this new project file without saving it because the map was designed for exporting, not for importing, and the new project file is not a complete project file.
When imported data is placed in a new Project document, there is no harm done because you can simply close the new document. However, import maps can also be defined so that they append the imported data to the active file or merge the imported data into existing task or resource records, updating existing field values with the values stored in the external document. If the map named in the file listing is set to append or merge, clicking the file listing alters the data in the document that is active when you click the listing. If the active document has unsaved data in it, you could lose some or all of that data as a result of clicking the listing on the File menu.
You should never open a foreign-format map file from the File menu's file list unless you're
The Who Does What Report map is best used to save an HTML table of resource assignments. The resulting table is named Who Does What and is similar to the Who Does What report you can print in Project by choosing View, Reports, Assignments, Who Does What. Like the Resource Usage view in Project, the HTML table lists resource names and task assignment names in the same column with the assignment names, indented under their resource names. There are
Although this map works best when saving to HTML format, you can also use it to save to an Excel workbook or to one of the text file formats. When saving to Excel or one of the text formats, the resource rows are indistinguishable from the assignment rows because the assignments are not indented as they are in the HTML format. If you open the HTML document in Excel, however, the resources and assignments are formatted distinctively.
When saving data to Excel or a text format, you should modify the Who Does What Report map and add the field named Assignment to the table to distinguish resource names from task assignment names. Rows in the list that are resources have No in the Assignment field, whereas rows for assignments have Yes. You could apply Excel's conditional formatting to bold the resource rows based on the value No being found in the Assignment column. See the section "Saving Project Data as an Excel Worksheet," later in this chapter, for more information.
If you attempt to use the Who Does What Report map to create a database table, Project doesn't include the associated assignment rows for each resource. To create database tables, you have to add to the map an additional table for the assignments and then link the tables on the resource names within the database application. (See "Working with Web-Enabled Project Data" later in this chapter for more details about the HTML format.)
You use the Compare to Baseline map to export a table named Baseline Comparison that lists all tasks, with their Start and Finish dates as well as their scheduled, baseline, and variance values for duration, work, and cost. This map works the same way for database, worksheet, and text formats.
You use the Cost Data by Task map to export a table named Task Costs that lists all the tasks (fixed cost, cost, baseline cost, cost variance, actual cost, and remaining cost) and their cost values.
The Default Task Information map can be used to export or import the basic task fields that are included in the Task Entry table: ID, Name, Duration, Start Date, Finish Date, Predecessors, and Resource Names. This map works the same way with all file formats.
You use the Earned Value Information map to export the earned value fields for tasks to any of the file formats. It includes these fields: Task ID, Task Name, BCWS, BCWP, ACWP, SV, CV, Cost (EAC), Baseline Cost (BAC), and VAC.
For more information about earned value fields, see "Analyzing Progress and Revising the Schedule," p. 555 .
You use the Export to HTML Using Standard Template map to export basic task, resource, and assignment values to an HTML document. The task table includes the ID, Name, Duration, Start, Finish, Resource Names, and % Complete fields. The resource table includes the ID, Name,
See the section "Working with Web-Enabled Project Data" later in this chapter for more details about the HTML format.
You use the Resource Export Table map to export the fields that are included in the predefined resource's Export table. The resource's Export table is a
You use the Task Export Table map to export nearly all the fields that are included in the predefined task's Export table. The 70+ fields that are exported include task definition fields; values for scheduled, baseline, and actual work, and values for cost, duration, start, and finish; and a large number of the
You use the Task and Resource PivotTable map to create an Excel document with two PivotTables that summarize the total cost of the resource assignments. Both PivotTables are organized by resource groups and within each group by resource names. The resulting Excel file contains four sheets in all:
The Tasks sheet provides the data for the Task PivotTable and includes these columns: Resource Group, Resource Name, Task Name(s), Duration, Start, Finish, and Cost. If your project is outlined, there is a Task Name column for each outline level, and all summary task names for a subtask appear on the row for the subtask. The outline level 1 summary tasks are in the column named Task Name1, the outline level 2 summary tasks are in the column named Task Name2, and so forth.
The Task PivotTable sheet shows for each resource group the tasks to which each resource in the group is assigned, along with the task duration, start, and finish. The cost for each assignment is the Data field in the PivotTable, and there are summary costs for each task and summary task, for each resource, and for each resource group.
The Resources sheet provides the data for the Resource PivotTable and includes the Resource Group, Resource Name, Work, and Cost fields.
The Resource PivotTable shows the work and cost totals for each resource group and each resource within a group. The totals are not broken down by task.
If a project has more than two outline levels, Project creates the data sheets and the PivotTables, but it is not able to perform the layout of fields for the Task PivotTable, and that sheet therefore appears to be empty. You have to do the layout manually in Excel.
See the section "Exporting to an Excel PivotTable," later in this chapter, for more information about working with PivotTables.
Use the Task List with Embedded Assignment Rows map to export an HTML table of tasks and their assigned resources. This map is best used to export a table that includes all the resource assignments for each task, as well as the Work, Duration, Start, Finish, and % Work Complete fields for each assignment. All the information about exporting to Excel or Access, as mentioned earlier for the Who Does What Report map, applies to this map as well.
You should use the Top Level Tasks List map to export a table that shows only the outline level 1 tasks. The fields include the Task ID, Name, Duration, Start, Finish, % Complete, Cost, and Work. This map works for all file formats.
Creating an Import/Export map is fairly straightforward when you understand the process because Project does most of the work for you. To
Export maps are easier to create than import maps because the Project field names are the source of the data, and you can create field names in the target format that are similar to the Project field names. When you create an Import map, the field names in the source format may not be as easy to relate to the field names in Project.
To create a map for exporting some of Project's cost data to an Access 2002 database, follow these steps:
Open the Project file whose data you want to export.
Choose File, Save As.
Select the directory location from the Save In list box.
In the Save as Type list box, select Microsoft Access Database (*.mdb).
If you are creating a new database, type its name in the File Name text box.
If you are going to append data to an existing database, it is a good idea to create a copy of the database for testing while creating the new map. Then, when the map behaves as you want it to, you can use it to append to the intended database and you can delete the test database.
In general, it is not wise to add partial project data sets to an Access database that already contains complete projects. Project does not let you add selective data to the tables it has already created in the database; therefore, you would have to create your own named tables to hold the data, which you might as well do in a special database that is dedicated to partial data sets.
Click the Save button to display the Export Wizard dialog box.
Select the option to export only selective data, and then click the Next button. The Map dialog box appears.
Select the option to use a new map and click the Next button. The Map Options dialog box appears.
Select one or more types of datatasks, resources, or assignmentsto be exported. In Figure 17.12, you can see that all three types of data will be exported.
Click the Next button to display the Task Mapping dialog box.
Supply a descriptive name for the table in the Destination Database Table Name field.
If you want to limit the tasks that will be exported, use the list of filters in the Export Filter field to select a task filter. Any of the currently defined filters can be
Define the task fields that will be exported in the mapping table. You must list each of the Project fields that are to be exported in the column labeled From: Microsoft Project Field. You must create a name for the database field that will hold that data in the column labeled To: Database Field. The data type is filled in automatically, based on the Project field types.
Click the list arrow in the first cell in the left columnthe
Select the fields to be exported from the list of Project fields, and press the Enter key after selecting each field. A default field name is inserted in the second column (for the exported database), and the field data type is automatically inserted in the third column. Below the mapping table, you should see a sample of the fields you have added and the data they contain.
You can change the export field name to suit your tastes. Be sure, however, that you don't
When exporting to Access, you should leave in place the
If needed, you can use several buttons to speed the process of managing the field mapping table (refer to Figure 17.14):
To move a field row in the list, select the row to be moved and use the Move arrows on the right side of the mapping table to move the row up or down in the list.
To insert all the task fields in Microsoft Project, click the Add All button.
To clear the mapping table, click the Clear All button.
To insert a blank row for a new field in the middle of the list, select the place where the row should be inserted and click the Insert Row button.
To remove a field row, select the row to be removed and click the Delete Row button.
To populate the mapping table with the same fields that are contained in one of the task tables in Microsoft Project, click the Base on Table button. The Select Base Table for Field Mapping dialog box appears (see Figure 17.15), with a list of all the currently defined task tables. Select the table you want to use and click OK. The field list is cleared from the mapping table, and the fields that are defined in the table you selected are inserted in the mapping table.
When the task mapping table is completed, click the Next button to move on to the next table you have elected to include in the database export. In this example, the Resource Mapping page is displayed (see Figure 17.16). The table name is Summary by Resource. You fill in the fields by selecting the Cost table from the list displayed by the Base on Table button. (Note that you can also apply a resource filter to select a subset of the resource records to be included in the Export table.)
Finally, if you are including assignment fields, click the Next button to display the Assignment Mapping page, and then repeat the process. The assignment records are the details that are combined for the task and resource cost summaries. Because there is no table in Microsoft Project for assignments (they only appear in the Task Usage and Resource Usage views and on certain forms), you cannot choose a table as a template for the fields to be included. Also, there are no filters for assignments. Both these options are dimmed on the Assignment Mapping tab.
When all the tabs are filled in, click the Next button. The End of Map Definition page appears.
Click the Save Map button to save the export map. The Save Map dialog box appears (see Figure 17.17).
Type a descriptive name for the map in the Map Name field and then click the Save button. You are returned to the End of Map Definition dialog box.
Click the Finish button to export the selected data to the database whose name you entered in the Save As dialog box in step 5.
If you design a map for use with one file formatfor example, with HTMLand then use the map to exchange data with an application of another format, such as Access, the results might be very different. For specialized maps it's a good idea to include the application name in the map name, as is done in the
When you want to import data into Microsoft Project from another source, you must either find a
The options for an import map are slightly different from those for the export map:
The source tables are already defined (whereas you defined the target tables in the export map). For example, there may be many tables in the source database that have task information in them. You must choose the table that contains the task data you need. Similarly, you must identify the source tables that are appropriate for supplying resource or assignment fields.
If you created the source by exporting fields from Project, it will be much easier to import the data back into Project from those same fields because the field names will be recognizable. If the source table was created by an application other than Project, the field names have to be matched with Project's internal fields.
You cannot import only a portion of the records by using any of the Project filters. If you want only part of the records from the source, you have to filter the source first, to produce a new database, and then import the resulting tables into Project. Otherwise, it might be easier to import all the data into Project and then just delete the unwanted records.
When importing, you can choose whether the imported data will be stored in a new project file or merged into an existing Project document. You can append the imported records to the records that already exist in a Project document. Or you can choose to merge the imported data by using it to update selected fields for tasks or resources that already exist.
To illustrate how to import from another file format, let's import from the cost database that was created earlier in this chapter, in the section "Creating and Using an Export Map."
If you intend to add the imported data to an existing Project document, you must open that project before starting the import process. Otherwise, it doesn't matter which project documents are open when you import because Project will create a new document. To import data by using a map, complete the following steps:
Choose File, Open, or click the Open tool.
Select the location of the data source in the Look In list box. If you are importing from an ODBC data source, click the ODBC button instead and select the data source in that dialog box.
In the Files of Type list box, select the format of the data source.
Choose the data source file from the file list and click the Open button. The Import Wizard dialog box appears.
Click the Next button to see the Data Type dialog box.
Select the option to import only selective data and then click the Next button. The Map dialog box appears.
Select the Use Existing Map option. If you want to create a new import map, select the New Map option, and then follow the same techniques outlined for creating an export map in the section "Creating and Using an Export Map." Click the Next button to display the Map Selection dialog box.
Select the map that you want to use to import the data. Be extremely careful when selecting a map, to be sure that it defines the way you want to import the data. Click the Next button to display the Import Mode dialog box.
Choose to import data as a new project to start a new Project file with the imported data. Or, choose Append the Data to an Existing Project to add the desired records after the records already in the project. Or, choose Merge the Data to an Existing Project to have Microsoft Project match incoming records with existing records and update the fields with the imported values.
Click the Next button. The Map Options dialog box appears.
Select the types of datatasks, resources, or assignmentsto import. Click the Next button.
The Mapping dialog box appears for the first type of data you selected in step 11.
Examine the Mapping dialog box for each type of data to be imported, to verify that the settings are correct. You should select the list of names in the Source Database Table Name field to verify that the appropriate table has been chosen for the type of data in each Mapping dialog box.
Figure 17.18 shows the Task Mapping dialog box for the import map that is derived from the export map created earlier. The Source Database Table Name list is selected to show the tables in the source database.
The list of source database table names contains more tables than you explicitly defined in your export map. These extra table names have an MSP_ prefix, and they are there to help the user decipher the data values that were exported.
Click the Next button and examine the Resource Mapping or Assignment Mapping dialog boxes to make sure the settings are correct.
If you choose to merge the imported data, the Set Merge Key button appears in the Task, Resource, and Assignment Mapping dialog boxes. You must select a merge key in each Mapping dialog box. Merge Key is a field that has identical values in both the existing Project file and the imported table. For example, the task ID field matches tasks as long as the task list has not been edited since the exported data was created. It would have been better to include the Unique ID field in the export because the Unique ID number doesn't change after a task is created and is a more reliable key field.
After you have selected the key field, click the Set Merge Key button. The field name changes to MERGE KEY: field name . In Figure 17.19, the ID field has been selected as the Merge Key in the Task Mapping dialog box. If you need to change the Merge Key field, select the new key field and click the Set Merge Key button again.
If you want to merge assignment data, you must use the Unique ID field for assignments.
Click the Next button to display the End of Map Definition dialog box. If you have made changes to the map and want to save the map by a new name, click the Save Map button.
Click the Finish button to import the desired data into your project.
Figure 17.20 illustrates the errors you may see in the mapping tables if you choose the wrong map. If the map names source fields that don't exist in the file you have started to open, the field name entries appear in red and have an OUT OF CONTEXT: prefix in the From: Database Field column.
If you don't see the imported data in Project after the import operation is finished, see "Missing Imported Data" in the "Troubleshooting" section at the end of this chapter.
You can export field data to Microsoft Excel workbooks as worksheet data or as PivotTable data. Furthermore, you can import table data from Microsoft Excel workbooks, but not from PivotTables. When you export to Excel, Microsoft Project creates an Excel file in the Excel 5.0/95 format, which makes the data available to that version and all later versions of Excel. You can import from Excel documents that are saved in Excel 4.0 format or any later format, including Excel 2002.
When you create an Import/Export map that will work with the Excel format, a few options are different from those for creating an Import/Export map that will work with other formats:
You can choose whether to export Project data by using the Project Excel Template or via an export map. The Project Excel Template exports a standardized set of Task, Resource, and Assignment fields, and it creates a separate worksheet for each type of data.
You can choose whether to export field names as the first row of the worksheet. If you choose not to export field names, the field data goes in Row 1 of the worksheet and there are no column titles unless you add them later.
You can instruct Project to include assignment rows such as those displayed in the tables of the Task Usage and Resource Usage views. The worksheet rows are not automatically outlined and indented in the Excel workbook as they are in Project.
You can use Excel's Group and Outline command to group assignments under the task or resource, and this makes it possible to hide and display the assignment rows at will (as in Project). However, you will have to group each set of assignments by hand, and when the assignment rows are displayed, they are still not indented.
Before using Excel's Group and Outline command, remember to change Excel's default grouping direction. Choose Data, Group and Outline, Settings and then clear the Summary Rows Below Detail check box. This places the outline symbols for grouping (the plus and minus indicators) to the left of the task or resource name (like in an Assignment view), rather than at the bottom of the list of assignments in each group.
You can use an Import/Export map you created for another format to export Project data to Excel. You might want to use an existing map, and then save it with a different name that indicates it is used for exporting to Excel. In the following example, the map for exporting to Excel is based on the Cost Data map developed previously in the section, "Creating and Using an Export Map."
To export Project data to an Excel workbook, follow these steps:
Open the Project file that you want to export.
Choose File, Save As to display the Save As dialog box.
Select the location for the new file in the Save In list box.
Change the Save as Type selection to Microsoft Excel Workbook (*.xls).
Type the name for the file in the File Name text box.
Click the Save button. Project displays the Export Wizard dialog box.
Click the Next button to display the Data dialog box.
If you choose to export selective data to Excel, click the Next button to display the Map dialog box.
Select the option Use Existing Map. If you want to create a new import map, select the New Map option, and then follow the same techniques outlined for creating an export map in the section "Creating and Using an Export Map." Click the Next button to display the Map Selection dialog box.
Choose a map and click the Next button. The Map Options dialog box appears.
Select the types of datatasks, resources, or assignmentsto be exported to Excel. Some options for Excel don't appear when maps are used for Access. Figure 17.21 shows the Map Options dialog box, with the settings from the Cost Data for Access map.
Select the check box labeled Export Includes Headers so that the first row of data on each sheet in the workbook will display field names as column headers. (If this option is not selected, there will be no label at the top of each column of data in the workbook.)
If you want tasks and resources to show details by assignment (as in the Task Usage and Resource Usage views), select the check box labeled Include Assignment Rows in Output, and remember to include the field named Assignment so that you can distinguish assignments from tasks or resources.
If you export the rows for the assignment details in a task mapping, the assignment rows will appear to be just additional tasks in the workbook that is created. If you then reimported that workbook back into Project, the resource assignments would indeed be listed as tasks, even if you included the Task Assignment field in the exported data. Similarly, exported assignment details in a resource mapping result in the assignments being treated as additional resources, and they cannot be imported back into Project satisfactorily.
Click the Next button to display the Task Mapping dialog box. Examine the task fields to be exported to Excel, to make sure the settings are correct. Notice the destination worksheet name that will be used for the worksheet that receives the task data.
Continue this process for the Resource Mapping and Assignment Mapping dialog boxes. Click the Next button to display the End of Map Definition dialog box.
Click the Save Map button. The Save Map dialog box appears.
Give the export map a unique name, to identify it for use with exporting to Excel, and click the Save button.
Click the Finish button to export the data to an Excel workbook.
When the exported data is opened in Excel, there is a worksheet for each of the tables that were defined in the export map in Project (see Figure 17.22). As specified in the export map, the field names appear in the first row of the worksheet, and the assignments for each resource are listed under the row for the resource.
Be very careful about sorting. If you
The Data dialog box in the Import/Export Wizard includes the option Project Excel Template, which is for exporting data to Excel. This option exports data to Excel quickly, using a template or standard list of fields for task, resource, and assignment information. The standard task fields exported to Excel are ID, Name, Duration, Start, Finish, Predecessors, Outline Level, and Notes. The standard resource fields to be exported are ID, Name, Initials, Type, Material Label, Group, Email Address, Windows User Account, Max Units, Standard Rate, Cost Per Use, and Notes. The template exports the following assignment fields: Task Name, Resource Name, % Work Complete, Work, and Units.
When you use the Project Excel Template option to export data to Excel, an Excel workbook is created that contains four worksheets, named Task_Table, Resource_Table, Assignment_Table, and Info_Table. The first three worksheets contain the type of data described by the worksheet name. The Info_Table worksheet contains general information about how to use the Project Excel Template. Figure 17.23 shows an Excel workbook that was created by using the Project Excel Template option and was then formatted by the user for ease of use.
For information on how to use the Project Excel Template to import Excel data into a Project file, see "Importing Project Data from the Excel Format," later in this chapter.
Excel PivotTables summarize data in crosstab calculations, and they offer impressive flexibility for quickly changing the layout of the PivotTable. When you export to the Excel PivotTable format, the export map lets you choose the fields to be included for one or more of the three categoriestasks, resources, and assignments. A separate PivotTable is created for each category, and all these PivotTables are stored in the same Excel workbook. The save operation not only copies the selected data into Excel data sheets, but it also creates the PivotTables on separate worksheets that are based on the data sheets.
To show how this works, let's export task and resource assignment costs to an Excel PivotTable. For this example, each record contains the name of a top-level task (a phase of the project), the name of a resource assigned to a task in that phase, and the scheduled cost of the assignment. The resulting PivotTable should display the data in a compact table that neatly summarizes the following items:
The total cost for any phase of the project, along with the distribution of that cost among contributing resources, in dollar amounts and in percentage terms
The distribution of cost for each resource in the different phases of the project
Creating a PivotTable such as this involves exporting the data and then fine-tuning the PivotTable in Excel to produce the results you want. The export operation creates only the simplest of PivotTables, but you can edit the PivotTable yourself to fine-tune the display of data.
To export Project data to an Excel PivotTable, follow these steps:
Open the Project file from which you want to export data.
Choose File, Save As.
Select the location for the new Excel file in the Save In list box, and provide a name for the file in the File Name text box.
Choose the file type Microsoft Excel PivotTable (.xls) in the Save as Type list box.
Click the Save button. The Export Wizard dialog box appears.
Click the Next button. The Map dialog box appears.
Choose New Map, and then click Next to display the Map Options dialog box.
In the Map Options dialog box, select one or more types of data you want to export to an Excel PivotTable, including tasks, resources, or assignments (see Figure 17.24).
You can use only one category for each PivotTable. If you export fields from all three categories, you will produce three separate, unrelated PivotTables in the same Excel workbook.
Click the Next button to display the Task Mapping dialog box.
Type a name for the worksheet in the Destination Worksheet Name text box (see Figure 17.25).
Choose the names of the Project fields you want to export in the From: Microsoft Project Field column. When you enter a Project field name, Project supplies the field name for the worksheet in the To: Worksheet Field column. Feel free to modify the worksheet field names.
The last field entered in the mapping table is the field that Excel uses for the table data (that is, the calculated summary
If the last field row is not red in your PivotTable map, see "Malfunctioning PivotTable Map" in the "Troubleshooting" section at the end of this chapter.
If you plan to group the data in the PivotTable by major categories, with minor category details listed under them, put the major category fields above the minor category fields in the field mapping.
Always make the field you want to be used for calculations the last field in the field mapping list. In this example, Cost is the last field listed because you want calculations based on its values to appear in the body of the PivotTable.
When the field map is completed, click the Next button. Repeat the process for any other data category tab you have chosen to use in the export.
Click the Next button to display the End of Map Definition dialog box.
Click the Save Map button. Give the new map a
Click the Finish button to begin saving the exported data into an Excel PivotTable.
You can open Excel to see the data and PivotTable you have created (see Figure 17.26). You should see two sheets in Excel for each data category (tab) you exported. In this example, only the Task category is used, so you see a single pair of sheets. The first sheet contains the raw data you exported in a table; you could use this data to create a PivotTable on your own.
If you include the Task Name field in the Task Mapping list of fields, Project creates multiple Task Name columns in the worksheetone for each outline level. For any subtask, the outline level 1 summary tasks will appear in the Task Name 1 column, the outline level 2 summary tasks will appear in the Task Name 2 column, and so forth (refer to Figure 17.26).
If you include the Resource Names field in the Task Mapping list of fields, Project creates a separate record for each resource name assigned to the task (refer to Figure 17.26).
The second sheet contains the default PivotTable that Project created (see Figure 17.27). You will almost always need to fine-tune the PivotTable that is created. Not only do you need to format things like column widths and the display of numbers, but you also need to adjust the layout of the PivotTable. Specifically, there are no column categories in the default PivotTable, but we want the resource names to appear as column headings. Also, there is only one calculation in the data area, and we want to show the percentage distribution in addition to the sum of the costs.
If the PivotTable sheet in your workbook is blank, see "Empty Exported PivotTable" in the "Troubleshooting" section at the end of this chapter.
For information on how to format Excel PivotTables, see Chapter 24, "Using PivotTables and PivotCharts," in Special Edition Using Microsoft Excel 2003 by Patrick Blattner (published by Que).
Also, there is an
Figure 17.28 shows a sample of the finished PivotTable, after it is edited. The PivotTable in Figure 17.28 shows only the top-level tasks that are summary tasks (in other words, top-level tasks that do not summarize phases have been hidden). The resource names appear across column headings, with data for each resource in the column below its name. A second calculation has been added, to show the percentage of the phase costs attributable to the resource in that column. This figure also shows a few minor formatting changes (zero values are suppressed, decimals are removed, and so forth) that improve the readability.
You must be extremely careful when importing data from Excel into a Project file. You have to be sure that the data is mapped to the correct Project fields and that the data type is appropriate for those fields.
As explained earlier in this chapter, if the Excel workbook was created by exporting tasks or resources from Microsoft Project, and if the option to include rows for resource assignments was selected, some of the rows in the workbook will be tasks (or resources) and others will be assignment details. Do not attempt to import data from a workbook such as this. Identify and remove the assignment details before attempting to import the data back into Project.
The example that
To import the data from Excel into the Project file, follow these steps:
Open the Project file into which you want to import the data, unless you plan to have Project create a new document file for the imported data. Figure 17.30 shows the Resource sheet before the data is imported.
Although it is not necessary, it is wise to choose a view in Project that shows the data when it is imported. This is
Choose File, Open to display the Open dialog box.
Use the Look In list box to select the location in which the Excel workbook is saved.
Change the Files of Type selection to Microsoft Excel Workbooks.
Select the Excel file from the file list and click the Open button. The Import Wizard dialog box appears.
Click the Next button to display the Map dialog box.
Select the New Map option and click the Next button. The Import Mode dialog box appears.
Select the option to append the data to an existing project. Click the Next button to display the Map Options dialog box.
Select Resources as the type of data to import. Make sure that the check box labeled Import Includes Headers is selected. If you don't check the Headers box, the map's field list will not display the Excel column headers (it will just number the columns). This would make it difficult for you to know which Excel column maps to which Project field (see Figure 17.32).
Note that the option to import assignment detail rows is not available. Project has no way of knowing which rows are tasks (or resources) and which are assignment details.
Click the Next button. The Resource Mapping dialog box appears.
Select the source worksheet name from the Source Worksheet Name list. In this example, the worksheet is named Sheet1 .
When the source worksheet is selected, Project fills the From: Excel Field column in the left side of the mapping table with the column headings from the worksheet. Project also attempts to find a matching field name from the project to display in the To: Microsoft Project Field column on the right side of the mapping table.
In Figure 17.33, Project is displaying the Excel field names but cannot locate a match in Project's Resource fields.
Supply the correct field names in the To: Microsoft Project Field column of the mapping table for each field in the From: Excel Field column (see Figure 17.34).
Click the Next button to display the End of Map Definition dialog box.
If you want to save the map, click the Save Map button and give the map a unique name. Otherwise, click the Finish button to begin the import. Your data is appended to the end of the Resource Sheet list of resources (see Figure 17.35).
If Project has a problem converting data that is being imported into a Task field, you might see a warning message. Project is not nearly as
Click Yes to continue importing and to continue seeing error messages. You should generally choose this option, unless you know what the problems are and what corrective action you need to take in the Project file as a result.
Click No to continue importing without seeing further error messages.
Click Cancel to stop importing.
Mismatched data is not imported into Project, and the affected field in Project displays a default value. You need to find these holes in the data and manually supply the correct information.
It's a good idea to jot down the source references in the warning message (refer to Figure 17.36). If you are importing a lot of data at once, the references can help you locate the problem in the source file so that you can determine where to look in the Project file to fill in the missing information.
If you prefer to begin a new project in Excel and then import it into a Project file, you can use one or both of the new Project Excel templates that ship with Project. When Project 2003 is installed, two Excel templates Microsoft Project Task List Import Template.xlt and Microsoft Project Plan Import Export Template.xlt are installed in the standard Office folder.
To begin a project in Excel, open Excel, and then click File, New and select one of the two Excel templates from the list. Microsoft Project Task List Import Template.xlt contains the same four worksheets (Task_Table, Resource_Table, Assignment_Table, and Info_Table) that are exported from Project to Excel when you select the Project Excel Template option in the Export Wizard. This template also contains the same fields that are exported from Project to Excel. Microsoft Project Plan Import Export Template.xlt is much simpler in content than the other template, with only two worksheets (Task_Table and Microsoft_Project) and seven fields (ID, Name, Duration, Start, Deadline, Resource Names, and Notes).
Create a new workbook by opening one of these two Excel templates, and then create your list of tasks, resources, and/or assignments, which can then be imported into Project. As you enter project data in Excel, be very careful about any data entered in a field that is a numerical field in Project, such as Duration, Work, Start, and Finish. Keep the following points in mind as you enter project data in an Excel template:
Duration should be entered as a text value, and it must include your desired time unit, such as 5d . Otherwise, your task durations will be set to 0 days.
Work and units can be entered as numerical data, but Project assumes default measures of work (hours) and units (percentage). You can also enter work as text data, with your desired time unit included, such as 40h .
Cost rates are imported as hourly rates unless a / and another time unit are appended.
Resource names cannot include commas, square brackets, or percent symbols.
Setting predecessors in Excel is a very tricky process because they use the Project Task ID number and not Excel's row number. Therefore, predecessors are probably best entered after the data has been imported into Project.
The default date format on the View tab of the Project Options dialog box sets the formatting for dates that are imported into Project. If you leave the Start and Finish fields blank in Excel, Project sets the current date as the start date for each task.
For information on time unit abbreviations, see "Entering Task Duration," p. 132 .
A quick way to add a list of tasks into a Project file is to use the new Project Guide sidepane to the left of the project file. To import tasks from Excel into a project using the sidepane, complete the following steps:
In the Sidepane, click the List the Tasks in the Project hyperlink.
In the Excel section of the List Tasks pane, click the hyperlink Import Wizard from the Import Tasks (see Figure 17.37).
Follow the process previously outlined in this section to import tasks from Excel into Project.
For additional information on using the Project Guide toolbar and the sidepane, see Chapter 2, "Learning the Basics of Microsoft Project," p. 29 .
When you are importing task start or finish dates, Project treats the imported dates as though you had typed them instead of letting Project calculate them. In other words, the tasks are assigned the soft constraint Start No Earlier Than (for fixed-start-date projects) or Finish No Later Than (for fixed-finish-date projects). You can reset these task constraints to As Soon As Possible or As Late As Possible after the tasks are imported into Project.
If you are importing dates and there is no time of day attached to the source date, Project
You can create pages for an intranet site or the Internet by exporting Project data to the HTML format, which is currently the standard format for Internet browsers. You can save Project data to the HTML format, but you cannot import Project data from the HTML format.
Another feature of Project 2003 is the ability to export project data in the XML format. XML is the standard language for describing and delivering data on the Web, just as HTML is the standard language for creating and displaying Web pages. The XML format is similar to HTML, and it is complementary to HTML for publishing Project data to Web pages and for viewing either on the Internet or on corporate
To save a project to an XML file, follow these steps:
Open the project file from which you want to export data.
Choose File, Save As to display the Save As dialog box.
Select the directory for the new XML file in the Save In list box and give the new file a name.
Select the XML Format (*.xml) file format in the Save as Type list box and click the Save button. The project is then saved as an XML file.
When saving a Project file in XML format, the entire project file is exported to the file; you cannot export only selected data from the plan.
Unlike with HTML files, Project can import as well as export XML files. To import an XML file, follow these steps:
If you want to append or merge the XML data into a project, open the project file into which you want to import the data. Otherwise, you do not need to have a project open.
Choose File, Open to display the Open dialog box.
Select the directory from the Look In list that contains XML files, and then select the file you want to open.
Select the XML Format (*.xml) file format in the Files of Type list box and click the Open button. The Import Wizard dialog box is displayed.
Click the Next button to display the Import Mode dialog box.
Select whether you want to import the file as a new project, append the data to an existing project, or merge the data into an existing project.
Click the Finish button.
The XML file is imported into either a new project or your active project, depending on your choice in step 6.
Project supports two ASCII text formats: the tab-delimited and comma-separated value (CSV) formats. The Import/Export maps for both of these formats are almost the same as the Import/Export maps for Excel, but with one key difference. With Excel, you can import or export task, resource, and assignment data tables
To export a list of project milestones to a text file, follow these steps:
Open the project plan from which you want to export the milestones.
Choose File, Save As.
Select the directory for the new text file in the Save In list box.
Select the file format in the Save as Type list box. For a tab-delimited file, select Text (Tab delimited) (*.txt). This format places tab characters between each field of data in a record (with quote marks
For a comma-delimited file, select CSV (Comma delimited) (*.csv). This format places commas between each field of data in a record (with quote marks surrounding field values that
Click the Save button. The Export Wizard dialog box appears.
Click the Next button to display the Map dialog box.
Select whether you want to use a new map or an existing map. Click the Next button, and the Map Options dialog box appears.
Select the type of datatasks, resources, or assignmentsyou want to export. Project allows you to choose only one of the three data types (see Figure 17.38).
Leave the Export Includes Headers box checked so that the exported data will be labeled.
Check the Include Assignment Rows in Output box only if you want assignments to be included. Remember that assignments will be indistinguishable from tasks or resources unless you include the Assignment field to identify them.
Select a Text Delimiter from the drop-down list if you want to use either the space or comma as a
Click the Next button. The Mapping dialog box for your selected data type appears.
In Figure 17.39 only two fields are to be exportedTask Name and Finish Dateand the Milestones filter has been selected. Notice that the Destination Table Name text box is not available for exporting or importing text-formatted data.
When the map is complete, click the Next button to display the End of Map Definition dialog box.
If you want to reuse the map you just created, click the Save button. Give the map a unique name, such as Export Milestones to Text (txt), to identify it as a map used for exporting Milestones to a text file.
Click the Finish button to create the file.
The resulting text file can be opened and
Importing from a text file is similar to importing from an Excel workbook, except that you can import only one type of data at a timeyou must import tasks, resources, and assignments separately. (See the previous section, "Importing Project Data from the Excel Format," for more information.) With text files, you are likely to encounter the same problems in matching field names that are found with Excel formats when the import source file was not originally exported from Project.
When importing from a text format, you have an additional option, which is grayed-out for exporting in Figure 17.38. You can use the File Origin text box to specify a different character set for the source data. The default is Windows (ANSI), but you can also select DOS or OS/2 (PC-8), or you can select Unicode. This maximizes your
Project 2003 has the ability to import a task list from Outlook. Because many people use Outlook to track their to-do lists of tasks, Project now makes it easy to import tasks directly from Outlook. Figure 17.41 shows a small list of tasks that has been entered into Outlook.
Figures 17.42 and 17.43 show the additional information that can be entered for each Outlook task, including notes and durations for each task. To add this information, double-click on the task and add the information.
To import a task list from Outlook into a project, follow these steps:
Type your task information into the Tasks section of Outlook. Include start date, duration, due date, and notes, if appropriate.
Open a blank project or the project into which you want to import the tasks.
Click Tools, Import Outlook Tasks. The Import Outlook Tasks dialog box appears (see Figure 17.44).
Select the tasks you want to import by clicking the check box in the first column for each task. Click the Select All button to select every task in the list.
Click the OK button to import the Outlook tasks.
The Outlook tasks are imported into your Project file in alphabetical order (see Figure 17.45). At this point, you can drag the imported tasks into the proper sequence, set task dependencies and constraints, and so on. Note that Project ignored the start date for each task in Outlook while it imported the tasks. The start date for each imported task is set to the start date of the project.
|< Day Day Up >|