Microsoft Office Project 2007 Inside Out
By Teresa S. Stover

Teresa S. Stover is a project management expert who has consulted with the Microsoft Office Project Team since version 4. She is an instructional designer and award-winning author with more than two decades of technical communication experience. Teresa is the author of countless user manuals, tutorials, and help systems — plus more than a dozen computer books, including Microsoft Office Project 2003 Inside Out and Microsoft Project Version 2002 Inside Out.

To learn more about other books on the 2007 Microsoft Office system, visit Microsoft Press.

• Produce a visual report

• Generate a visual report from a built-in template

• Create and edit visual report templates in Excel

• Configure a visual report in Excel

You’ll find plenty of reasons to transfer some of your Microsoft Office Project 2007 data into Microsoft Office Excel. For example, you can export project cost and earned value data to Office Excel, where you can create graphs — such as S-curves — to analyze project performance. You can even link Excel information in Project 2007 to automatically update when that information changes, for example, to update status for change requests that you’ve added to your schedule. If you are spoiled by the power of PivotTables for other kinds of management reporting you do, you can export Project information to Excel and pivot to your heart’s content. In Project 2007, the new visual reports are built-in templates designed to help you choose which Project data to display in Excel and how that data should be presented.

Produce a visual report

Visual reports are new in Project 2007 and are easy-to-build report templates that use Microsoft Office Excel 2003 or later (as well as Microsoft Office Visio Professional 2007) to transform Project 2007 task, resource, and assignment information into charts and graphs that communicate your project information more effectively. For example, in earlier versions of Microsoft Office Project, displaying an Excel graph of earned value required special toolbars and numerous steps to produce the earned value status you wanted. In Project 2007, you simply choose the Earned Value Over Time report, and the graph appears in Excel.

When you first start generating an Excel visual report, Project 2007 gathers the information requested for the selected template and stores it in a database. Then, the visual report template calls on an Excel template to generate the report in an Excel PivotChart. Unlike Project’s text-based reports, once data is set up for a visual report, you can configure the report to examine different fields over different time periods without generating a brand new report each time. For example, you could begin by analyzing cost overruns for each fiscal quarter and then drill down to view overruns by each week of the project. In addition, you can add, remove, or rearrange the fields you want to analyze.

You can modify existing visual reports or create your own report templates to do exactly what you want. You can use them on your own projects as well as publish them for other team members or project managers to use.

 Top of Page

Generate a visual report from a built-in template

Unlike the text reports that are available in Project 2007 and in earlier versions, visual reports transfer data to Excel and use Excel’s PivotTable feature to categorize and collate results. For example, suppose the executives on your project selection team ask to see cash flow by quarter for potential projects. Rather than use the text report of cash flow, which displays values for cash spent by time period, the Visual Cash Flow report generates an Excel chart that shows cash flows by quarter more clearly, as illustrated in Figure 1.

To generate a built-in visual report, do the following:

1. In Project 2007, click Visual Reports on the Reports menu.
2. To view only those visual report templates that use Excel, clear the Microsoft Office Visio check box and be sure to select the Microsoft Office Excel check box.
3. To view all the visual report templates that come with Project 2007, regardless of the category to which they belong, click the All tab (see Figure 2).



Figure 2 Specify whether you want to see report templates based on Excel or Visio and then select the report you want to generate.

Unless you create numerous custom visual report templates, it’s easy to find the reports you want on the All tab. However, if the list of reports grows unwieldy, select a category tab to see only the reports in that category. For example, the Task Usage category includes the Cash Flow Report template, whereas the Assignment Usage category includes templates for reporting baseline and budgeted costs and work as well as earned value over time.

4. To specify the level of detail that Project 2007 transfers to Excel, click a time period (Days, Weeks, Months, Quarters, or Years) in the Select Level Of Usage Data To Include In The Report box.

For projects with shorter durations, choose Days or Weeks. If a project spans a year or more, consider using Months, Quarters, or even Years.

 Note    If you want to save an OLAP (Online Analytical Processing) cube that contains the data for the report, click Save Data. This can save you time generating additional reports because Project 2007 doesn’t have to gather the reporting data required or transfer it to Excel.

5. To generate the report, click View.

Excel launches and generates a PivotChart using the data transferred from Project 2007. The first worksheet in the Excel file, which contains the PivotChart, is called Chart1. The second worksheet is labeled with the name of the report category and contains the data for the report.

 Top of Page

Create and edit visual report templates in Excel

The built-in visual report templates cover many of the project status and performance topics that project managers need, such as baseline cost and work, cash flow, earned value, and resource availability. If none of the built-in reports do exactly what you want, you can edit a template to fit your requirements or create a new custom template.

