Wednesday, May 5

How to retrieve requirement - test case - linked defect using excel reports

QC 9.2 has feature called excel reports. This feature is very versatile as you can retrieve complex data which simple reports or graphs from QC will not be able to do. One of the report that may be useful for any testing project is to find which all defects are linked to test cases and these test cases are covering which of the requirements. This will give you data that can be used to address which defects are more severely effecting your testing progress and resolving a defect what requirements/test cases you can quickly progress.

Here below is entity relationship diagram that you can find in HP quality center database reference. It is one of the help documentation available in QC.

For folks who not sure how to access excel reports in QC , follow below steps you will be amazed to find this feature.

Login into QC with project and domain. Then click on Tools and next select excel reports. It will open up another window. In the top menu , there is + button which on clicking will open report. You can make that report public or private.

TEST - REQUIREMENT ER DIAGRAM


So the box highlighted in Red is the table which stores Test details ( from Plan).  The TEST table is connected to Requirement (REQ) table via requirement coverage table REQ_COVER..

At the start , your query would be simple one as below which will retrieve test details

Select * from TEST t

Next we need join both REQ and REQ_COVER tables. Join condition between REQ and REQ_COVER is
rq_req_id = rc_req_id. Join condition between REQ_COVER and TEST is rc_entity_id = ts_test_id and rc_entity_type = 'TEST'. Using these lets construct below query

Select r.rq_req_name, t.TS_NAME from TEST t, REQ r, REQ_COVER rc
where rq_req_id = rc_req_id
and rc_entity_id = ts_test_id
and rc_entity_type = 'TEST'

The above query will list all the tests and requirements attached to those tests.

Next is how to find linked defects. Basically the defects are linked to tests which are pulled in the test sets of QC. We have to join test sets (TESTCYCL) to test (TEST) first, then join linked defects table LINK
Join between TESTCYCL and TEST tables is as below 

TEST..ts_test_id = TESTCYCL.tc_test_id

The join between test set (TESTCYCL) and LINK  is as below

LINK.ln_entity_type = 'TESTCYCL' and TESTCYCL.TC_TESTCYCL_ID = LINK.ln_entity_id

Here you can notice the condition "LINK.ln_entity_type = 'TESTCYCL'  " has value equated to TESTCYCL which is table name containing test sets. Basically if you link defects to test sets or tests within test set then, ln_entity_type will have the value TESTCYCL 

So combining all the conditions we can make the query which will retrieve requirements/test cases/linked defects

Select r.rq_req_name, t.TS_NAME,ln_bug_id from TEST t, REQ r, REQ_COVER rc, TESTCYCL tc, LINK l
where rq_req_id = rc_req_id
and rc_entity_id = ts_test_id
and rc_entity_type = 'TEST'
and t.ts_test_id = tc.tc_test_id
and l.ln_entity_type = 'TESTCYCL'
and Tc.TC_TESTCYCL_ID = l.ln_entity_id


If you run the above query in excel reports, you should be able to retrieve the mapping of link defects - test cases- requirements.

*Note : This will not retrieve those defects which are linked to test cases but the test cases are not covered to a requirement.


7 comments:

  1. Thanks a lot for your blog it is helpful.

    I have made a script to extract
    test case - test runs

    I have added a post processing to make a Pivot table so i can see
    what version each run was tested on and the result.
    Now I would like to have everything in one pivot table
    REQ-TEST_TESTRUN- BUG


    The script looks today
    SELECT

    CYCL_FOLD.CF_ITEM_NAME AS 'Folder Name',
    CYCLE.CY_CYCLE AS 'Test Set Name',
    ALL_LISTS.AL_DESCRIPTION AS 'Subject',
    TEST.TS_EXEC_STATUS as 'Latest test status',/*Test.Execution Status*/
    TEST.TS_NAME AS 'Test Name',

    TEST.TS_USER_01 as 'Plan : Approval status',/*Test.Approval Status*/
    RUN.RN_RUN_NAME AS 'Run',
    RUN.RN_STATUS AS 'Execution Status',
    RUN.RN_USER_02 AS 'Approval Status',
    RUN.RN_TESTER_NAME AS 'Tester', /*Run.Last Tester*/
    RUN.RN_EXECUTION_DATE AS 'Run exe date', /*Run.Exec Date*/
    RUN.RN_USER_03 AS 'SW Version tested', /*Run.SW version*/
    BUG.BG_RUN_REFERENCE as 'bug'/*Defect.Run Reference*/
    FROM
    RUN

    LEFT JOIN TESTCYCL ON
    TESTCYCL.TC_TESTCYCL_ID = RUN.RN_TESTCYCL_ID /*Run.Test*/
    LEFT JOIN BUG ON
    BUG.BG_RUN_REFERENCE = RUN.RN_RUN_ID /*Run.Run Name*/
    LEFT JOIN TEST ON
    RUN.RN_TEST_ID = TEST.TS_TEST_ID

    LEFT JOIN CYCLE ON
    TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID /*Test Set.Cycle ID*/

    LEFT JOIN CYCL_FOLD ON
    CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID /*Test Set Folder (CYCLE).Test Set Folder ID*/

    LEFT JOIN ALL_LISTS ON
    TEST.TS_SUBJECT = ALL_LISTS.AL_ITEM_ID /*Test Plan Folder.Item Id*/

    WHERE
    CYCLE.CY_USER_05 = '2 - Commissioning'

    and post processing is recorded in macro to create pivot table

    ReplyDelete
  2. Thank you Per ekvall for your comment. It is really nice info from you where you manage to join Run table too

    ReplyDelete
  3. Thank you for the query. I have question, how should I replace this statement with standard fields? CYCLE.CY_USER_05 = '2 - Commissioning'.

    With out where condition, I am getting duplicate records.

    - Dinkar

    ReplyDelete
  4. HI,

    Can you help me in fetching:
    1. Requirements which are not mapped to test cases.
    2. Test cases which are in BLocked status.
    3. Defects which are not linked.

    ReplyDelete
  5. Hi Shambu,

    I used the above query to get the req-tc-defct details, in 1 go.
    I saw that, the entity type is 'TESTCYCL' and so defcts entered at step level or run level cannot be retrived using this query. I verified the LINK table and found that such defects are under entity type STEP and RUN. Can you please provde me the query to get defects that are linked to a test case, at step level and run level. PLEASE RESPOND

    ReplyDelete
  6. Hi Anonymous
    I m not quite sure whether defects can be linked to step or run. Previous in QC 9.2 , I remember defects were linked to a test case as test instance level. Unfortunately , I been away on QC for long time and do not have running instance to verify your issue. Hope to check the issue when I get my hands on QC again. Not sure when :(

    ReplyDelete
  7. Hi I'm using the below mentioned query to return all requirements, test cases and mapped defects. But when there is no defect linked to the test case mapped to requirements, there no records retrieved. Can you please tel me what is wrong here. Select RC_REQ_ID, REQ.RQ_REQ_NAME , REQ_COVER.RC_ENTITY_ID , TEST.TS_NAME , TEST.TS_EXEC_STATUS ,TESTCYCL.TC_TESTCYCL_ID
    from REQ_COVER , TEST ,REQ ,LINK, TESTCYCL
    Where REQ_COVER.RC_REQ_ID = REQ.RQ_REQ_ID
    AND REQ_COVER.RC_REQ_ID=10441
    AND REQ_COVER.RC_ENTITY_ID = TEST.TS_TEST_ID
    AND TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID

    ReplyDelete

---------------------------------------------

Related Posts Plugin for WordPress, Blogger...