Highlighted
Spijkerjaap Contributor.
Contributor.
872 views

Report of all tests in a Test Lab with test step details

Hi all,

I'm looking for a way to create a report of all Test Cases in a Test Lab with all Design Step Details, is there a way to do this? I found an old post but the solution link doesn't work anymore.

Can you please help me out?

Regards,

Spijkerjaap

 

0 Likes
23 Replies
Knowledge Partner
Knowledge Partner

Re: Report of all tests in a Test Lab with test step details

Hi Spijkerjaap,

To which DB server is your ALM application connected? ( Oracle or SQL Server ) ?

Regards, Srihari

0 Likes
Spijkerjaap Contributor.
Contributor.

Re: Report of all tests in a Test Lab with test step details

Hi Srihari,

We are using Oracle.

Regards,

Spijkerjaap

0 Likes
Knowledge Partner
Knowledge Partner

Re: Report of all tests in a Test Lab with test step details

Hi Spijkerjaap,

Could you please explain requirement by highlighting required data with screenshots .

We can extract test script data along with their design steps from Test Plan module itself correct?

I am little confused on why we need to consider test lab in this requirement.

You can build Excel Query with below query and tweak it further:

select
DS_TEST_ID as "Test Case ID",
TS_NAME as "Test Case Name",
DS_STEP_ORDER as "Step Order",
DS_STEP_NAME as "Step Name",
DS_DESCRIPTION as "Step Description",
DS_EXPECTED as "Expected"
from DESSTEPS, TEST
WHERE DESSTEPS.DS_TEST_ID = TEST.TS_TEST_ID
AND TEST.TS_TEST_ID IN
(select A.TS_TEST_ID from (select AL_ITEM_ID, sys_connect_by_path(AL_DESCRIPTION, '\\') PTH from ALL_LISTS connect by prior AL_ITEM_ID = AL_FATHER_ID
start with AL_FATHER_ID = 0 and AL_DESCRIPTION = 'Subject') LPATH, TEST A
where LPATH.AL_ITEM_ID = A.TS_SUBJECT
and PTH like  '\\Subject\\ABC\\XYZ%'             --- Give you parent folder path here
and TS_TEST_ID is not null)

Regards,Srihari

0 Likes
NEW_ALM12 Outstanding Contributor.
Outstanding Contributor.

Re: Report of all tests in a Test Lab with test step details

Hi @Srihari-, Any solution on how to do the same requirement via VBA Excel?

I would like to see following columns with data.

Test_Case_Folder_Name, Test_Set_Name, Test_Case_Name, Test_Case_Type, Test_Case_Status, Steps_Name, Step _Description, Actual_Result, Expected_Resluts, Step_Status, and Responsible_Tester. 

The reason I wold need to use TestLab instead of Test Plan is becase each Test Set may contain Test cases from varius folder folder of Test Plan. 

0 Likes
Knowledge Partner
Knowledge Partner

Re: Report of all tests in a Test Lab with test step details

Hi New_ALM12,

Below blog & code provided, will be very helpful for your requirement:

http://excelreferences.blogspot.com/2014/07/extract-last-run-results-from-hp-almqc.html?m=1

You may need to tweak it little.

Regards, Srihari
0 Likes
NEW_ALM12 Outstanding Contributor.
Outstanding Contributor.

Re: Report of all tests in a Test Lab with test step details

@Srihari- thank you for suggesting me a useful resource. I tried lot to twist the cocde . But, unfortunatly, Im not in a position to make it as I want.

The given code doesnot meet my need where I need to Pull test cases from all Test Sets from the given Set folder. It only pull test cases out of one Test set at a time. Im struggling to make this happen. 

0 Likes
Knowledge Partner
Knowledge Partner

Re: Report of all tests in a Test Lab with test step details

Hi NEW_ALM12,

Below is the sample code I have written to acheive this requirement.

You may need to tweak it further .

Basically, code will perform the following.

1. Navigates to the Test Set Folder provided.

2. Identifies the subfolders present in the Test Set Folder.

3. In each subfolder, it will identify the test sets present.

4. From each test set, it identifies the test instances present

5. from each test instance, it will extract the design step information.

 

