Querying the Quality Center Database

Sometimes the reports available from Quality Center don’t quite give you what you want, and you find that you need to extract data from the QC database and manipulate it using Excel.

Here are a few tips to get you started…

The Excel Report Generator is available under Tools > Excel Reports… and allows you to run arbitary queries against the Quality Center database. Note that even though it is possible to run updates, this should not be done, as you run the risk of hosing all your data; also, be aware that tables and columns may change between QC versions.

1
2
3
4
5
6
7
8
9
10
11
-- This query returns all the tables and fields in the current QC project
-- It is useful because the Entities View in the Excel Report Generator does
-- not show all the tables in the database.
SELECT
  TABLE_NAME AS 'Table Name',
  COLUMN_NAME AS 'Column Name'
FROM
  INFORMATION_SCHEMA.COLUMNS
ORDER BY
  TABLE_NAME,
  COLUMN_NAME

I find it useful to make some notes about which tables I am using and the fields in them. It is especially useful if I ever have to come back and modify my queries.

Requirement (REQ)

  • Req ID (RQ_REQ_ID)
  • Name (RQ_REQ_NAME)
  • Description (RQ_REQ_COMMENT)
  • Requirement Type (RQ_TYPE_ID)
  • Is Folder (RQ_IS_FOLDER) – beware. This contains incorrect data (folders with value “N”)
  • Req Father ID (RQ_FATHER_ID) e.g. the folder id that the requirement is in.
  • Risk (RQ_USER_03)

Requirement Types (REQ_TYPE)

  • Requirement Type ID (TPR_TYPE_ID) – is foreign key for REQ.RQ_TYPE_ID
  • Requirement Type Name (TPR_NAME) – e.g. “Folder”, “Business”, “Functional” etc

Requirement Coverage (REQ_COVER) – maps requirements to test cases

  • Requirement (RC_REQ_ID)
  • Covering Entity ID (RC_ENTITY_ID)
  • Coverage Type (RC_ENTITY_TYPE) – always seems to be “TEST”. Do not put this in a WHERE clause or you will filter out requirements that do not have an associated test case.

Test (TEST) – contains test cases

  • Test ID (TS_TEST_ID)
  • Test Name (TS_NAME)
  • Execution Status (TS_EXEC_STATUS)

Here are a few queries you may find useful…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- This query shows all requirements in all folders.
SELECT
  REQ.RQ_REQ_ID AS 'Requirement ID',
  REQ.RQ_USER_01 AS 'Req ID Project', -- this has the original REQ ID from project doco
  REQ.RQ_REQ_NAME AS 'Requirement Name',
  REQ.RQ_REQ_COMMENT AS 'Requirement Description',
  REQ_TYPE.TPR_NAME AS 'Requirement Type',
  REQ.RQ_USER_03 AS 'Risk'
FROM
  REQ
  JOIN REQ_TYPE
    ON REQ.RQ_TYPE_ID = REQ_TYPE.TPR_TYPE_ID -- join to get the name of the requirement type e.g "Folder"
WHERE
  TPR_NAME != 'Folder' -- Only retrieve requirements of type "Business" or "Functional"
ORDER BY
  RQ_REQ_ID
1
2
3
4
5
6
7
8
9
-- This query shows all test cases in all folders.
SELECT
  TEST.TS_TEST_ID AS 'Test ID',
  TEST.TS_NAME AS 'Test Name',
  TEST.TS_EXEC_STATUS AS 'Execution Status'
FROM
  TEST
ORDER BY
  TEST.TS_TEST_ID

When I am doing complicated joins, I usually draw the tables and their relationships, so I don’t accidentally do a RIGHT OUTER JOIN when I really meant to do a LEFT OUTER JOIN.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- This query shows all requirements with any test cases that are mapped to the requirement.
-- If there are 2 test cases mapped to a requirement, then both will be included.
SELECT
  REQ.RQ_REQ_ID AS 'Requirement ID',
  REQ.RQ_USER_01 AS 'Req ID Project', -- this has the original REQ ID from project doco
  REQ.RQ_REQ_NAME AS 'Requirement Name',
  REQ.RQ_REQ_COMMENT AS 'Requirement Description',
  REQ_TYPE.TPR_NAME AS 'Requirement Type',
  REQ.RQ_USER_03 AS 'Risk',
  TEST.TS_TEST_ID AS 'Test Case ID',
  TEST.TS_NAME AS 'Test Case Name',
  TEST.TS_EXEC_STATUS AS 'Execution Status'