Because visual reports use Excel PivotCharts or Visio Pivot Diagrams to do the heavy report lifting, visual report templates are either Excel or Visio templates. Whether you edit a built-in template or create your own, you specify the fields you want to work with and the type of data on which you want to report.

Edit a built-in visual report template for Excel

To edit a built-in visual report template, do the following:

1. On the Reports menu, click Visual Reports.
2. Select the built-in visual report template you want to edit and then click Edit Template.

The Visual Reports Field Picker dialog box appears (see Figure 3).



Figure 3 You can specify the Project 2007 fields to include in the data cube for a visual report.
3. To add fields to the OLAP cube for the visual report, in the Available Fields list, select the fields you want and then click Add.

The fields appear in the Selected Fields list. Remove fields from the Selected Fields list by selecting the ones you want to remove and then clicking Remove. You can select multiple fields to add or remove by holding the CTRL key and clicking the fields.

4. Click the Edit Template button.

Project 2007 builds the OLAP cube based on the fields you selected. Excel launches using the built-in Excel template.

5. Make the changes you want to the settings in Excel and then save the Excel template.

Create a new visual report template for Excel

To create an Excel visual report template from scratch, do the following:

1. On the Reports menu, click Visual Reports.
2. Click New Template.

The Visual Reports – New Template dialog box appears with the three basic selections you must make to build a template (see Figure 4).



Figure 4 When you create a new visual report template, you must specify only a few basic elements.
3. If necessary, select the Excel option.
4. Under Select Data Type, choose the type of data you want to use as the basis for your report.

Visual reports are based on six different sets of information: Task Summary, Task Usage, Resource Summary, Resource Usage, Assignment Summary, and Assignment Usage. These data types determine the fields that Project 2007 adds to the OLAP cube, but you can add or remove fields as well.

5. To modify the fields for the template, click Field Picker and add or remove fields in the Available Fields list.
• To add fields to the new visual report’s OLAP cube, select the fields you want in the Available Fields list and then click Add.
• To remove fields from the Selected Fields list, select the fields and then click Remove.
6. Click OK in the Visual Reports – Field Picker dialog box and then click OK in the Visual Reports – New Template dialog box.

Excel launches and opens a blank PivotChart (see Figure 5).



Figure 5 Drag fields onto the PivotTable or into the PivotTable configuration boxes to build your report.
7. To build the PivotTable, drag the field you want to use for rows in the table to the area labeled Drop Row Fields Here. For example, to produce a resource report, drag the Resources field. Drag the field that you want to use for columns to the area labeled Drop Column Fields Here, such as a time period for an earned value report.
8. Drag the Project 2007 fields on which you want to report to the area labeled Drop Data Items Here.
9. When finished configuring the PivotTable, click the Microsoft Office Button and then click Save. In the Save As dialog box, make sure that the template is being saved to the folder where all the other templates are located. By default, this is in the \Users\username\AppData\Roaming\Microsoft\Templates\1033 folder.

Saving the template in this folder ensures that the template will appear in the Visual Reports – Create Reports dialog box.

10. If you’d rather save your custom template in another location, you can still make it appear in the Visual Reports dialog box. In that dialog box, select the Include Reports Template From check box and click Modify to specify the path that contains your custom templates.

 Top of Page

Configure a visual report in Excel

When Excel is open and displays one of your visual reports, you can use Excel PivotTable tools to configure what you see in the report. For example, you can change the time periods you see, add or remove fields in the chart, or display additional calculations.

Use one or more of the following techniques to change a visual report:

• To control the totals that appear in the chart, click the tab for the data worksheet, such as Task Usage for the Cash Flow Report, and then expand or collapse the groupings in the table.

When data in the worksheet is collapsed as it is by default, you see plus signs to the left of collapsed groupings. For example, for a time-based report, Q1, Q2, Q3, and Q4 for each year are collapsed, but the years are expanded (indicated by a minus sign to the left). To show more detail for some or all of the report, click the plus sign next to the group you want to expand (see Figure 6).



Figure 6 Expanding or collapsing time periods or other groups on the data worksheet controls the time periods or groups that appear in a visual report.
• To add fields within the chart, in the PivotTable Field list, select the check box for the field you want to add.

To remove a field, clear its check box.

• To filter the tasks, resources, or assignments included in the report, in the worksheet, click the down arrow in the first row. Expand the drop-down list to the level you want to see and then select the items you want to include (see Figure 7).



Figure 7 Select the tasks, resources, or assignments you want the report to include.
• To change the calculation that appears in the chart, in the ∑ Values section, right-click a field and then click Value Field Settings. In the Value Fields Settings dialog box, in the Summarize Value Field By list, click the type of calculation you want, such as the sum, average, minimum value, maximum value, and so on.

 Top of Page