HPQC Excel Report: Defect LifeCycle TimeSpan

The following code can be used to see how long it takes from a defect is submitted to defect closure.

Our defects goes from Submitted (via Assigned, Open, Fixed, Verified) to Closed.

 

This simple query is all it takes to see how long it took from detection to closure: 

SELECT

BUG.BG_BUG_ID as “Defect Id”,

left(BG_SUMMARY, 6) as “Screen”, — We add screen unique ID as first 6 bytes of defect.

BG_PRIORITY as “Priority”,

BG_SEVERITY as “Severity”,

datediff(“d”, BG_CLOSING_DATE, AU_TIME) as “Fix Time”, — Calculate LifeSpan

AU_TIME as “Submitted”, — When defect was submitted

BG_CLOSING_DATE as “Closing Time” — When defect was closed (date only unfortunatly)

FROM audit_log, audit_properties, bug

WHERE

AP_NEW_VALUE = ‘Submitted’ — (initial status; change to “New” or appropriate) 

and AU_Action_ID = AP_Action_ID — table connect

AND AU_ENTITY_ID = BG_BUG_ID — table connect

AND AU_ENTITY_TYPE = ‘BUG’

AND AP_FIELD_NAME = ‘BG_STATUS’

AND BG_STATUS = ‘Closed’ — can only see entire LifeSpan for closed defects

order by “Priority”, “Severity”, “Fix Time” — Want to see how long it took for high priority defects first.

 

In addition, Post-processing can be done like this to format the output in the Excel Spreadsheet:

Sub QC_PostProcessing()

Dim MainWorksheet As Worksheet

‘ Make sure your worksheet name matches!

Set MainWorksheet = ActiveWorkbook.Worksheets(“Sheet1″)

Dim DataRange As Range

Set DataRange = MainWorksheet.UsedRange

 

    ‘ Set header row to bold

    Rows(“1:1″).Select

    Selection.Font.Bold = True

    ‘ Set colum “Fix Time” to bold

    Selection.Find(What:=”Fix Time”, After:=ActiveCell, LookIn:=xlFormulas, _

        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

        MatchCase:=False, SearchFormat:=False).Activate

    activecell.entireColumn.select

    Selection.Font.Bold = True

 

    Cells.Select

    Cells.EntireColumn.AutoFit

    ‘ Borders

    DataRange.Select

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone

    Selection.Borders(xlDiagonalUp).LineStyle = xlNone

    With Selection.Borders(xlEdgeLeft)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlEdgeTop)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlEdgeBottom)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlEdgeRight)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlInsideVertical)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlInsideHorizontal)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    DataRange.Select

End Sub 

 

 

This gives output like this: 

Defect Id	Screen	Priority	Severity	Fix Time	Submitted	Closed
54	abc123	1 – High	1. Critical defect	-21	21.12.2010 16:01	11.01.2011 00:00
90	abc321	1 – High	1. Critical defect	-8	04.01.2011 10:19	12.01.2011 00:00
91	abc213	1 – High	1. Critical defect	-8	04.01.2011 11:48	12.01.2011 00:00

About these ads