There are various methods to do that, below are two methods:
1. VB Script to download test cases with test steps from QC/ALM.
Function EmportTestCases()
On Error Resume Next
Dim QCConnection
Dim sUserName, sPassword
Dim sDomain, sProject
Dim TstFactory, TestList
Dim TestCase
'Create QC Connection Object to connect to QC
Set QCConnection = CreateObject("TDApiOle80.TDConnection")
sUserName = "your user id"
sPassword = "your password"
QCConnection.InitConnectionEx "your QC URL/qcbin"
'Authenticate your user ID and Password
QCConnection.Login sUserName, sPassword
'Quit if QC Authentication fails
If (QCConnection.LoggedIn <> True) Then
MsgBox "QC User Authentication Failed"
End
End If
sDomain = "your domain name"
sProject = "your project name"
'Login to your Domain and Project
QCConnection.Connect sDomain, sProject
'Quit if login fails to specified Domain and Project
If (QCConnection.AuthenticationToken = "") Then
MsgBox "QC Project Failed to Connect to " & sProject
QCConnection.Disconnect
End
End If
'Now successful connection is made to QC
'Get the test factory
Set TstFactory = QCConnection.TestFactory
' Your QC Project Path for which you want to download
' the test cases.
fpath = "your project folder"
Set myfilter = TstFactory.Filter()
myfilter.Filter("TS_SUBJECT") = "^" & fpath & "^"
'Get a list of all test cases for your specified path
Set TestList = myfilter.NewList()
'Format the header before downloading the test cases
With ActiveSheet
.Range("B5").Select
With .Range("B4:H4")
.Font.Name = "Arial"
.Font.FontStyle = "Bold"
.Font.Size = 10
.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 15
End With
.Cells(4, 2) = "Subject (Folder Name)"
.Cells(4, 3) = "Test Name (Manual Test Plan Name)"
.Cells(4, 4) = "Description"
.Cells(4, 5) = "Status"
.Cells(4, 6) = "Step Name"
.Cells(4, 7) = "Step Description(Action)"
.Cells(4, 8) = "Expected Result"
Dim Row
Row = 5 '- set the data row from 5
'loop through all the test cases.
For Each TestCase In TestList
.Cells(Row, 2).Value = TestCase.Field("TS_SUBJECT").Path
.Cells(Row, 3).Value = TestCase.Field("TS_NAME")
'QC stores description in html format. So before storing it
'in to excel, StripHTML() will remove all HTML tags and put
'texts only. Also new line tag <br> is replaced with new line
'character chr(10) in excel so that all the new line texts appears properly
.Cells(Row, 4).Value = StripHTML(Replace(TestCase.Field("TS_DESCRIPTION"), _
"<br>", Chr(10)))
.Cells(Row, 5).Value = TestCase.Field("TS_EXEC_STATUS")
'Get the DesignStepFactory for the this testcase
Dim DesignStepFactory, DesignStep, DesignStepList
Set DesignStepFactory = TestCase.DesignStepFactory
Set DesignStepList = DesignStepFactory.NewList("")
'Check if design steps exists for the test
If DesignStepList.Count <> 0 Then
'loop for all the steps for this test case
For Each DesignStep In DesignStepList
.Cells(Row, 6).Value = DesignStep.StepName
.Cells(Row, 7).Value = StripHTML(Replace(DesignStep.StepDescription, _
"<br>", Chr(10)))
.Cells(Row, 8).Value = StripHTML(Replace(DesignStep.StepExpectedResult, _
"<br>", Chr(10)))
Row = Row + 1
Next 'next Step
End If
' release the design step objects
Set DesignStepFactory = Nothing
Set DesignStep = Nothing
Set DesignStepList = Nothing
Next ' Next test case
End With
'Release the object
Set DesignStepFactory = Nothing
Set DesignStep = Nothing
Set DesignStepList = Nothing
Set TstFactory = Nothing
Set TestList = Nothing
Set TestCase = Nothing
QCConnection.Disconnect
MsgBox ("All Test cases are downloaded with Test Steps")
End Function
Function StripHTML(sInput As String) As String
Dim RegEx As Object
Set RegEx = CreateObject("vbscript.regexp")
Dim sInput As String
Dim sOut As String
sInput = cell.Text
With RegEx
.Global = True
.IgnoreCase = True
.MultiLine = True
.Pattern = "<[^>]+>" 'Regular Expression for HTML Tags.
End With
sOut = RegEx.Replace(sInput, "")
StripHTML = sOut
Set RegEx = Nothing
End Function
2. SQL query to download test cases with test steps from QC/ALM.
SELECT T.TS_NAME, T.TS_TEST_ID, T.TS_DESCRIPTION, T.TS_STEPS, S.DS_STEP_NAME, S.DS_DESCRIPTION, S.DS_EXPECTED
FROM TEST T, DESSTEPS S
WHERE T.TS_TEST_ID = S.DS_TEST_ID;
test status
SQL 1:
SELECT
CF_ITEM_NAME as "Test Lab Folder Name",
CF_ITEM_PATH as "Folder path",
CF_ITEM_ID as "Folder ID"
FROM CYCL_FOLD
order by CF_ITEM_PATH ;
SQL 2:
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_HOST as "Test Host Name",
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_HOST IS NOT NULL and
RN_TESTER_NAME IS NOT NULL and
CF_ITEM_PATH like 'AAAAAAAAA%'
ORDER BY TS_NAME,RN_RUN_ID,ST_RUN_ID,ST_EXECUTION_DATE,ST_EXECUTION_TIME ASC;