FROM
  REQ
  LEFT JOIN
    REQ_COVER ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID -- join requirements to test cases (REQ to REQ_COVER)
  LEFT JOIN
    TEST ON REQ_COVER.RC_ENTITY_ID = TEST.TS_TEST_ID -- join requirements to test cases (REQ_COVER to TEST)
  JOIN
    REQ_TYPE ON REQ.RQ_TYPE_ID = REQ_TYPE.TPR_TYPE_ID -- join to get the name of the requirement type e.g "Folder"
WHERE
  REQ_TYPE.TPR_NAME != 'Folder' -- Only retrieve requirements of type "Business" or "Functional"
ORDER BY
  REQ.RQ_REQ_ID

Hopefully this is enough to get you started…

30 comments

Hi – I am looking to retrieve the following reports from QC:

All Test Cases Execution count by application
and
Date of Last execution of any Test Cases by application

Is this possible?

I modified your query a bit and added defects associated. That way it is an RTM showing requirements, tests ran against and defects to the runs. Let me know what you think.

— This query shows all requirements with any test cases that are mapped to the requirement.
— If there are 2 test cases mapped to a requirement, then both will be included.

SELECT
CYCL_FOLD.CF_ITEM_NAME AS ‘Test Cycle’,
REQ.RQ_REQ_ID AS ‘Requirement ID’,
REQ.RQ_USER_01 AS ‘Req ID Project’, — this has the original REQ ID from project doco
REQ.RQ_REQ_NAME AS ‘Requirement Name’,
REQ.RQ_REQ_COMMENT AS ‘Requirement Description’,
REQ_TYPE.TPR_NAME AS ‘Requirement Type’,
REQ.RQ_USER_03 AS ‘Test Case #’,
TEST.TS_TEST_ID AS ‘Test Case ID’,
TEST.TS_NAME AS ‘Test Case Name’,
TEST.TS_EXEC_STATUS AS ‘Execution Status’,
BUG.BG_BUG_ID AS ‘Defect’,
BUG.BG_SEVERITY as ‘Severity’,
BUG.BG_Priority as ‘Priority’,
BUG.BG_CYCLE_ID as ‘Cycle’,
BUG.BG_STATUS as ‘Status’
FROM
REQ
LEFT JOIN
REQ_COVER ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID — join requirements to test cases (REQ to REQ_COVER)
LEFT JOIN
TEST ON REQ_COVER.RC_ENTITY_ID = TEST.TS_TEST_ID — join requirements to test cases (REQ_COVER to TEST)
JOIN
REQ_TYPE ON REQ.RQ_TYPE_ID = REQ_TYPE.TPR_TYPE_ID — join to get the name of the requirement type e.g “Folder”
LEFT JOIN CYCL_FOLD on REQ.RQ_FATHER_ID = CYCL_FOLD.CF_FATHER_ID
LEFT JOIN REQ_CYCLES on REQ_CYCLES.RQC_REQ_ID = REQ.RQ_REQ_ID /*Requirement.ReqID*/
LEFT JOIN BUG on BUG.BG_CYCLE_ID = REQ_CYCLES.RQC_CYCLE_ID
WHERE
REQ_TYPE.TPR_NAME != ‘Folder’ — Only retrieve requirements of type “Business” or “Functional”
— (CYCL_FOLD.CF_ITEM_NAME is null or CYCL_FOLD.CF_ITEM_NAME ‘Root’)
ORDER BY
CYCL_FOLD.CF_ITEM_NAME DESC,
REQ.RQ_REQ_ID

Hi, i need to get all the requirements from a test cycle.

Note that i have not linked the requirements to the cycle.

Is there a way to retrieve all the requirement from the test that i have include in the test lab for a specific cycle?

Hi

how do i generate report on when last modification has been done like either test case is added/deleted/modified .Hence the report should contain the Application name along with which test case which is modified(added/deleted/updated) and time.

