I recently used MS Excel 2010′s Visual Basic for Applications to normalize and automate the reporting of test cases by severity. The purpose of this view is to answer the question, “Of the failed tests reported, how many of the defects are high severity vs. how many are low?” So instead of just seeing just a pass/fail percentage like this:

You could publish the customer impact of the associated defects like this:

HP ALM 11 natively reports test case results and defect results, but not both in one view. If you’d like to see failed tests with their associated, you need a custom SQL query and you’ll end up with very generic tabular data like this:

Sorting and formatting the data was painful.  The following scripts provide automation for most of the steps involved using Excel’s built in VBA macro system.

All we care about is the highest severity level defect linked to each test case.  The others (like the second row above) can be discarded.  (If a test/feature fails on a Show Stopper, we don’t care if there are also typos on it, at least not yet.)  This is the VBA to de-dupe by sev:

'
' Normalize duplicate failed tests
'

' Skip the header row, process non blank rows
' Need to constrain this by an end-of-data function, not an # of rows integer
For i = 2 To 200
    ' Don't process blanks
    If Cells(i, 1).Value <> "" Then
        ' If there is >1 defect logged against the same test
        If Cells(i, 1).Value = Cells(i + 1, 1).Value Then
            ' If the current row has lesser or equal severity defect than the following row, delete the current row
            If Cells(i, 6).Value >= Cells(i + 1, 6).Value Then
                Cells(i, 1).EntireRow.Delete
            ' Otherwise delete the following row
            Else
            Cells(i + 1, 1).EntireRow.Delete
            End If
        ' Reset i to account for the row deleted
        i = i - 1
        End If
    End If
Next i

' Sort by sev
    Columns("F:F").Select
    ActiveWorkbook.Worksheets("Failed Tests with Severities").sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Failed Tests with Severities").sort.SortFields. _
        Add Key:=Range("F1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Failed Tests with Severities").sort
        .SetRange Range("A2:J96")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

This summarizes the results:

'
' SummaryTable Macro
'
    Range("L2") = "Show Stopper"
    Range("L3") = "High"
    Range("L4") = "Medium"
    Range("L5") = "Low"
    Range("L6") = "Passed"
    Range("L7") = "Blocked"
    Range("M2") = WorksheetFunction.CountIf(Columns(6), "1-Show Stopper")
    Range("M3") = WorksheetFunction.CountIf(Columns(6), "3-High")
    Range("M4") = WorksheetFunction.CountIf(Columns(6), "4-Medium")
    Range("M5") = WorksheetFunction.CountIf(Columns(6), "5-Low")
    Range("M6") = 1
    Range("M7") = 1

And this graphs them:
‘
‘ Graph Macro
‘

Range(“L2:M7″).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPieExploded
ActiveChart.SetSourceData Source:=Range( _
“‘Failed Tests with Severities’!$L$2:$M$7″)
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels
ActiveChart.SeriesCollection(1).DataLabels.Select
ActiveChart.SeriesCollection(1).HasLeaderLines = False
Selection.Position = xlLabelPositionOutsideEnd

ActiveChart.SeriesCollection(1).Select
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
ActiveChart.SeriesCollection(1).Explosion = 0
ActiveChart.SeriesCollection(1).Explosion = 1
ActiveChart.SeriesCollection(1).Points(1).Select
Selection.Explosion = 15
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Solid
End With
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(192, 0, 0)
.Transparency = 0
.Solid
End With
ActiveChart.SeriesCollection(1).Points(2).Select
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(192, 0, 0)
.Solid
End With
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Solid
End With
ActiveChart.SeriesCollection(1).Points(3).Select
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Solid
End With
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 117, 117)
.Transparency = 0
.Solid
End With
ActiveChart.SeriesCollection(1).Points(4).Select
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 117, 117)
.Solid
End With
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 167, 167)
.Transparency = 0
.Solid
End With
ActiveChart.SeriesCollection(1).Points(5).Select
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 167, 167)
.Solid
End With
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 176, 80)
.Transparency = 0
.Solid
End With
ActiveChart.SeriesCollection(1).Points(6).Select
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 176, 80)
.Solid
End With
ActiveChart.ChartGroups(1).FirstSliceAngle = 322
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Transparency = 0
.Solid
End With
ActiveChart.Legend.Select
Selection.Format.TextFrame2.TextRange.Font.Size = 12
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.Format.TextFrame2.TextRange.Font.Size = 12