Tuesday, September 13, 2011

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.

17 comments:

  1. Hi Vashi,

    grat code. I have copird the same code and edited with my credentials for the below code
    QCconnect_silent "URL", "DOMAIN", "PROJECT", "USERNAME", "PASSWORD"

    while running th macro, i am getting object "User - defined type not defined for the lines
    Dim tdc As TDConnection
    Dim tsf As TestSetFactory
    Dim tf As TestFactory
    Dim filterSet As TDFilter
    Dim Tset As TestSet
    Dim test As TSTest.

    Can you please help/provide me the code on this.

    Thanks
    Surendhar
    surendhar.ramakrishnan@wipro.com

    ReplyDelete
  2. plz comment the above declaration part
    'Dim tdc As TDConnection

    ReplyDelete
  3. have you include the OTA Com type Libary in the project reference ?

    ReplyDelete
  4. TD Path doesnt seem to be used to locate the test lap path...Can you pls. confirm?

    ReplyDelete
    Replies
    1. Hi Vashi,

      I would like to know the significances of
      TestSetFactory
      TestFactory
      TDFilter
      TestSet
      TSTest

      Moreover My requirement is to get the script status details for some selected test lab folders,i think i can modify the code to match my requirement once i am given some overview on the above listed terms.

      Thanks in Advance.

      Delete
    2. hi,
      Getting same error ->
      "User - defined type not defined for the lines
      Dim tdc As TDConnection
      Dim tsf As TestSetFactory
      Dim tf As TestFactory
      Dim filterSet As TDFilter
      Dim Tset As TestSet
      Dim test As TSTest.

      Please help !
      pritsj@gmail.com

      Delete
  5. Hi,

    How to update the execution status of the individual steps in a test case as Pass/Fail in QC.As of now I could get the status of whole test case as Pass/Fail automataically when I Execute from Test Lab.But could not get for the steps in the test case.Can anyone please provide me the exact piece of code to write in QC using VB Script

    ReplyDelete
  6. I need to create a status report for all test cases in particular folder. Folder path is like Root\Dry Run\System Integration, inside system integration there are multiple folders and each folder contains multiple test sets.
    Can you please help how can i generate the report.
    Structure is like:
    Root\Dry Run\System Integration\Test1 - Test1 contains multiple folders name: Test11, Test12, Test13 and each folder contains multiple test sets

    ReplyDelete
  7. Hi,

    Its really a great code..can you update it with the users option to enter the particular testlab path

    ReplyDelete
  8. Aw, this was a very nice post. Taking the time and
    actual effort to produce a really good article… but what can I say… I hesitate
    a lot and never manage to get anything done.

    Here is my web page: battlefield 4 campaign cheats xbox one (achieveyourdreams.co.vu)

    ReplyDelete
  9. Nice Article, Thanks for sharing.

    ReplyDelete
  10. Hi,
    Thank you so much for posting this. I really appreciate your work. Keep it up. Great work!
    http://kosmiktechnologies.com/selenium/

    ReplyDelete
  11. Hi,
    Thanks for sharing the very informative article.
    http://kosmiktechnologies.com/selenium/

    ReplyDelete
  12. I am expecting more interesting topics from you. And this was nice content and definitely it will be useful for many people.
    Excellent ! I am truly impressed that there is so much about this subject that has been revealed and you did it so nicely.

    home tuition
    home tuition
    home tuitions
    home tuitions in hyderabad

    ReplyDelete
  13. Really Thanks For Posting Such an Useful and informative article I gathered some needful information from your blog United States Medical Licensing Examination

    ReplyDelete
  14. What a tremendous job. You have written the exact facts without distorting them. Keep sharing such works. In case, you are facing any problem in using QuickBooks accounting software, call on the QuickBooks Technical Support Phone Number +1 (888) 238-7409. The executives available will thoroughly guide you and provide you exact solutions. Read more: - https://qbtechnicalsupportphone.com/ & Visit us: - https://tinyurl.com/yy2cp4dx

    ReplyDelete