Hi Stuart, in HP ALM 12.01 when I try to generate a business views excel report, not all the test cases that reside under a given folder from Test Plan are displayed. Eg. if you have 3 levels of folders and you select the one at the top level with the expectation that you will get all the test cases that reside under any subfolders (which I have tried filtering as both Parent Folder or Subject to no avail) nothing is displayed. Even when I went into the customisation view and selected Absolute Path to be displayed, it didnt. The report generates some kind of database codes in place of plain english and gives numeric references instead of a folder name for the subject. Older versions of QC used to produce a full list of test cases just by selecting the subject as the highest level folder and you used to also get the full path. Is there any way that this basic piece of functionality can be made to work in a Test Case report again? Thanks ..

Hi Stuart,

We are publishing a few reports from the analysis center in ALM 12.20 to list the test case contents. However, we when list the test steps, the named parameters (e.g. <<>>) are not included in the steps, just “”.

Any ideas on how to fix this?

thanks

Hi Stuart,

Could you please provide me the DB query to export test cases from particular folder and particular release cycle.
It will be really great and helpful for me.

Thanks

Answer this question please. How do i filter the requirements by folder name as they appear in the requirements module. (I want to exclude certain folders and their requirements from appearing in the report or vice versa have a certain folder and its requirements only in a report)

You can query through SQL server to the database of QC.

Here are some helpful queries that I built for a dashboard for my company…

—Get status of all tests in a cycle
declare @completed float
declare @total float
declare @percentage float
declare @failed float

set @completed = (select count(DISTINCT(RN_TEST_ID))
from td.releases R
inner join td.Release_Cycles RC on r.Rel_ID = RC.RCYC_Parent_ID
inner join td.Cycle Cy on Cy_Assign_RCYC = RC.RCYC_ID
inner join td.TestCycl TC on TC.TC_Cycle_ID = Cy.CY_Cycle_ID
inner join td.Run Rn on Rn.RN_Test_ID = TC.TC_Test_ID
inner join td.Test TS on TS.TS_Test_ID = RN.RN_Test_ID
Where RCYC_ID = ’10’
AND Rn.RN_Execution_Date = DATEADD(DAY, DATEDIFF(DAY, 140, GETDATE()), 0)
and rn.rn_status = ‘Passed’)

select distinct(ts_test_id) as ‘TestID’, ts_name as ‘Name’, max(rn.RN_EXECUTION_DATE) as ‘Time’
into #tmpStatus
from td.Release_Cycles RC
inner join td.Cycle Cy on Cy_Assign_RCYC = RC.RCYC_ID
inner join td.TestCycl TC on TC.TC_Cycle_ID = Cy.CY_Cycle_ID
inner join td.Run Rn on Rn.RN_Test_ID = TC.TC_Test_ID
inner join td.test ts on ts.TS_TEST_ID = rn.RN_TEST_ID
Where RCYC_ID = ’10’
and rn.rn_execution_date = DATEADD(DAY, DATEDIFF(DAY, 140, GETDATE()), 0)
group by ts_test_id, ts_name
order by TS_TEST_ID asc

select max(RN_RUN_ID) as ‘RunID’, RUN.RN_TEST_ID as ‘RunTestID’
into #tmpMaxRun
from td.RUN, #tmpStatus
where #tmpStatus.TestID = RUN.RN_TEST_ID
and RUN.RN_EXECUTION_DATE = #tmpStatus.[Time]
group by RN_TEST_ID

set @failed = (select count(RUN.rn_Status) as ‘Status’
from #tmpStatus
inner join #tmpMaxRun on #tmpStatus.TestID = #tmpMaxRun.RunTestID
inner join td.RUN on #tmpMaxRun.RunID = RUN.RN_RUN_ID
inner join td.TEST ts on ts.ts_test_id = #tmpstatus.testid
where RUN.rn_execution_date = DATEADD(DAY, DATEDIFF(DAY, 140, GETDATE()), 0)
and RUN.RN_EXECUTION_DATE = #tmpstatus.[Time]
and RUN.rn_status = ‘Failed’)

Drop table #tmpStatus
Drop table #tmpMaxRun

set @total = (select count(DISTINCT(RN_TEST_ID))
from td.releases R
inner join td.Release_Cycles RC on r.Rel_ID = RC.RCYC_Parent_ID
inner join td.Cycle Cy on Cy_Assign_RCYC = RC.RCYC_ID
inner join td.TestCycl TC on TC.TC_Cycle_ID = Cy.CY_Cycle_ID
inner join td.Run Rn on Rn.RN_Test_ID = TC.TC_Test_ID
Where RCYC_ID = ’10’
AND Rn.RN_Execution_Date = DATEADD(DAY, DATEDIFF(DAY, 140, GETDATE()), 0))

