Export Test scripts from QC to Excel in Bulk

[Rati Saxena]

The script given to export the testcases and test scenario from QC to Excel doesn't work. Can anyone provide the updated script and let me know how to Export the data from QC to Excel????

[Mary E Evans]

-- Tree Level
-- MRI/SIT/R5-Sept 09/BPM/MRI R5%
--Test Lab

SELECT
CF_ITEM_NAME as "Test Set Folder Name",
CY_CYCLE as "Test Set Name",
TS_NAME as "Test Case Name",
RN_STATUS as "Test Case Status",
ST_STEP_NAME as "Test Step Name",
ST_STATUS as "Test Step Status",
ST_DESCRIPTION as "Test Step Description",
ST_EXPECTED as "Test Step Expected Result",
ST_ACTUAL as "Test Step Actual Result",
RN_TESTER_NAME as "Tester Name",
ST_EXECUTION_DATE as "Test Step Execution Date",
ST_EXECUTION_TIME as "Test Step Execution Time"
FROM STEP a, TEST b, CYCLE c,RUN d,CYCL_FOLD e
where
a.ST_TEST_ID=b.TS_TEST_ID and
c.CY_CYCLE_ID=d.RN_CYCLE_ID and
d.RN_TEST_ID=b.TS_TEST_ID and
e.CF_ITEM_ID=c.CY_FOLDER_ID and
RN_TESTER_NAME IS NOT NULL and
CF_ITEM_NAME ='@TestFolder@'
and CY_CYCLE like '%@TestSet@%'
ORDER BY TS_NAME,RN_RUN_ID,ST_RUN_ID,ST_EXECUTION_DATE,ST_EXECUTION_TIME ASC

--Tree Level example for Test Plan
--Parameter is at 5th level of tree
--MRI/BPM/R5-Sept 09 Release/MRI R5 hVar/
--%TestFolder%

SELECT
ALL_LISTS.AL_DESCRIPTION,
TEST.TS_NAME,
TEST.TS_CREATION_DATE,
DESSTEPS.DS_STEP_NAME,
DESSTEPS.DS_DESCRIPTION,
DESSTEPS.DS_EXPECTED
FROM ALL_LISTS
INNER JOIN
TEST ON ALL_LISTS.AL_ITEM_ID = TEST.TS_SUBJECT INNER JOIN
DESSTEPS ON TEST.TS_TEST_ID = DESSTEPS.DS_TEST_ID
WHERE ALL_LISTS.AL_DESCRIPTION like '%@LikeTestFolder@%'
ORDER BY ALL_LISTS.AL_DESCRIPTION,TEST.TS_NAME, DESSTEPS.DS_STEP_ORDER

[Mahavir_1]

Hi Percy,

I came up with quite an interesting observation while downloading the test cases. If the design step has a Call to test Template then using the code we are getting designStep description as "CALL" where as if we try to manually export the test case, we get this as "CALL with the following parameters: ...."

Do you know how we can also get the test template name details along with the CALL using the code.

Regards,
Mahavir

[Percy Bell]

Mahavir,

Sorry but I haven't investigated called tests while exporting design steps.

[Mahavir_1]

Hi Percy,

Any pointers to how the called test case name can be captured?

Regards,
Mahavir

[Percy Bell]

Mahavir,

You may be able to use the LinkTest or LinkTestID to return the object you need.

In the For Each DesignStep loop try adding MsgBox DesignStep.LinkTestID. That should return the Test ID of the linked Test. You should be able create a new loop to get all the steps for the linked test. I think the LinkTest method will return a new DesignStep Object for the linked template test.

[Mahavir_1]

Hey Percy,

I finally got the solution on the same lines as you thought of.

I am also putting in my code snippet :

If DesignStep.LinkTestID <> 0 Then
'MsgBox ("LinkedTestID = " & DesignStep.LinkTestID)
Dim TreeMgr1, TestTree1, TestFactory1, TestList1
'Set TreeMgr1 = QCConnection.TreeManager
'Set TestTree1 = TreeMgr1.NodeByPath("Subject")
'Set TestFactory1 = TestTree1.TestFactory

Set TestFactory1 = QCConnection.TestFactory

Dim Filter1 'As TDFilter
Dim Test1 'As Test
Set Filter1 = TestFactory1.Filter
Filter1.Filter("TS_Test_ID") = DesignStep.LinkTestID
Set TestList1 = TestFactory1.NewList(Filter1.Text)
Set Test1 = TestList1.Item(1)
'MsgBox ("Test Template Name = " & Test1.Field("TS_NAME"))
objSheet.Cells(Row, 7).Value = "Call < " & Test1.Field("TS_NAME") & " >" ' Format: Call < Tempalte Name >
Else
objSheet.Cells(Row, 7).Value = stripHTML(DesignStep.StepDescription)
End If

Regards,
Mahavir

[Maheshwar_1]

Hi All,

Just double click the vbs file and u will be prompted with all the necessary pop-ups and finally the results will get stored in the "C" drive by showing the completed msgbox.

Thanks
Maheshwar

[Maheshwar_1]

save the Attached file as .vbs and double click and follow my instructions as above.


Thanks
Maheshwar

[Maheshwar_1]

Please find the correct attachment. srry for the confusion


Regards
Maheshwar

[PattCo]

I'm jumping in late, but I use the Excel Report Generator to do this and it's very simple and works all the time

Here's my query:
select a.*,ds_step_name,ds_description
from (
SELECT father.al_description,
ts_subject,
ts_name,
ts_steps,
ts_responsible,
ts_test_id
FROM test,
all_lists child,
all_lists father

WHERE ts_subject = child.al_item_id
AND child.al_father_id = father.al_item_id ) a

left outer join

dessteps
on a.ts_test_id = ds_test_id

order by ts_test_id,ds_step_order

Good luck -
Kathy

[Jason Gorman]

This script works great. Does anyone know the line I could add to pull out the requirement coverage for each test case as well. There apears to be a "GetCoverList Method" as part of the Test object but I'm not sure how to implement this.

[Jason Gorman]

Here are the additions required to pull out the requirements mapped to a Test Case:
'Iterate through all the tests.
For Each TestCase In TestList
Dim DesignStepFactory, DesignStep, DesignStepList, ReqCov, ReqCovList
Set DesignStepFactory = TestCase.DesignStepFactory
Set DesignStepList = DesignStepFactory.NewList("")
Set ReqCovList = TestCase.GetCoverList()

AND:
For Each DesignStep In DesignStepList
'Save a specified set of fields.
Sheet.Cells(Row, 4).Value = TestCase.Field("TS_RESPONSIBLE")
Sheet.Cells(Row, 5).Value = TestCase.Field("TS_STATUS")

'Save the specified design steps.
Sheet.Cells(Row, 6).Value = stripHTML(DesignStep.StepName)
Sheet.Cells(Row, 7).Value = stripHTML(DesignStep.StepDescription)
Sheet.Cells(Row, 8).Value = stripHTML(DesignStep.StepExpectedResult)
Row = Row + 1
Next
Row = Row - DesignStepList.Count
For Each ReqCov In ReqCovList
Sheet.Cells(Row, 9).Value = ReqCov.Name
Row = Row + 1
Next
End If

AND:
'Set the Column width for the following columns.
Excel.Columns("C").ColumnWidth = 80 'Description
Excel.Columns("G").ColumnWidth = 80 'Step Description(Action)
Excel.Columns("H").ColumnWidth = 80 'Expected Result
Excel.Columns("I").ColumnWidth = 80 'Requirement