Private Sub Form_Load()
Dim tdc As New TDAPIOLELib.TDConnection
tdc.InitConnectionEx "http://XXXX:8080/qcbin"
tdc.ConnectProjectEx "POC", "DEMO_Project_Data", "XXXX", "XXXX"
Dim Test_Set_Folder_Node As TestSetFolder
Dim Test_Set_Folder_Path As String
Dim Test_Instance As TSTest
Dim My_Test As Test
Dim My_Design_Step As DesignStep
Dim My_Design_Step_List As List
Test_Set_Folder_Path = "Root\SIT (04/09-04/20)"
Set Test_Set_Folder_Node = tdc.TestSetTreeManager.NodeByPath(Test_Set_Folder_Path)
Dim Test_Set_Folder_Child_Nodes As IFactoryList
Dim Test_Set_Test_Instance_List As IFactoryList
Set Test_Set_Folder_Child_Nodes = Test_Set_Folder_Node.FindChildren("")
Dim m As Integer
m = 1
If Test_Set_Folder_Child_Nodes.Count > 1 Then
        For i = 1 To Test_Set_Folder_Child_Nodes.Count
            For j = 1 To Test_Set_Folder_Child_Nodes.Item(i).FindTestSets("").Count
               If Test_Set_Folder_Child_Nodes.Item(i).FindTestSets("").Item(j).TSTestFactory.NewList("").Count > 0 Then
                  Set Test_Set_Test_Instance_List = Test_Set_Folder_Child_Nodes.Item(i).FindTestSets("").Item(j).TSTestFactory.NewList("")
                      For k = 1 To Test_Set_Test_Instance_List.Count
                        Set Test_Instance = Test_Set_Test_Instance_List.Item(k)
                        Set My_Test = Test_Instance.Test
                         If My_Test.DesignStepFactory.NewList("").Count > 0 Then
                            For l = 1 To My_Test.DesignStepFactory.NewList("").Count
                             Set My_Design_Step_List = My_Test.DesignStepFactory.NewList("")
                                For Each My_Design_Step In My_Design_Step_List
                                    ThisWorkbook.Sheets("Sheet1").Range("A" & m).Value = Test_Set_Folder_Child_Nodes.Item(i).Path
                                    ThisWorkbook.Sheets("Sheet1").Range("B" & m).Value = Test_Set_Folder_Child_Nodes.Item(i).Name
                                    ThisWorkbook.Sheets("Sheet1").Range("C" & m).Value = Test_Set_Folder_Child_Nodes.Item(i).FindTestSets("").Item(j).Name
                                    ThisWorkbook.Sheets("Sheet1").Range("D" & m).Value = Test_Instance.Name
                                    ThisWorkbook.Sheets("Sheet1").Range("E" & m).Value = Test_Instance.TestId
                                    ThisWorkbook.Sheets("Sheet1").Range("F" & m).Value = Test_Instance.TestName
                                    ThisWorkbook.Sheets("Sheet1").Range("G" & m).Value = My_Design_Step.StepName
                                    ThisWorkbook.Sheets("Sheet1").Range("H" & m).Value = My_Design_Step.StepDescription
                                    ThisWorkbook.Sheets("Sheet1").Range("I" & m).Value = My_Design_Step.StepExpectedResult
                                    m = m + 1
                                Next
                           Next
                         End If
                    Next
                End If
            Next
        Next
End If

'                Debug.Print TypeName(Test_Set_Folder_Child_Nodes)  ' IReq3
'                Debug.Print IsObject(ReqFact.Item(My_TestSet_TreeNode))  ' True
'                Debug.Print VarType(ReqFact.Item(My_TestSet_TreeNode))   ' 9
'                Debug.Print IsEmpty(ReqFact.Item(My_TestSet_TreeNode))   ' False
'                Debug.Print IsArray(ReqFact.Item(My_TestSet_TreeNode))   ' False
'                Debug.Print IsDate(ReqFact.Item(My_TestSet_TreeNode))    ' False
'                Debug.Print IsNull(ReqFact.Item(My_TestSet_TreeNode))    ' False
'                Debug.Print IsEmpty(ReqFact.Item(My_TestSet_TreeNode))   ' False
'                Debug.Print IsNumeric(ReqFact.Item(My_TestSet_TreeNode)) ' False
Set Test_Set_Folder_Node = Nothing
Set Test_Set_Folder_Child_Nodes = Nothing
Set Test_Set_Test_Instance_List = Nothing
Set Test_Instance = Nothing
Set My_Test = Nothing
Set My_Design_Step_List = Nothing
Set My_Design_Step = Nothing
tdc.DisconnectProject
tdc.ReleaseConnection
Set tdc = Nothing
End Sub

