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.

The other useful blogs related to Audit tables and QC history can be found in following locations

1. First time Fix faliures or Reopen defects
2. How to retrieve Quality Center bug details and Audit tables information
3. How to retrieve QC status history for closed defect
4. How to retrieve test instances and test sets within particular folder