SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 3 of 3
  1. #1
    Apprentice
    Join Date
    Feb 2011
    Location
    Lisboa, Portugal
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Report - How much time a bug passed in each status

    Hello,

    I need create a report that count the time that each defect passed in each status.
    I have thought may its possible calculated with the difference between the times in modified field.

    Anyone can help me?

    Thanks,
    Daniel

  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: Report - How much time a bug passed in each status

    I have written an Excel Report that achieves this.. I'll be the first to suggest there's a better/more elegant way, but it works.


    Here's what I have.. hopefully you are able to modify it to suit your needs - you'll need to modify the 'STATES' headings and SUMMARY TABLE section if you use different state values.

    The Post Processing assumes that:
    1. The final state for any defect is 'Closed'
    2. There isn't a major timezone difference between your QC server [AU_TIME] and the local machine generating the report [NOW()]


    QUERY:
    <font class="small">Code:</font><hr /><pre>select AU_ENTITY_ID As 'Defect'
    , BG_SEVERITY As 'Severity'
    , AU_TIME As 'DateTime'
    , AP_NEW_VALUE As 'New Value'
    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'
    order by AU_ENTITY_ID asc, AU_TIME asc</pre><hr />

    POST PROCESSING:
    <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("F2:F" &amp; CStr(DataRange.Rows.Count)).Select
    Selection.FormulaR1C1 = _
    "=IF(RC[-2]&lt;&gt;""Closed"",IF(R[1]C[-5]=RC[-5],R[1]C[-3]-RC[-3],Now()-RC[-3]),"""")"
    Selection.NumberFormat = "0"

    Range("F2:P" &amp; CStr(DataRange.Rows.Count)).Select
    Selection.NumberFormat = "0"

    Range("F1").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 '
    '----------------'

    Range("H1").Value = "Defect ID"
    Range("I1").Value = "Severity"
    Range("J1").Value = "Lifespan"

    ' STATES
    Range("K1").Value = "New"
    Range("L1").Value = "Open"
    Range("M1").Value = "Assigned"
    Range("N1").Value = "Deferred"
    Range("O1").Value = "Fixed"
    Range("P1").Value = "Retest"

    '--------------------'
    ' 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("H" &amp; CStr(TableRow)).Value = CurrentBug

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

    Continue = True

    Do While Continue = True
    Continue = False

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

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

    Select Case LCase(CurrentState)
    Case "new"
    Range("K" &amp; CStr(TableRow)).Value = Range("K" &amp; CStr(TableRow)).Value + TimeInCurrentState

    Case "open"
    Range("L" &amp; CStr(TableRow)).Value = Range("L" &amp; CStr(TableRow)).Value + TimeInCurrentState

    Case "assigned"
    Range("M" &amp; CStr(TableRow)).Value = Range("M" &amp; CStr(TableRow)).Value + TimeInCurrentState

    Case "deferred"
    Range("N" &amp; CStr(TableRow)).Value = Range("N" &amp; CStr(TableRow)).Value + TimeInCurrentState

    Case "fixed"
    Range("O" &amp; CStr(TableRow)).Value = Range("O" &amp; CStr(TableRow)).Value + TimeInCurrentState

    Case "retest"
    Range("P" &amp; CStr(TableRow)).Value = Range("P" &amp; CStr(TableRow)).Value + TimeInCurrentState

    End Select

    '| Write Age
    Range("J" &amp; CStr(TableRow)).Value = _
    (Range("K" &amp; CStr(TableRow)).Value + _
    Range("L" &amp; CStr(TableRow)).Value + _
    Range("M" &amp; CStr(TableRow)).Value + _
    Range("N" &amp; CStr(TableRow)).Value + _
    Range("O" &amp; CStr(TableRow)).Value + _
    Range("P" &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("K" &amp; CStr(TableRow)).Activate
    For col = 0 To 5
    If ActiveCell.Offset(0, col).Value = 0 Then ActiveCell.Offset(0, col).Value = ""
    Next
    TableRow = TableRow - 1
    Loop

    Range("A1").Activate
    Range("A:G").Delete
    Columns.AutoFit

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

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

    Alex
    ... just another Tester ...

  3. #3
    Apprentice
    Join Date
    Feb 2011
    Location
    Lisboa, Portugal
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Report - How much time a bug passed in each status

    Thanks,

    I will try.

    Regards,
    Daniel

 

 

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 11:51 AM.

Copyright BetaSoft Inc.