SQL Queries for Quality Center Modules

One of the few good things [there are only few] about QC is that all the info is stored in a MSSQL DB, and can be easily accessed via QC OTA, Analysis Modules, etc. Here are a few samples.

A. Notes on some Components of QC Database -

• RELEASE_CYCLES - this table stores details about the releases created in the Release module.
• CYCLE - this table stores info about the test sets, and uses CY_ASSIGN_RCYC
• TESTCYCL - this table stores info about the instances of test runs, status, etc
• TEST - this table stores info about the test names, test types [manual, automated], designer, etc

 
B. Process to build SQL Queries for different Quality Center modules

All Analysis reports have to be built in the Dashboard >> Analysis module. There are 3 different types of reports that can be built, but Excel is the one we are focusing on right now, since, we can add SQL Queries only to Excel type reports.

Report type - Create a report to get the Status and Count of all the tests present under different Test Sets, for a particular Release Cycle.

Solution Steps -

• Create a Release Cycle under Management >> Release Module, based on the name of your current release. Eg., 'App_Release2_SIT'.
• Note down the value of 'Target Cycle ID' field populated in QC - this would get saved as CY_ASSIGN_RCYC field in the QC database, lets assume it as 1005.
• Create a folder in Test Lab as 'Parent_TL_Folder'. Create another subfolder called SIT under this parent folder, and assign the release cycle, 'App_Release2_SIT' to this folder.
• If there are 2 different cycles in your plan, one for Functional SIT and one for Regression, then create another cycle for Regression like 'App_Release2_Regression'. Then create a seperate folder for Regression tests, under the same 'Parent_TL_Folder' at the same level as 'SIT' folder, and assign this regression cycle 'App_Release2_Regression' to this folder.
• Now, create all the different Test Sets for this release under this Test Lab folder. Lets say there are 3 test sets called 'Artifact1' 'Artifact2' 'Artifact3'.
• Now, pull all your tests under these 3 tet sets, from the Test Plan.
• Once this all is done, all you need is the 'Target Cycle ID' for both the cycles [SIT and Regression] to query all the tests for the release.

C. Sample Queries -
--------------------------------------------------------------
--Query to get the execution Status and Count for all the tests under a given release cycle

SELECT CYCLE.CY_CYCLE ,
COUNT (*) AS "No of Tests" ,
COUNT (CASE WHEN TESTCYCL.TC_STATUS = 'PASSED' THEN 1 END ) AS 'Passed' ,
COUNT (CASE WHEN TESTCYCL.TC_STATUS = 'FAILED' THEN 1 END ) AS 'Failed' ,
COUNT (CASE WHEN TESTCYCL.TC_STATUS = 'BLOCKED' THEN 1 END ) AS 'Blocked' ,
COUNT (CASE WHEN TESTCYCL.TC_STATUS = 'NO RUN' THEN 1 END ) AS 'No Run' ,
COUNT (CASE WHEN TESTCYCL.TC_STATUS IN ( 'PASSED' , 'FAILED' ) THEN 1 END ) AS 'Executed' ,
COUNT (CASE WHEN TESTCYCL.TC_STATUS = 'NOT COMPLETED' THEN 1 END ) AS 'Partially Executed'
FROM CYCLE
JOIN TESTCYCL ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
--JOIN TEST ON TEST.TS_TEST_ID = TESTCYCL.TC_TEST_ID    --Join with TEST table, can help us pull out TEST_NAME as well for the tests
WHERE 1=1
AND CYCLE.CY_ASSIGN_RCYC = '1005'
--AND CYCLE.CY_CYCLE LIKE 'BRD%'    --filter all the test sets starting with BRD
GROUP BY CYCLE.CY_CYCLE

--------------------------------------------------------------
-- Query to get the Name and ID of the Release Cycle

SELECT RCYC_NAME, RCYC_ID, *
FROM RELEASE_CYCLES
WHERE 1=1
AND RCYC_ID = '1005'

--------------------------------------------------------------
--Query to get Names of all the Test Sets under the chosen Release Cycle

SELECT CY_CYCLE AS "TEST_SET_NAME" , *
FROM CYCLE
WHERE 1=1
AND CY_ASSIGN_RCYC = '1005'
ORDER BY CY_CYCLE

--------------------------------------------------------------
--Query to get Defect related details for a Release

SELECT
BG_BUG_ID AS "DEFET ID" ,
BG_SEVERITY AS "SEVERITY" ,
BG_STATUS AS "STATUS" ,
BG_SUMMARY AS "SUMMARY" ,
BG_PRIORITY AS "PRIORITY" ,
BG_RESPONSIBLE AS "ASSIGNED TO" ,
BG_USER_11 AS "DETECTED BY" ,
BG_DETECTION_DATE AS "DETECTED ON" ,
BG_DEV_COMMENTS AS "COMMENTS"
FROM BUG
WHERE 1=1
AND BG_STATUS NOT IN ('Closed', Deferred' , 'TestReady' )
AND BG_USER_12 = 'SIT ENV'
AND BG_USER_13 = 'App_Release2_SIT'

--------------------------------------------------------------
--Query to get BPT Components for a Test. 
--The below query gets the design steps of the components part of a BPT test in the order they are in the test. Just add the TestID where shown.

SELECT
C.CO_NAME || '[' || BT.BC_CO_INSTANCE || ']',
CS.CS_STEP_NAME ,
CS.CS_DESCRIPTION,
CS.CS_EXPECTED
FROM BPTEST_TO_COMPONENTS BT,
COMPONENT C,
COMPONENT_STEP CS
WHERE 1=1
AND BT.BC_PARENT_ID = <testIDhere>
AND BT.BC_CO_ID = C.CO_ID
AND C.CO_ID = CS.CS_COMPONENT_ID
ORDER BY BT.BC_ORDER, CS.CS_STEP_ORDER

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

Install JBehave plugins for IntelliJ and Eclipse

For IntelliJ -

Go to add plugin section, then search the repositories for 'jbehave'
The following plugins [in blue] would be needed