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 on “Querying the Quality Center Database

  1. 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?

  2. 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

  3. 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?

  4. 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.

  5. 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 ..

  6. 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

  7. 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

  8. 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)

  9. 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.

  10. 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!!!

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

  12. 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?

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

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

  15. 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?

  16. 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

  17. 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?

  18. 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

  19. 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

  20. 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.

  21. 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