Do you have feedback on our new interface?
Do you have feedback on our new interface? Let us know HERE
Highlighted
AB-Decathlon
New Member.
120 views

[ALM]: excel extraction of run with test config

Hello all,

on ALM 12,21 version, in company, we use excel extraction ot create own report with extracted data from run.

All extracted datas are ok on tests and runs..

Since this new program version in testing period, testing teams are using testconfigs. SQL request doesn't extract expected status of test config. I tried to change request to have exepected test configs status. but i didn't arrive it.

I have expected status of testconfig runs, but i have them not only one time, but as many times as test configs present in testset.

example: if in testset, i have 5 testconfigs, so extract provides 5 times status of each testconfigs

repertory ABC has only 5 testconfigs.

TC1, TC2, TC3, TC4 and TC5

TC1 has 4 runs

modified request provide me datas like this

TCNAME, REPERTORY, STATUS

TC1  , ABC,  STATUS_TC1_RUN1   TC1  , ABC,  STATUS_TC1_RUN1   TC1  , ABC,  STATUS_TC1_RUN1   TC1  , ABC,  STATUS_TC1_RUN1   TC1  , ABC,  STATUS_TC1_RUN1   

TC1  , ABC,  STATUS_TC1_RUN2  TC1  , ABC,  STATUS_TC1_RUN2  TC1  , ABC,  STATUS_TC1_RUN2  TC1  , ABC,  STATUS_TC1_RUN2  TC1  , ABC,  STATUS_TC1_RUN2

TC1  , ABC,  STATUS_TC1_RUN3  TC1  , ABC,  STATUS_TC1_RUN3  TC1  , ABC,  STATUS_TC1_RUN3  TC1  , ABC,  STATUS_TC1_RUN3  TC1  , ABC,  STATUS_TC1_RUN3

TC1  , ABC,  STATUS_TC1_RUN4  TC1  , ABC,  STATUS_TC1_RUN4  TC1  , ABC,  STATUS_TC1_RUN4  TC1  , ABC,  STATUS_TC1_RUN4  TC1  , ABC,  STATUS_TC1_RUN4

please find extract request:

INITIAL REQUEST WITH TEST STATUS

select * from(
SELECT
CYCL_FOLD.CF_ITEM_NAME AS Dossier ,
CYCLE.CY_CYCLE AS Campagne ,
TC_TEST_ORDER as Test_Order ,
RUN.RN_TEST_ID AS Test_id ,
CYCLE.CY_CYCLE_ID AS Cycle_id ,
TEST.TS_NAME AS Test_name ,
TEST.TS_USER_01 AS Criticity ,
TEST.TS_USER_06 AS Main_project ,
TEST.TS_USER_02 AS Business_domain ,
TEST.TS_RESPONSIBLE AS Designer ,
TEST.TS_USER_04 AS Approver ,
TEST.TS_STATUS AS Status ,
P.TC_PLAN_SCHEDULING_DATE as Planned_exec_date,
TEST.TS_USER_03 AS Perimeter ,
TEST.TS_USER_05 AS Component ,
TEST.TS_USER_07 AS Version ,
RN_STATUS AS Test_Status ,
RN_RUN_ID AS Run_id ,
RN_EXECUTION_DATE AS Run_date ,
RN_EXECUTION_TIME AS Run_Hour ,
RN_TESTER_NAME AS Tester


FROM
RUN ,
CYCLE ,
CYCL_FOLD ,
TEST ,
TESTCYCL P

WHERE
RUN.RN_CYCLE_ID = CYCLE.CY_CYCLE_ID
AND TC_CYCLE_ID = CY_CYCLE_ID
AND TC_TEST_ID = TS_TEST_ID

AND TS_TEST_ID = RN_TEST_ID
and RN_STATUS <> 'N/A'
AND CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
-- nom de l'instance de test
--and cycle.CY_CYCLE like 'OUT01.001 Transfert CAC Rouv (EWM) to CAR Caen (AS400)%'
-- nom du répertoire contenant les instances de test
AND ((CYCL_FOLD.CF_ITEM_NAME like 'name_of_repertory%'))
)t3

