SQL to pull Dynamic Test Summary Report from ALM ..Quick view about the Project Status!!!!

We often get Queries on the Status of the Execution /Project .Requirement Coverage ,test Case associated with it and Defect if any associated with it.
In ALM we do not have a Standardized Report in the form of Excel/Word doc which just pulls the Report.We end up doing a Manual Work ..Manually pick the Requirements Pick the test case ..then defects if any...Imagine a Project with Multiple Requirements and 1000 Test Cases ..this is almost a impossible task..So Solution??????????

Go to Dashboard in ALM ,Select a New Excel Report ....

Query...

   SELECT t1.RC_REQ_ID AS RequirementID,
  t1.RQ_REQ_NAME    AS REQUIREMENTDESC,
  t1.TS_NAME        AS TESTCASENAME,
  BG_BUG_ID         AS DEFECTID,
  BG_SUMMARY,
  BG_DESCRIPTION
FROM
  (SELECT *
  FROM REQ R,
    REQ_COVER RC,
    TEST T
  WHERE RC_ENTITY_ID=T.TS_TEST_ID
  AND Rq_req_product='PROJECT NAME'
  AND R.RQ_REQ_ID   =RC.RC_REQ_ID
  )T1
LEFT OUTER JOIN
  ( SELECT * FROM LINK L,BUG B WHERE L.LN_BUG_ID=B.BG_BUG_ID
  )T2
ON T1.TS_TEST_ID       =T2.LN_ENTITY_ID
AND T2.LN_ENTITY_TYPE IN ('TEST','TESTCYCL')
AND BG_PROJECT         ='PROJECT NAME'

Report Done!!!!!!!!!!!!!!
Let us know if you need any assistance.

Query to return only test sets with Non Closed defects

HI ,
Below query will give the result for all the test set , which will have linked defect status as non closed status.

Please  notice that this query will account the defects which are conntect to test set only. (there miight be some defect which are linked to test case or test step.)

SO this will give us the name of the Test Set and Ensure that these Test Set have doesnt have any closed defect.

select cy_cycle_id as 'Test Set Name',BG_status 'BUG Status' from cycle,link,bug
where cy_cycle_id=LN_ENTITY_ID and LN_ENTITY_TYPE='CYCLE' and ln_bug_id=bg_bug_id

and cy_cycle_id not in (select ln_entity_id from link,bug
where LN_ENTITY_TYPE='CYCLE' and ln_bug_id=bg_bug_id and bg_status='Closed') 

Please comment if you have nay issue or the cpncern with the result set

A New Page Dedicated to HP-Quality Center

Hi Folks this is a page which will be dedicated to HP-Quality Center .
We will discuss about HP-QC/HP-ALM along with HP OTA related topics here