Regards,Srihari

[If this post solves or helps solve your issue, mark the thread as solved and give KUDOS to the author for their assistance.]

(Opinions expressed in my postings are mine alone, and do not reflect the opinions of my employer.No warranties express or implied for any solution/suggestion posted.)

0 Likes
Spijkerjaap Contributor.
Contributor.

Re: Report of all tests in a Test Lab with test step details

Hi Srihari,

I'm sorry for the delay.

I want to have an overview of all test cases including test step details for a particular release. In the test plan we manage our scripts by functionality and in the test lab we gather all scripts necessary for a release. That's why I want this report to run from the Lab, to have a proper selection of scripts.

Is there a way to do this?

Regards,

Spijkerjaap

0 Likes
NEW_ALM12 Outstanding Contributor.
Outstanding Contributor.

Re: Report of all tests in a Test Lab with test step details

@Srihari-, I really appreciate your input and time. 

I tested the above code snipit and I am getting error in a line highlighted in yellow. 

Error.PNG

Code_Error.PNG

Sample of my test folder/test sets in Test lab is as below structre. 

TS_Structure.PNG

 

 

I am only using test folder path as "Root\Dashboard Testing\QA".

Also, if any test cases/s do not have Step/s, I should be able to pull those test case/s from TestSet, though that test case do not have Run history or  execution status or execution time.

 

0 Likes
Knowledge Partner
Knowledge Partner

Re: Report of all tests in a Test Lab with test step details

Hi New_ALM12,

Above provided code will work if we provide path upto "Dashboard Testing" folder, something like "Root\Dashboard Testing".

If your requirement is something like extracting the direct test sets and their associated test instance along with design step details from a specific test set folder then below sample code will be helpful.

This sample code is intended just to provide a strating point , you may need to further enhance it based on findings.

Private Sub Form_Load()
Dim tdc As New TDAPIOLELib.TDConnection
tdc.InitConnectionEx "http://XXXXXXX:8080/qcbin"
tdc.ConnectProjectEx "POC", "DEMO_Project_Data", "XXXXX", "XXXXX"
Dim Test_Set_Folder_Node As TestSetFolder
Dim Test_Set_Folder_Path As String
Dim Test_Instance As TSTest
Dim My_Test As Test
Dim My_Design_Step As DesignStep
Dim My_Design_Step_List As List
Dim Test_Set_Object As TestSet
Dim Test_Set_Folder_Test_Sets As List
Test_Set_Folder_Path = "Root\SIT (04/09-04/20)"
Set Test_Set_Folder_Node = tdc.TestSetTreeManager.NodeByPath(Test_Set_Folder_Path)
Dim Test_Set_Folder_Child_Nodes As IFactoryList
Dim Test_Set_Test_Instance_List As IFactoryList
'Set Test_Set_Folder_Child_Nodes = Test_Set_Folder_Node.FindChildren("")
Set Test_Set_Folder_Test_Sets = Test_Set_Folder_Node.FindTestSets("")
Dim m As Integer
m = 1