set @percentage = CAST((@completed/@total)*100 as Decimal (6,1))

select @total as ‘Total Tests’, @completed as ‘Passed Tests’, @failed as ‘Failed Tests’, @percentage as ‘% Passed’

SELECT RCYC_Name As Cycle , cy_cycle as ‘Test Set’,
[Passed],[Failed],[No Run],[Blocked],[Not Completed],
([Passed]+[Failed]+[No RUN]+[Blocked]+[Not Completed]) as ‘Total Scripts’,
ROUND(Cast(([Passed]*100) AS FLOAT)
/
CASE WHEN ISNULL(CAST(([Passed]+[Failed]+[No RUN]+[Blocked]+[Not Completed]) AS FLOAT),1) = 0 THEN 1 ELSE
ISNULL(CAST(([Passed]+[Failed]+[No RUN]+[Blocked]+[Not Completed]) AS FLOAT),1) END,2) As ‘% Passed’
FROM
(

select RCYC_Name,cy_cycle,TC_STATUS
from td.TESTCYCL /*Test Instance*/
Inner Join td.Cycle on cycle.cy_cycle_id = testcycl.tc_cycle_id
inner join td.Release_Cycles on testcycl.TC_ASSIGN_RCYC=Release_Cycles.RCYC_ID
inner Join td.Releases on Releases.Rel_id=Release_Cycles.RCYC_Parent_Id
where Rcyc_ID = ‘1001’
) AS MAIN
PIVOT
(
Count(TC_STATUS)
FOR TC_STATUS IN ([Passed], [Failed],[No Run],[Blocked],[Not Completed])
) as pvt

Hope this helps I know this is a old post.

Is it that, only admin users of QC can do all these querying? Is there any other method for a limited user to do all these??

Thanks!!!

Pls share BUG table fields from query

I want to run a query for test cases executed within specified time period by specific group of testers.
Please suggest.

How to get the old and new names in the test lab module as the column name is reveiwer and column type is user list?

I have to modify existing data in defects tab, is it possible to write a insert query instead of manually updating data?

Yes, you can update the data.

need a query that tells how many testcases with its test case no’s that failed when they were run for the first time

This is great.. How do you query the life cycle of a defect? Meaning, how can I get the total time it took for a defect to go from New to Passed and/or Closed?

Nice Query: with some modification it really comes in usefull :)

How to create database in QC for keeping old records? Please suggest.

sahil khanna

I want a SQL query to get the records of the user who updated the test scripts.

Hi

i need a query for the following requirement.

i want all the test cases related to business requirement from a specific folder.
i have a main folder in project, under that i have another 6 subfolders, so i want TC & BR related to Test cases in an excel sheet for Each Folder.

please help.

Thanks
Naga

srikanth kokalla

i need a query to retrieve all the test cases linked to a particular defect.

Hi Stuart,

I am not able to get the execution status for all test cases in a given TestFolder under Test Lab Module.
Eg: I want to get the execution status from the folder Project1 which is under root directory(/Root/Abc/Project1)

Can you please help me on this?

i am working in qc 9.2 with BPT Add in and i need the query wherein under test plan for each test script what is the asociated BPT Components were added for each testcases like (Busines T code in SAP)
Please post the query this is most urgent

Thanks in advance

Hi

How to query the name of the current QC project we are logged in to? I need to use it in script editor to retrieve the project name.
Thanks in advance.
Archana

Hi,

Thanks for your information. But you know there are several tables available in QC. It is very difficult to understand the relationship between those table to create a report. Is there anyway or ready reference where you can get the details of the tables on what information is stored and the relationship to other tables.

Appreciate your inputs.

Stuart Moncrieff
Stuart Moncrieff

In QC, have a look in Help > Documentation. There is a useful “Database Guide” document which should have the information that you need.

Hello Stuart, did you find this on QC12? or previous versions? As Im using 12 and can´t find that document :(

Pls dont worry abt the above question. I have figured it out. Filtered using REQ.RQ_REQ_PATH and it works a treat.

Thanks Again

Awesome. That makes my work o much easier. Cannot thank you enough.

One question:

How do i filter the requirements by folder name as they appear in the requirements module. (I want to exclude certain folders and their requirements from appearing in the report or vice versa – have a certain folder and its requirements only in a report)

Leave a Reply