Problem:

Can I use Microsoft Excel to build reports containing TrackRecord project data?

Resolution:

Microsoft Excel is not a supported feature of the TrackRecord project; however, TrackRecord provides an uninstalled version. Consult Microsoft Excel's online help for information about manipulating spreadsheet data, generating graphs, and formatting cells.

Microsoft Excel allows you build three types of reports of TrackRecord project data: Distribution Trend Aging

Any of these reports can be viewed as raw data, but the first two are best viewed as graphs.

To generate a report from TrackRecord data:

1. Double-click the TrackRecord Excel Reports.XLS file in the following directory:
   
   • For 6.0.2: ProgramFiles\Compuware\TrackRecord\Examples\Excel\Reports
   • For 6.0.1: ProgramFiles\Compuware\TrackRecord
2. Click Enable Macros.
3. Select New or Update next to the type of report/data with which you want to work. New lets you create a new report, and Update lets you modify an existing report.
4. Log in to TrackRecord using the provided login box.
5. Step through the TrackRecord Reporting Wizard screens that follow.

Excel Reports: Distribution

The most common type of report, showing the breakdown of items across some field or fields.

For example, "Show me all items by Priority," where you end up with a column for each Priority value and a count/value in each column. You can choose an additional field to get even more granularity, such as "Show me all items by Status and Priority," where the values/counts in each Status column are broken down by Priority as well.

Typical distribution fields for defects are: Status, Priority, Assigned To, Project.

Excel Reports: Trend

The second most common type of report gives you a report (e.g. a graph and/or table) of when things happened, an historical analysis with the x-axis being date/time. For example, "Show me all items that have been Opened and Closed," where you will end up with two bars (or values) over some time period. In this example, you will probably want to see that the numbers of items Closed (or the rate at which items are being closed) is growing faster than the number of items Opened (or the rate at which items are being opened). This type of analysis helps you understand where you are in a project life-cycle, and whether or not you are in trouble. Using the Macro

The macro lets you Trend against either Status, Action, or Workflow.

• Status - Trend against things like "Open" and "Closed" -- no matter how the items actually got into that state. The most common choice.
• Action - Trend against what actions have been taken on an item (e.g. "Resolve" or "Enter"), no matter what state that action activated.
• Workflow - Trend against a specific Action-Status pair (e.g. "Resolve-Pending Validation").

The choices "Last Modified" and "Date Created" are always available as trend options no matter what trend-type you choose. You can specify up to 4 things to trend against (e.g. 4 states, or 4 actions, or 4 action-state pairs). In addition, you can specify one additional field for grouping. For example, if you choose to Trend against the "Open" state, and group by Priority, you will get a break down of how many items are getting into the "Open" state over time, with each time period broken down by Priority.

The macro also lets you specify you date range and grouping options. For example, you can choose to restrict items to a certain start and end date, or to show/hide empty dates that have no data.

Grouping Data

You can also choose how granular the date grouping should be. For example, if you want to know how any items are being closed by week, you would choose to group by "7 days." If you do not specify a grouping, every day is shown as a unique column in the resulting table/graph.

In short, this report gives you a picture of how you have been doing, and therefore the ability to predict where you might be going (hence the term "trend").

Excel Reports: Aging

This type of report is useful for post-mortem analysis of a project, or for determining how you are doing against your SLAs (service level agreements). It is used to return the average number of days an item is in a given state. For example, "show me the average number of days my defects were Open, broken down by Priority;" you may have company policies that state P1 bugs must be fixed within 48 hours, P2 within 1 week, and so on. This report lets you track these conditions.