Left outer join (SELECT RN_TEST_ID,RN_CYCLE_ID,max(RN_RUN_ID)as maxID FROM RUN GROUP BY RN_TEST_ID,RN_CYCLE_ID )t4 on (t4.RN_TEST_ID=t3.Test_id and t4.RN_CYCLE_ID=t3.Cycle_id and t4.maxID=Run_id )
where RN_TEST_ID is not NULL and RN_CYCLE_ID is not NULL and maxID is not NULL

 

 

MODIFIED REQUEST with TESTCONFIG status


select * from(
SELECT
CYCL_FOLD.CF_ITEM_NAME AS Dossier ,
CYCLE.CY_CYCLE AS Campagne ,
TC_TEST_ORDER as Test_Order ,
RUN.RN_TEST_ID as RN_Testid ,
RUN.RN_TEST_CONFIG_ID AS Test_id ,
CYCLE.CY_CYCLE_ID AS Cycle_id ,
TEST.TS_NAME AS Test_name ,
TEST_CONFIGS.TSC_NAME as Test_config_name ,
TEST.TS_USER_01 AS Criticity ,
TEST.TS_USER_06 AS Main_project ,
TEST.TS_USER_02 AS Business_domain ,
TEST.TS_RESPONSIBLE AS Designer ,
TEST.TS_USER_04 AS Approver ,
TEST.TS_STATUS AS Status ,
P.TC_PLAN_SCHEDULING_DATE as Planned_exec_date,
TEST.TS_USER_03 AS Perimeter ,
TEST.TS_USER_05 AS Component ,
TEST.TS_USER_07 AS Version ,
RN_STATUS AS Test_Status ,
RN_RUN_ID AS Run_id ,
RN_EXECUTION_DATE AS Run_date ,
RN_EXECUTION_TIME AS Run_Hour ,
RN_TESTER_NAME AS Tester


FROM
RUN ,
CYCLE ,
CYCL_FOLD ,
TEST ,
TESTCYCL P ,
TEST_CONFIGS

WHERE
RUN.RN_CYCLE_ID = CYCLE.CY_CYCLE_ID
AND TC_CYCLE_ID = CY_CYCLE_ID
AND TC_TEST_ID = TS_TEST_ID

AND TS_TEST_ID = RN_TEST_ID
AND RN_TEST_CONFIG_ID = TSC_ID
and RN_STATUS <> 'N/A'
AND CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID
-- nom de l'instance de test
--and cycle.CY_CYCLE like 'OUT01.001 Transfert CAC Rouv (EWM) to CAR Caen (AS400)%'
-- nom du répertoire contenant les instances de test
AND ((CYCL_FOLD.CF_ITEM_NAME like '19.1 Downstream (2) TI AS400toEWM LOG INBOUND%'))
)t3
Left outer join
(SELECT RN_TEST_ID, RN_CYCLE_ID, max(RN_RUN_ID)as maxID FROM RUN GROUP BY RN_TEST_ID,RN_CYCLE_ID )t4
on
(t4.RN_TEST_ID=t3.RN_Testid and t4.RN_CYCLE_ID=t3.Cycle_id and t4.maxID=t3.Run_id )

i expect to have ony 

TC1  , ABC,  STATUS_TC1_LASTRUN

TC2  , ABC,  STATUS_TC2_LASTRUN

TC3  , ABC,  STATUS_TC3_LASTRUN

TC4  , ABC,  STATUS_TC4_LASTRUN

TC15 , ABC,  STATUS_TC5_LASTRUN

please help me to fix modified request to have only 1 line of last run of each testconfig

Regards Aurélien

Tags (1)
0 Likes
1 Reply
Micro Focus Expert
Micro Focus Expert

Re: [ALM]: excel extraction of run with test config

this is wrong community, you should be posting on https://community.softwaregrp.com/t5/Quality-Center-ALM/ct-p/sws-QC

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.