Chapter 3
Defect Tracking
Overview
You can change the structure of Perforce jobs by altering the job specification (see the Perforce System Administrator's Guide for details). The queries in this chapter are based on a job specification containing the following fields:
dependency
|
release
|
description
|
reportedby
|
job
|
reporteddate
|
modifiedby
|
severity
|
modifieddate
|
status
|
ownedby
|
subsystem
|
priority
|
type
|
You cannot create a query or report on one server and then execute it against a server which has a different job specification. If you change the job specification, you might need to change the corresponding P4Report queries, especially if you rename or remove fields.
Note that job status can be easily changed, and a job can be reopened after being closed. The examples in this section assume that the defect tracking process does not allow jobs to be reopened.
For details about supported SQL functions, refer to Appendix B, P4Report Functions.
What jobs are unassigned?
select job from jobs
where ownedby = '' and status='open';
What jobs were entered by a specified user?
select * from jobs
where reportedby = 'tonyz' and status='open';
What jobs of a specified type are open?
select * from jobs
where type = 'bug' and status = 'open';
How many jobs were closed in a specified branch this quarter?
select count(job) from jobs
where filespec='//depot/main/release1.1...'
and status='closed'
and quarter(modifieddate)=quarter(curdate());
What changes are not associated with any fixes?
select change, description, fixes.job
from changes left outer join fixes
where fixes.job is NULL;
A simple Excel pie chart: display open jobs by job type
You can create an Excel pie chart that displays the percentage of total open jobs assigned to each component you track using Perforce. Note that the Perforce job specification can be customized. This example assumes that your job specification includes a "status" field and a "subsystem" field.
To load the data, perform the following steps:
- Launch Excel. An empty spreadsheet is displayed.
- Choose Data>Get External Data>New Database Query... The Choose Data Source dialog is displayed.
- On the Databases tab, choose the Perforce data source from the list of data sources and click OK. The Query Wizard - Choose Columns dialog is displayed.
- Expand the jobs table and move the status and subsystem columns into the Columns in your query list.
- Click Next. The Filter Data dialog is displayed.
- Click the status field. In the Only include rows where group, choose equals from the left drop-down list of conditions, and enter "open" in the field on the right.
- Click Next. The Sort Data dialog is displayed.
- From the drop-down list, choose "subsystem" and click Next. The Finish dialog is displayed. Click Finish to return the data to your spreadsheet.
- On your spreadsheet, Excel displays the Returning External Data to Microsoft Excel dialog. Specify where you want the data placed and click OK. Two columns of data are loaded into your spreadsheet.
To create the chart from the data you loaded, perform the following steps:
- Select the data by clicking the buttons at the top of the columns. (For example, if you loaded data starting at the top right cell, click the A and B buttons.)
- Choose Data>PivotTable and PivotChart Report. The PivotTable and PivotChart wizard is launched and displays the Step 1 of 3 dialog.
- Select the Microsoft Excel list or database and PivotTable options and click Next. The Step 2 of 3 dialog is displayed, listing the range of data in the selected columns.
- Click Next. The Step 3 of 3 dialog is displayed, asking where you want to put the chart.
- Choose New worksheet and click Finish. Excel display the PivotTable designer.
- Drag the status field, followed by the subsystem field, from the pallet to the report heading fields as shown in the following figure.
Excel displays a table showing your data summarized by subsystem.
- Right-click the summarized list and choose PivotChart from the context menu. Excel displays a bar chart of your data.
- To change the chart type, right-click the chart and choose Chart Type... from the context menu. To display a pie chart, choose Pie from the Standard Types tab and specify your preferred display options.
A simple Excel bar chart: bug totals for the last four quarters
This chart displays the number of bugs opened in the each of the last four quarters. To create the chart, perform the following steps:
- Launch Excel. A new, empty spreadsheet is displayed.
- Choose Data>Get External Data>New Database Query... The Choose Data Source dialog is displayed.
- On the Databases tab, choose the Perforce data source from the list of data sources and click OK. The Query Wizard - Choose Columns dialog is displayed.
- Expand any table, choose any one column, and click > to move the column into the Columns in your query list. It does not matter which column you choose, because you will replace the entire query with your own SQL.
- Click Next. The Filter Data dialog is displayed.
- Click Next. The Sort Data dialog is displayed.
- Click Next. The Finish Dialog is displayed.
- Choose the View or edit data in Microsoft Query option and click Finish. Microsoft Query is launched, displaying your initial query.
- Choose View>SQL... The SQL dialog is displayed.
- Enter the following SQL query in the SQL window and click OK:
select jobs.job,
concat(concat(year(reporteddate), ' '),
concat('q',quarter(reporteddate))) from jobs jobs
where (jobs.type='bug')
and timestampdiff(7,reporteddate,curtimestamp())<3)
order by 2
- Choose File>Return Data to Microsoft Excel. The data is returned to your spreadsheet.
To format the bar chart, perform the following steps:
- Select the data: click the top right cell where the data starts, scroll to the bottom left cell and shift-click it. Excel highlights the cells you selected.
- Choose Data>PivotTable and PivotChart Report. The PivotTable and PivotChart wizard is launched and displays the Step 1 of 3 dialog.
- Select the Microsoft Excel list or database and PivotTable options and click Next. The Step 2 of 3 dialog is displayed, listing the range of data in the selected columns.
- Click Next. The Step 3 of 3 dialog is displayed, asking where you want to put the chart.
- Choose New worksheet and click Finish. Excel displays the PivotTable designer.
- Drag the job field, followed by the expr field, from the pallet to the report heading fields as shown in the following figure.
Excel displays a table showing your data summarized by subsystem.
- Right-click the summarized list and choose PivotChart from the context menu. Excel displays a bar chart of your data, such as the example in the following figure.
Please send comments and questions about this manual to
manual@perforce.com.
Copyright 2002-2005 Perforce Software. All rights reserved.
Last updated: 12/28/05