test execution report from test lab

There are so many different ways to pull the data from QC for reports.
1) You can create a small program using the C#.Net
2) You can use the third party tool like Reliable Business Reporting, Inc.(http://www.rbreporting.com/)
3) You can write a small macro to generate your report in EXCEL using VB script.

Here is the questions :

How to pull a report from the Test Lab describing a high level execution to show Test Folders and their Status?

Ans:  You may wish to create a macro to pull the metrics out. I prefer this over the excel report generator.

Here is some VBA to pull out test set metrics. Just change the connection string with your details...

Dim tdc As TDConnection

Sub UpdateStats()

'create QC connection
QCconnect_silent "URL", "DOMAIN", "PROJECT", "USERNAME", "PASSWORD"
'process tests
getMetrics
'disconnect
QCdisconnect

End Sub

Private Sub QCconnect_silent(url As String, domain As String, project As String, user As String, pass As String)
'# Creates a connection to QC
On Error GoTo connected
'create the connection object
Set tdc = CreateObject("TDApiOle80.TDConnection")
tdc.InitConnectionEx url
'login to QC
tdc.Login user, pass
'connect to project
tdc.Connect domain, project
Application.StatusBar = "Connected to Quality Centre...."
Exit Sub
connected:
Exit Sub
End Sub


Private Sub QCdisconnect()
'# disconnect from QC and release connection
'check if connection exists
If (tdc Is Nothing = False) Then
'disconnect, logout and release connection
tdc.Disconnect
tdc.Logout
tdc.ReleaseConnection
Application.StatusBar = "Disconnnected from Quality Centre...."
End If
End Sub


Private Sub getMetrics()
'create variables
Dim TestSetFolderPath, TestName, TestInstance, TestSetName, tsl
Dim tsf As TestSetFactory
Dim tf As TestFactory
Dim filterSet As TDFilter
Dim Tset As TestSet
Dim testInLab, testList
Dim test As TSTest
Dim row As Integer
Dim execDate, diff
Dim totalTestCount, runCount, totalRunCount, passed, totalPassed, failed, totalFailed As Integer
Dim runThisWeek, passedThisWeek, failedThisWeek, totalRunThisWeek, totalPassedThisWeek, totalFailedThisWeek As Integer
'write out column headings
writeHeadings
'set initial values
row = 3
totalRunCount = 0
totalPassed = 0
totalFailed = 0
passed = 0
failed = 0
runCount = 0
runThisWeek = 0
passedThisWeek = 0
failedThisWeek = 0
'get list of test cycles
Set tsf = tdc.TestSetFactory
Set tsl = tsf.NewList("")
'loop for each test cycle
For Each Tset In tsl
Application.StatusBar = "Getting Metrics for " & Tset.Name & "...."
'get list of tests in cycle
Set testInLab = Tset.TSTestFactory
Set testList = testInLab.NewList("")
'output cycle folder\name and number of tests
Range("A" & row).Value = Tset.TestSetFolder & "\" & Tset.Name
Range("B" & row).Value = testList.Count
'keep total of tests
totalTestCount = totalTestCount + testList.Count
'loop for each test in the cycle
For Each test In testList
'get the execution date and compare with todays date
execDate = test.Field("TC_EXEC_DATE")
diff = DateDiff("d", execDate, Date)
'check if test HAS been run
If test.Status <> "No Run" Then
'increment count
runCount = runCount + 1
'check if the run was this week - 0 to 7 days
If diff < 8 Then runThisWeek = runThisWeek + 1
End If
'check if test has passed
If test.Status = "Passed" Then
'increment count
passed = passed + 1
'check if the run was this week - 0 to 7 days
If diff < 8 Then passedThisWeek = passedThisWeek + 1
End If
'check if test has failed
If test.Status = "Failed" Then
'increment count
failed = failed + 1
'check if the run was this week - 0 to 7 days
If diff < 8 Then failedThisWeek = failedThisWeek + 1
End If
Next
'output totals for test cycle
Range("C" & row).Value = runCount
Range("D" & row).Value = runThisWeek
Range("E" & row).Value = passed
Range("F" & row).Value = passedThisWeek
Range("G" & row).Value = failed
Range("H" & row).Value = failedThisWeek
'clear objects containing list of tests in cycle
Set testsinlab = Nothing
Set testList = Nothing
'increment totals
totalRunCount = totalRunCount + runCount
totalPassed = totalPassed + passed
totalFailed = totalFailed + failed
totalRunThisWeek = totalRunThisWeek + runThisWeek
totalPassedThisWeek = totalPassedThisWeek + passedThisWeek
totalFailedThisWeek = totalFailedThisWeek + failedThisWeek
'reset values
passed = 0
failed = 0
runCount = 0
runThisWeek = 0
passedThisWeek = 0
failedThisWeek = 0
'increment row number
row = row + 1
Next 'end of processing
'increment row to add blank line
row = row + 1
'output totals
Range("A" & row).Value = "Total"
Range("B" & row).Value = totalTestCount
Range("C" & row).Value = totalRunCount
Range("D" & row).Value = totalRunThisWeek
Range("E" & row).Value = totalPassed
Range("F" & row).Value = totalPassedThisWeek
Range("G" & row).Value = totalFailed
Range("H" & row).Value = totalFailedThisWeek
'autofit columns
Columns("A:K").EntireColumn.AutoFit
'clear objects containing list of test cycles
Set tsf = Nothing
Set tsl = Nothing
'inform user of finish
MsgBox "Finished"
End Sub

Private Sub writeHeadings()
'# write out column headings
Range("A1").Value = "Quality Center Statistics"
Range("B1").Value = "Date: " & Date
Range("A2").Value = "Test Cycle"
Range("B2").Value = "No. of Tests"
Range("C2").Value = "No. Run"
Range("D2").Value = "No. Run this Week"
Range("E2").Value = "Total No. Passed "
Range("F2").Value = "No. Passed this Week"
Range("G2").Value = "Total No. Failed"
Range("H2").Value = "Total No. Failed this Week"
Range("I2").Value = "Current cycle No."
Range("J2").Value = "No of Cycles required for complete run"
Range("K2").Value = "No of builds required for complete run"
End Sub

Note: its vba so needs to go in a macro, this is written for excel. You can either:

Open excel, add the dev bar which gives a link to the vba editor. Paste in there, change the connection string, save then run.

Or...You can open excel, click to create a new macro, stop recording then edit and paste the code in.