There is no direct option to download all the test cases from ALM under specific folder from Test Lab. Though we can download single test case at a time from export option available in TestLab, it is time consuming if we want to download 100's of test cases in a short span of time.
Writing SQL query in the ALM under "Dashboard->Analysis View" with Excel report would solve our problem and can download all test cases in seconds/minutes
Following SQL queries needs to be added in excel report, and upon executing these queries would generate excel file with relevant information we are looking for.
It is basically 2 way approach as there is no option to provide the folder path in the SQL query
1. Find out the absolute path of required folder path in TestLab
''Finding actual folder path from the absolute path
select in_cf.AL_ITEM_ID, sys_connect_by_path( in_cf.AL_DESCRIPTION, '\') PTH, in_cf.AL_ABSOLUTE_PATH as "ABS Path"
from ALL_LISTS in_cf connect by prior in_cf.AL_ITEM_ID = in_cf.AL_FATHER_ID
start with in_cf.AL_FATHER_ID = 0
''Finding absolute path from Test Lab
SELECT AL_DESCRIPTION, /* Level3 Folder Name */ AL_ABSOLUTE_PATH
FROM All_Lists
2. Pick up the absolute path from the above query and pass that in the following query and execute the report to generate excel file with the test cases.
You can pass required fields though in the query, in below I have only provided basic fields which are required in test case.
'''Downloading testcases from Test Lab with absolute path
SELECT
ALL_LISTS.AL_DESCRIPTION as "Folder Name" /*Test Plan Folder.Name*/,
TEST.TS_TEST_ID as "Test ID" /*Test.Test ID*/,
TEST.TS_NAME as "Test Name" /*Test.Test Name*/,
TEST.TS_STATUS as "Status" /*Test.Status*/,
TEST.TS_TYPE as "Test Case Type" /*Test.Status*/,
TEST.TS_RESPONSIBLE as "Tester" /*Test.Designer*/,
TEST.TS_DESCRIPTION as "Test Description"/*Test.Description*/,
DESSTEPS.DS_STEP_NAME as "Test Step Name" /*Test Step.Step Name*/,
DESSTEPS.DS_DESCRIPTION as "Step Description"/*Test Step.Description*/,
DESSTEPS.DS_EXPECTED as "Expected Results" /*Test Step.Expected*/
From TEST
Join ALL_LISTS On TEST.TS_SUBJECT = ALL_LISTS.AL_ITEM_ID
Join DESSTEPS On TEST.TS_TEST_ID=DESSTEPS.DS_TEST_ID
Where All_Lists.AL_ABSOLUTE_PATH like'AAAAAPAAMAAIAAHAAD%'
ORDER BY TEST.TS_TEST_ID, DESSTEPS.DS_STEP_ORDER
Writing SQL query in the ALM under "Dashboard->Analysis View" with Excel report would solve our problem and can download all test cases in seconds/minutes
Following SQL queries needs to be added in excel report, and upon executing these queries would generate excel file with relevant information we are looking for.
It is basically 2 way approach as there is no option to provide the folder path in the SQL query
1. Find out the absolute path of required folder path in TestLab
''Finding actual folder path from the absolute path
select in_cf.AL_ITEM_ID, sys_connect_by_path( in_cf.AL_DESCRIPTION, '\') PTH, in_cf.AL_ABSOLUTE_PATH as "ABS Path"
from ALL_LISTS in_cf connect by prior in_cf.AL_ITEM_ID = in_cf.AL_FATHER_ID
start with in_cf.AL_FATHER_ID = 0
''Finding absolute path from Test Lab
SELECT AL_DESCRIPTION, /* Level3 Folder Name */ AL_ABSOLUTE_PATH
FROM All_Lists
2. Pick up the absolute path from the above query and pass that in the following query and execute the report to generate excel file with the test cases.
You can pass required fields though in the query, in below I have only provided basic fields which are required in test case.
'''Downloading testcases from Test Lab with absolute path
SELECT
ALL_LISTS.AL_DESCRIPTION as "Folder Name" /*Test Plan Folder.Name*/,
TEST.TS_TEST_ID as "Test ID" /*Test.Test ID*/,
TEST.TS_NAME as "Test Name" /*Test.Test Name*/,
TEST.TS_STATUS as "Status" /*Test.Status*/,
TEST.TS_TYPE as "Test Case Type" /*Test.Status*/,
TEST.TS_RESPONSIBLE as "Tester" /*Test.Designer*/,
TEST.TS_DESCRIPTION as "Test Description"/*Test.Description*/,
DESSTEPS.DS_STEP_NAME as "Test Step Name" /*Test Step.Step Name*/,
DESSTEPS.DS_DESCRIPTION as "Step Description"/*Test Step.Description*/,
DESSTEPS.DS_EXPECTED as "Expected Results" /*Test Step.Expected*/
From TEST
Join ALL_LISTS On TEST.TS_SUBJECT = ALL_LISTS.AL_ITEM_ID
Join DESSTEPS On TEST.TS_TEST_ID=DESSTEPS.DS_TEST_ID
Where All_Lists.AL_ABSOLUTE_PATH like'AAAAAPAAMAAIAAHAAD%'
ORDER BY TEST.TS_TEST_ID, DESSTEPS.DS_STEP_ORDER
Would love to perpetually get updated great blog ! . Venito
ReplyDeletebless you with regard to the particular blog post ive really been looking with regard to this kind of advice on the net for sum time these days hence with thanks Leather Watch Bands
ReplyDelete