If Test_Set_Folder_Test_Sets.Count > 0 Then
    For Each Test_Set_Object In Test_Set_Folder_Test_Sets
        Set Test_Set_Test_Instance_List = Test_Set_Object.TSTestFactory.NewList("")
                          For k = 1 To Test_Set_Test_Instance_List.Count
                            Set Test_Instance = Test_Set_Test_Instance_List.Item(k)
                            Set My_Test = Test_Instance.Test
                             If My_Test.DesignStepFactory.NewList("").Count > 0 Then
                                For l = 1 To My_Test.DesignStepFactory.NewList("").Count
                                 Set My_Design_Step_List = My_Test.DesignStepFactory.NewList("")
                                    For Each My_Design_Step In My_Design_Step_List
                                        ThisWorkbook.Sheets("Sheet1").Range("A" & m).Value = Test_Set_Folder_Node.Path
                                        ThisWorkbook.Sheets("Sheet1").Range("B" & m).Value = Test_Set_Folder_Node.Name
                                        ThisWorkbook.Sheets("Sheet1").Range("C" & m).Value = Test_Set_Object.Name
                                        ThisWorkbook.Sheets("Sheet1").Range("D" & m).Value = Test_Instance.Name
                                        ThisWorkbook.Sheets("Sheet1").Range("E" & m).Value = Test_Instance.TestId
                                        ThisWorkbook.Sheets("Sheet1").Range("F" & m).Value = Test_Instance.TestName
                                        ThisWorkbook.Sheets("Sheet1").Range("G" & m).Value = My_Design_Step.StepName
                                        ThisWorkbook.Sheets("Sheet1").Range("H" & m).Value = My_Design_Step.StepDescription
                                        ThisWorkbook.Sheets("Sheet1").Range("I" & m).Value = My_Design_Step.StepExpectedResult
                                        m = m + 1
                                    Next
                               Next
                             ElseIf My_Test.DesignStepFactory.NewList("").Count = 0 Then
                                        ThisWorkbook.Sheets("Sheet1").Range("A" & m).Value = Test_Set_Folder_Node.Path
                                        ThisWorkbook.Sheets("Sheet1").Range("B" & m).Value = Test_Set_Folder_Node.Name
                                        ThisWorkbook.Sheets("Sheet1").Range("C" & m).Value = Test_Set_Object.Name
                                        ThisWorkbook.Sheets("Sheet1").Range("D" & m).Value = Test_Instance.Name
                                        ThisWorkbook.Sheets("Sheet1").Range("E" & m).Value = Test_Instance.TestId
                                        ThisWorkbook.Sheets("Sheet1").Range("F" & m).Value = Test_Instance.TestName
                                        ThisWorkbook.Sheets("Sheet1").Range("G" & m).Value = ""
                                        ThisWorkbook.Sheets("Sheet1").Range("H" & m).Value = ""
                                        ThisWorkbook.Sheets("Sheet1").Range("I" & m).Value = ""
                                        m = m + 1
                             End If
                        Next
    Next
End If



'If Test_Set_Folder_Child_Nodes.Count > 0 Then
'        For i = 1 To Test_Set_Folder_Child_Nodes.Count
'            For j = 1 To Test_Set_Folder_Child_Nodes.Item(i).FindTestSets("").Count
'               If Test_Set_Folder_Child_Nodes.Item(i).FindTestSets("").Item(j).TSTestFactory.NewList("").Count > 0 Then
'                  Set Test_Set_Test_Instance_List = Test_Set_Folder_Child_Nodes.Item(i).FindTestSets("").Item(j).TSTestFactory.NewList("")
'                      For k = 1 To Test_Set_Test_Instance_List.Count
'                        Set Test_Instance = Test_Set_Test_Instance_List.Item(k)
'                        Set My_Test = Test_Instance.Test
'                         If My_Test.DesignStepFactory.NewList("").Count > 0 Then
'                            For l = 1 To My_Test.DesignStepFactory.NewList("").Count
'                             Set My_Design_Step_List = My_Test.DesignStepFactory.NewList("")
'                                For Each My_Design_Step In My_Design_Step_List
'                                    ThisWorkbook.Sheets("Sheet1").Range("A" & m).Value = Test_Set_Folder_Child_Nodes.Item(i).Path
'                                    ThisWorkbook.Sheets("Sheet1").Range("B" & m).Value = Test_Set_Folder_Child_Nodes.Item(i).Name
'                                    ThisWorkbook.Sheets("Sheet1").Range("C" & m).Value = Test_Set_Folder_Child_Nodes.Item(i).FindTestSets("").Item(j).Name
'                                    ThisWorkbook.Sheets("Sheet1").Range("D" & m).Value = Test_Instance.Name
'                                    ThisWorkbook.Sheets("Sheet1").Range("E" & m).Value = Test_Instance.TestId
'                                    ThisWorkbook.Sheets("Sheet1").Range("F" & m).Value = Test_Instance.TestName
'                                    ThisWorkbook.Sheets("Sheet1").Range("G" & m).Value = My_Design_Step.StepName
'                                    ThisWorkbook.Sheets("Sheet1").Range("H" & m).Value = My_Design_Step.StepDescription
'                                    ThisWorkbook.Sheets("Sheet1").Range("I" & m).Value = My_Design_Step.StepExpectedResult
'                                    m = m + 1
'                                Next
'                           Next
'                         End If
'                    Next
'                End If
'            Next
'        Next
'End If

