New Ranks & Badges For The Community!
Notice something different? The ranks and associated badges have gone "Star Fleet". See what they all mean HERE
Highlighted
Absent Member.. Absent Member..
Absent Member..
13606 views

how to generate testcases into excel from test lab

Hi, i am using QC 10.0. I have few test cases in the test lab, now i want to convert all the test cases into excel.

Could any one provide me the steps for it?

0 Likes
12 Replies
Highlighted
Absent Member.
Absent Member.

You can export the test cases to excel using export option from test plan. This will be done from design steps.

 

Alternatively u can use microsoft excel add in for QC to export the cases.

0 Likes
Highlighted
Absent Member.. Absent Member..
Absent Member..

You can export test cases and their runs to excel using excel reports in the analysis view of the dashboard.

 

A SQL script to extract Folder, Test Set, Test, Run, and Step information might look like the following (for an Oracle DB):

 

SELECT "Path",
CY_CYCLE as "TestSet",
CY_CYCLE_ID as "TestSet ID",
TC_TESTCYCL_ID as "ID Testinstanz",
TS_NAME as "Testname",
TC_STATUS as "Teststatus",
RN_Run_Name as "Run Name",
ST_STEP_ORDER as "Step Order",
ST_STEP_NAME as "Step Name",
ST_STATUS as "Step Status"
FROM (SELECT CF_ITEM_ID, REPLACE(SYS_CONNECT_BY_PATH(CF_ITEM_NAME, '//'),'//','/')
AS "Path"
FROM CYCL_FOLD
START WITH CF_FATHER_ID = <your folder id to start from>
CONNECT BY PRIOR CF_ITEM_ID = CF_FATHER_ID), CYCLE, TESTCYCL, TEST, RUN, STEP
WHERE CY_FOLDER_ID=CF_ITEM_ID
AND TC_CYCLE_ID = CY_CYCLE_ID
AND TS_TEST_ID = TC_TEST_ID
AND RN_TESTCYCL_ID = TC_TESTCYCL_ID
AND ST_RUN_ID = RN_RUN_ID
order by "Path", "TestSet", "Testname", RN_EXECUTION_DATE, RN_EXECUTION_TIME, "Step Order"

 

"SYS_CONNECT_BY_PATH ..." generates the folder hierarchy in Test Lab.

 

HTH,

 

Armin

 

Highlighted
Cadet 3rd Class Cadet 3rd Class
Cadet 3rd Class

I tried to run this and get the following error:

 

ORA-01756: quoted string not terminated properly.

 

I am trying to run it from the Excel report generator query.  We use Oracle as our DB.  Any idea why this error is showing up?

0 Likes
Highlighted
Absent Member.
Absent Member.

Hi

It is written "You can export the test cases to excel using export option from test plan."

 

I use HP ALM 11 and I can not find it anywhere.

 

And can you please write MSSQL version of that SQL ?

0 Likes
Highlighted
Cadet 3rd Class Cadet 3rd Class
Cadet 3rd Class

Hi,

 

we use a slightly different query:

 

SELECT t1.folder,
       t1.cy_cycle         AS testset,
       t2.ts_name          AS NAME,
       t2.ts_user_01       AS priorität,
       t2.tc_tester_name   AS planned_tester,
       t2.tc_actual_tester AS actual_tester,
       t2.tc_status        AS status,
       t2.tc_exec_date     AS datum,
       t2.tc_exec_time     AS zeit,
       t2.ts_steps         AS steps
