SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 3 of 3
  1. #1
    New Member
    Join Date
    Aug 2010
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Defect Aging Report

    Hi,
    I have a requirement to create a Defect Aging Report. In other words, how long each defect took to transition through the various statuses. Has anyone done this before? If so, can someone paste their code if it is remotely similar so that I can use that as a template to build one for myself.

    Either SQL queries or the code to be used in Post Processing would be greatly helpful.

    Thanks,
    Raj

  2. #2
    Advanced Member
    Join Date
    Aug 2004
    Location
    Wellington, New Zealand
    Posts
    797
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)
    Total Downloaded
    0

    Re: Defect Aging Report

    Step 1: Pull out the defect audit details, so that you can see when each state transition occurred.

    <font class="small">Code:</font><hr /><pre>select AU_ENTITY_ID As 'Defect'
    , BG_SEVERITY As 'Severity'
    , BG_USER_11 As 'Use Case'
    , BG_USER_04 As 'Defect Type'
    , BG_USER_08 As 'Resolution Code'
    , AU_TIME As 'DateTime'
    , AP_NEW_VALUE As 'New Value'
    , BG_USER_14 As 'Resp Org'
    ,BG_USER_13 As 'Retest Failures'
    from audit_properties
    inner join audit_log on ap_action_id = au_action_id
    inner join bug on au_entity_id = cast(bg_bug_id as varchar(200))
    where AP_TABLE_NAME = 'BUG'
    and AP_FIELD_NAME = 'BG_STATUS'
    and BG_DETECTED_IN_RCYC = 1043
    and BG_USER_04 &lt;&gt; 'Dev Integration Test'
    and BG_DETECTION_DATE &gt;= @Start_Date@
    order by AU_ENTITY_ID asc, AU_TIME asc</pre><hr />

    Step 2: do some post processing, and do date calculations.
    You'll need to do some heavy modifications to this as I wrote for a specific implementation. Hopefully you can follow it OK - I commented it more than I usually do!

    <font class="small">Code:</font><hr /><pre>Sub QC_PostProcessing()
    Dim MainWorksheet As Worksheet
    ' Make sure your worksheet name matches!
    Set MainWorksheet = ActiveWorkbook.Worksheets("Query1")
    Dim DataRange As Range
    Dim col As Integer
    Set DataRange = MainWorksheet.UsedRange
    ' Now that you have the data in DataRange you can process it.
    Range("J2:J"&amp; Cstr(DataRange.Rows.Count)).Select
    Selection.FormulaR1C1 = _
    "=IF(RC[-3]&lt;&gt;""Closed"",IF(R[1]C[-9]=RC[-9],R[1]C[-4]-RC[-4],""""),"""")"
    Selection.NumberFormat = "0"

    Range("J2:AH"&amp; Cstr(DataRange.Rows.Count)).Select
    Selection.NumberFormat = "0"

    Range("J1").Select
    Selection.Value = "Time in State"

    '-----------'
    ' Variables '
    '-----------'

    Dim CurrentRow As Long, _
    CurrentBug As Integer, _
    CurrentState As String, _
    TableRow As Integer, _
    TimeInCurrentState As Integer, _
    NextBug As Integer, _
    Continue As Boolean, _
    RowLimit As Integer

    '----------------'
    ' Build Headings '
    '----------------'

    ' SUMMARY STATES
    Range("K1").Value = "Defect ID"
    Range("L1").Value = "Resp Org"
    Range("M1").Value = "Severity"
    Range("N1").Value = "Use Case"
    Range("O1").Value = "Defect Type"
    Range("P1").Value = "Resolution Code"
    Range("Q1").Value = "Time in Review"
    Range("R1").Value = "Time in Defered"
    Range("S1").Value = "Time in Clarification"
    Range("T1").Value = "Time in Dev"
    Range("U1").Value = "Time to Deploy"
    Range("V1").Value = "Time to Test"
    Range("W1").Value = "Age"
    Range("X1").Value = "Retest Failures"

    ' REAL STATES
    Range("Z1").Value = "Review"
    Range("AA1").Value = "Deferred"
    Range("AB1").Value = "Clarification Required"
    Range("AC1").Value = "Assigned"
    Range("AD1").Value = "In Progress"
    Range("AE1").Value = "Third Party"
    Range("AF1").Value = "Ready To Build"
    Range("AG1").Value = "Ready To Release SIT"
    Range("AH1").Value = "Ready To Release AT"
    Range("AI1").Value = "SIT"
    Range("AJ1").Value = "AT"

    '--------------------'
    ' Set Start Position '
    '--------------------'

    CurrentRow = 2
    TableRow = 2
    ' Get Bug Info
    CurrentBug = Range("A" &amp; CStr(CurrentRow)).Value
    Range("A" &amp; CStr(CurrentRow)).Activate
    NextBug = ActiveCell.Offset(1, 0).Value
    RowLimit = DataRange.Rows.Count


    '---------------------'
    ' Build Summary Table '
    '---------------------'


    Do While CurrentRow &lt; RowLimit
    CurrentBug = Range("A" &amp; CStr(CurrentRow)).Value
    Range("A" &amp; CStr(CurrentRow)).Activate

    '| Write ID
    Range("K" &amp; CStr(TableRow)).Value = CurrentBug

    '| Write Responsible Org
    Range("L" &amp; CStr(TableRow)).Value = ActiveCell.Offset(0, 7).Value

    '| Write Severity
    Range("M" &amp; CStr(TableRow)).Value = ActiveCell.Offset(0, 1).Value

    '| Write Use Case
    Range("N" &amp; CStr(TableRow)).Value = ActiveCell.Offset(0, 2).Value

    '| Write Defect Type
    Range("O" &amp; CStr(TableRow)).Value = ActiveCell.Offset(0, 3).Value

    '| Write Resolution Code
    Range("P" &amp; CStr(TableRow)).Value = ActiveCell.Offset(0, 4).Value

    '| Write Retest Failures
    Range("X" &amp; CStr(TableRow)).Value = ActiveCell.Offset(0, 8).Value
    Continue = True

    Do While Continue = True
    Continue = False

    CurrentState = Range("G" &amp; CStr(CurrentRow)).Value

    If CStr(Range("J" &amp; CStr(CurrentRow)).Value) = "" Then
    TimeInCurrentState = 0
    Else
    TimeInCurrentState = CInt(Range("J" &amp; CStr(CurrentRow)).Value)
    End If

    Select Case LCase(CurrentState)
    Case "review"
    '| ReviewTime
    Range("Q" &amp; CStr(TableRow)).Value = Range("Q" &amp; CStr(TableRow)).Value + TimeInCurrentState
    Range("Z" &amp; CStr(TableRow)).Value = Range("Z" &amp; CStr(TableRow)).Value + TimeInCurrentState

    Case "deferred"
    '| Deferred
    Range("R" &amp; CStr(TableRow)).Value = Range("R" &amp; CStr(TableRow)).Value + TimeInCurrentState
    Range("AA" &amp; CStr(TableRow)).Value = Range("AA" &amp; CStr(TableRow)).Value + TimeInCurrentState

    Case "clarification required"
    '| Clarification
    Range("S" &amp; CStr(TableRow)).Value = Range("S" &amp; CStr(TableRow)).Value + TimeInCurrentState
    Range("AB" &amp; CStr(TableRow)).Value = Range("AB" &amp; CStr(TableRow)).Value + TimeInCurrentState

    Case "assigned"
    '| Dev Time
    Range("T" &amp; CStr(TableRow)).Value = Range("T" &amp; CStr(TableRow)).Value + TimeInCurrentState
    Range("AC" &amp; CStr(TableRow)).Value = Range("AC" &amp; CStr(TableRow)).Value + TimeInCurrentState
    Case "in progress"
    '| Dev Time
    Range("T" &amp; CStr(TableRow)).Value = Range("T" &amp; CStr(TableRow)).Value + TimeInCurrentState
    Range("AD" &amp; CStr(TableRow)).Value = Range("AD" &amp; CStr(TableRow)).Value + TimeInCurrentState
    Case "third party"
    '| Dev Time
    Range("T" &amp; CStr(TableRow)).Value = Range("T" &amp; CStr(TableRow)).Value + TimeInCurrentState
    Range("AE" &amp; CStr(TableRow)).Value = Range("AE" &amp; CStr(TableRow)).Value + TimeInCurrentState

    Case "ready to build"
    '| Deploy Time
    Range("U" &amp; CStr(TableRow)).Value = Range("U" &amp; CStr(TableRow)).Value + TimeInCurrentState
    Range("AF" &amp; CStr(TableRow)).Value = Range("AF" &amp; CStr(TableRow)).Value + TimeInCurrentState
    Case "ready to release sit"
    '| Deploy Time
    Range("U" &amp; CStr(TableRow)).Value = Range("U" &amp; CStr(TableRow)).Value + TimeInCurrentState
    Range("AG" &amp; CStr(TableRow)).Value = Range("AG" &amp; CStr(TableRow)).Value + TimeInCurrentState
    Case "ready to release at"
    '| Deploy Time
    Range("U" &amp; CStr(TableRow)).Value = Range("U" &amp; CStr(TableRow)).Value + TimeInCurrentState
    Range("AH" &amp; CStr(TableRow)).Value = Range("AH" &amp; CStr(TableRow)).Value + TimeInCurrentState

    Case "sit"
    '| Testing Time
    Range("V" &amp; CStr(TableRow)).Value = Range("V" &amp; CStr(TableRow)).Value + TimeInCurrentState
    Range("AI" &amp; CStr(TableRow)).Value = Range("AI" &amp; CStr(TableRow)).Value + TimeInCurrentState
    Case "at"
    '| Testing Time
    Range("V" &amp; CStr(TableRow)).Value = Range("V" &amp; CStr(TableRow)).Value + TimeInCurrentState
    Range("AJ" &amp; CStr(TableRow)).Value = Range("AJ" &amp; CStr(TableRow)).Value + TimeInCurrentState
    End Select

    '| Write Age
    Range("W" &amp; CStr(TableRow)).Value = _
    (Range("Q" &amp; CStr(TableRow)).Value + _
    Range("R" &amp; CStr(TableRow)).Value + _
    Range("S" &amp; CStr(TableRow)).Value + _
    Range("T" &amp; CStr(TableRow)).Value + _
    Range("U" &amp; CStr(TableRow)).Value + _
    Range("V" &amp; CStr(TableRow)).Value)

    NextBug = Range("A" &amp; CStr(CurrentRow + 1)).Value
    If NextBug = CurrentBug Then Continue = True
    CurrentRow = CurrentRow + 1

    Loop

    TableRow = TableRow + 1

    Loop

    '| Now Loop back up and clean out the 'zero' values, as they interfere with the statistical analysis
    Do While TableRow &gt; 1
    Range("G" &amp; CStr(TableRow)).Activate
    For col = 0 to 17
    If ActiveCell.Offset(0, col).Value = 0 Then ActiveCell.Offset(0, col).Value = ""
    Next
    TableRow = TableRow -1
    Loop

    Range("A1").Activate
    Range("A:J").Delete
    Columns.Autofit

    Columns("O:Z").EntireColumn.Hidden = True

    Range("A1","N1").Interior.ColorIndex = 25
    Range("A1","N1").Font.ColorIndex = 2

    End Sub</pre><hr />
    Regards,

    Alex
    ... just another Tester ...

  3. #3
    New Member
    Join Date
    Aug 2010
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Defect Aging Report

    Thanks a lot. Im trying to customize the above post processign code to suit my needs. Shall let you know if I face any difficulties. Thanks Again [img]/images/graemlins/smile.gif[/img]

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 11.54%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 03:31 AM.

Copyright BetaSoft Inc.