'                Debug.Print TypeName(Test_Set_Folder_Child_Nodes)  ' IReq3
'                Debug.Print IsObject(ReqFact.Item(My_TestSet_TreeNode))  ' True
'                Debug.Print VarType(ReqFact.Item(My_TestSet_TreeNode))   ' 9
'                Debug.Print IsEmpty(ReqFact.Item(My_TestSet_TreeNode))   ' False
'                Debug.Print IsArray(ReqFact.Item(My_TestSet_TreeNode))   ' False
'                Debug.Print IsDate(ReqFact.Item(My_TestSet_TreeNode))    ' False
'                Debug.Print IsNull(ReqFact.Item(My_TestSet_TreeNode))    ' False
'                Debug.Print IsEmpty(ReqFact.Item(My_TestSet_TreeNode))   ' False
'                Debug.Print IsNumeric(ReqFact.Item(My_TestSet_TreeNode)) ' False

Set Test_Set_Folder_Test_Sets = Nothing
Set Test_Set_Object = Nothing
Set Test_Set_Folder_Node = Nothing
Set Test_Set_Folder_Child_Nodes = Nothing
Set Test_Set_Test_Instance_List = Nothing
Set Test_Instance = Nothing
Set My_Test = Nothing
Set My_Design_Step_List = Nothing
Set My_Design_Step = Nothing
tdc.DisconnectProject
tdc.ReleaseConnection
Set tdc = Nothing
End Sub

Regards,Srihari

0 Likes
Knowledge Partner
Knowledge Partner

Re: Report of all tests in a Test Lab with test step details

Hi Spijkerjaap,

Please refer to the functional group diagram provided in below link:

https://admhelp.microfocus.com/alm/en/12.55/api_refs/project_db/webframe.html#topic20.html

I donot have Oracle database as backend for my ALM , so unfortunately could not develope the query.

Query should be written covering below steps:

1.  If you look at earlier query provided, using "SYS_CONNECT_BY_PATH", "CONNECT BY PRIOR TO" and "START WITH" we iterated through ALL_LISTS ( test plan folders ) , like wise this time we have iterate through "CYCL_FOLD" table to get path of all the test set folders.

2. Further Join "CYCLE" table which will hold the test set information

3. Further Join the "TESTCYCL" table which will hold information of the test instances present in each test set.

4. Then join the "TEST" table to get the test details.

5.  refer to below link:

   https://admhelp.microfocus.com/alm/en/12.55/api_refs/project_db/webframe.html#topic21.html

6. Join the DESSTEPS table to get the "design steps"

7. Join above obtained data with "RELEASES", "CYCLE", "RELEASE_CYCLES" tables

 In order to filter out data related to a particular release,  you can filter data on REL_ID from RELEASES table.

Regards,Srihari

0 Likes
NEW_ALM12 Outstanding Contributor.
Outstanding Contributor.

Re: Report of all tests in a Test Lab with test step details

@Srihari-, I tested your original code with only one test set. So it was throwing an error. Now I tested the same code as you suggested. This is working fine except duplicate records. Please see screen print below. 

Duplicate_row.PNG

BTW, both of code works great except duplicate. I also made some minor changes that suites my requirement. 

How do I get rid off the duplicate rows?

Thanks

 

0 Likes
Knowledge Partner
Knowledge Partner

Re: Report of all tests in a Test Lab with test step details

Hi New_ALM12,
Please go through solution provided in below link:
https://stackoverflow.com/questions/16988816/delete-all-duplicate-rows-excel-vba

Add similar code to our VBA script and get rid of duplicate rows.

Regards,
Srihari
0 Likes
NEW_ALM12 Outstanding Contributor.
Outstanding Contributor.

Re: Report of all tests in a Test Lab with test step details

@Srihari- i perfer not to use removeduplicaterow sub because remove this sub wont be applicable for the result that is pulled from above original code. 

This is what I have as a removeduplicaterow. I worked on it but did not satisfies for me so I added a comment eailier about duplicate. 

Sub RemoveDuplicateRows()

Dim MyRange As Range

Dim LastRow As Long

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Set MyRange = ActiveSheet.Range("A1:I" & LastRow)

MyRange.RemoveDuplicates Columns:=8, Header:=xlYes

End Sub

 To me, the original code sould populate all rows value only once but not sure why its populating mre than twice. It may be cause it has variuos conditions i.e. i, j,k,l

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.