FROM   (SELECT cy_cycle_id,
               cy_cycle,
               cf_item_id,
               Sys_connect_by_path(cf_item_name, '\') folder
        FROM   cycl_fold
               left outer join cycle
                 ON cy_folder_id = cf_item_id
        START WITH cf_father_id = 0
        CONNECT BY PRIOR cf_item_id = cf_father_id) t1
       left outer join (SELECT tc_cycle_id,
                               ts_name,
                               ts_user_01,
                               tc_tester_name,
                               ts_test_id,
                               tc_status,
                               tc_exec_date,
                               tc_exec_time,
                               tc_actual_tester,
                               ts_steps
                        FROM   testcycl,
                               test
                        WHERE  tc_test_id = ts_test_id) t2
         ON t1.cy_cycle_id = t2.tc_cycle_id
WHERE  t1.ordner NOT LIKE '%Archiv%'
ORDER  BY t1.ordner

 Have fun,

 

michael

0 Likes
Highlighted
Absent Member.. Absent Member..
Absent Member..

Hi Micheal/Arwin,

 

I used the queries in the analysis view of the dashboard, but still it gave error. the message was "The SQL Query has failed to run with the following error. Failed SQL /*~~*/ <then the entire query> ". Any clue how to resolve the query error message.

 

In the query for CF_FATHER_ID i have given teh value as 0.

 

Attached is the screenshot "SQL excel.jpg"

 

Regards,

Krushna

0 Likes
Highlighted
Cadet 3rd Class Cadet 3rd Class
Cadet 3rd Class

Sorry,

 

had a copy-paste-error.

 

Please try this query:

 

SELECT t1.folder,
       t1.cy_cycle         AS testset,
       t2.ts_name          AS NAME,
       t2.ts_user_01       AS priorität,
       t2.tc_tester_name   AS planned_tester,
       t2.tc_actual_tester AS actual_tester,
       t2.tc_status        AS status,
       t2.tc_exec_date     AS datum,
       t2.tc_exec_time     AS zeit,
       t2.ts_steps         AS steps
FROM   (SELECT cy_cycle_id,
               cy_cycle,
               cf_item_id,
               Sys_connect_by_path(cf_item_name, '\') folder
        FROM   default_tkkb_db.cycl_fold
               left outer join default_tkkb_db.cycle
                 ON cy_folder_id = cf_item_id
        START WITH cf_father_id = 0
        CONNECT BY PRIOR cf_item_id = cf_father_id) t1
       left outer join (SELECT tc_cycle_id,
                               ts_name,
                               ts_user_01,
                               tc_tester_name,
                               ts_test_id,
                               tc_status,
                               tc_exec_date,
                               tc_exec_time,
                               tc_actual_tester,
                               ts_steps
                        FROM   default_tkkb_db.testcycl,
                               default_tkkb_db.test
                        WHERE  tc_test_id = ts_test_id) t2
         ON t1.cy_cycle_id = t2.tc_cycle_id
WHERE  t1.folder NOT LIKE '%Archiv%'
ORDER  BY t1.folder

 Best Regards,

Michael

0 Likes
Highlighted
Absent Member.
Absent Member.

hello,

 

I have tried Armin's query in ALM 11 and I get this error : the sql query have failed to run !

I've checked the tables and I have no clue why I cannot run the query.

Many thanks if you got an idea.

 

PS : I'm trying to export tests stes without the steps description.

 

Gabrielle

0 Likes
Highlighted
Knowledge Partner Knowledge Partner
Knowledge Partner

 Hi,

 

Which database server are you using Oracle or SQL ?

These are Oracle queries these wont execute on SQL db.

we need to modify them.

 

Thanks,

Srihari

404-579-5368

0 Likes
Highlighted
Absent Member.
Absent Member.

Hi, I did modify it as I use the SQL/build query of ALM. (so I guess is sql db)

I did this the below query for right now but the problem is that I need the same query to export my test cases sets from the test plan. I don't need to export the test case steps, as we have decided to write all tests linked to a campaign by using the tests by test name + description and the compulsory fields.

 

Can u guide me or tell what tables I should use. It's more a question of time rather than understanding how to build the query.

Many thanks Gabrielle

 [u can directly wrote me at GARZ.UTI@cirra.fr] - France:

 

SELECT   PATH.path || '/' || CYCLE.CY_CYCLE AS PATH,
         CYCL_FOLD.CF_DESCR /*Test Set Folder (CYCLE).Description*/   ,
          CYCL_FOLD.CF_ITEM_ID /*Test Set Folder (CYCLE).Test Set Folder ID*/,
           CYCL_FOLD.CF_ITEM_PATH /*Test Set Folder (CYCLE).Test Set Folder Path*/ ,
                   CYCLE.CY_CYCLE /*Test Set.Name*/ , CYCLE.CY_CYCLE_ID /*Test Set.Test Set ID*/  ,
                    TESTCYCL.TC_TEST_ID /*Test Instance.Test*/  ,  TESTCYCL.TC_CYCLE_ID /*Test Instance.TestSet*/    ,
                     TEST.TS_TEST_ID /*Test.Test ID*/          ,  TEST.TS_NAME /*Test.Test Name*/  ,
                      RUN.RN_TESTCYCL_ID /*Run.Test Instance*/       ,  RUN.RN_RUN_ID /*Run.Run ID*/   ,
                       STEP.ST_RUN_ID /*Test Step.Run ID*/ , STEP.ST_PATH /*Test Step.Path*/
 from  CYCLE /*Test Set*/  ,  CYCL_FOLD /*Test Set Folder (CYCLE)*/    ,  TESTCYCL /*Test Instance*/    ,
  TEST /*Test*/  ,  RUN /*Run*/ ,  STEP /*Test Step*/,
  (SELECT CF_ITEM_ID, substr(REPLACE ( SYS_CONNECT_BY_PATH(CF_ITEM_NAME, ','),',',','),2) AS Path
FROM CYCL_FOLD
START WITH CF_FATHER_ID =0
CONNECT BY PRIOR CF_ITEM_ID = CF_FATHER_ID) PATH

WHERE CY_FOLDER_ID=CYCL_FOLD.CF_ITEM_ID
AND TC_CYCLE_ID = CY_CYCLE_ID
AND TS_TEST_ID = TC_TEST_ID
AND RN_TESTCYCL_ID = TC_TESTCYCL_ID
AND ST_RUN_ID = RN_RUN_ID
AND PATH.CF_ITEM_ID = CYCL_FOLD.CF_ITEM_ID

0 Likes
Highlighted
Absent Member.
Absent Member.

Hi Armin,

 

It is saying that "Sys_connect_by_path" is not a recognized function name. I am using SQL Database.

 

Attaching the screenshot for your reference.

 

Thanks

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.