JDS Australia » Tech Tips » Querying the Quality Center Database

Querying the Quality Center Database

June 26th, 2009 Posted by Stuart Moncrieff 6 Comments »

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…


Bookmark using any bookmark manager!

 

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

6 Responses to “Querying the Quality Center Database”

  1. Andy Says:
    September 15th, 2010 at 11:07 am

    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)

  2. Andy Says:
    September 15th, 2010 at 11:57 am

    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

  3. Venkatraman Says:
    September 7th, 2011 at 7:21 am

    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.

  4. Stuart Moncrieff Says:
    September 7th, 2011 at 11:00 am

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

  5. Archana Says:
    September 7th, 2011 at 3:02 pm

    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

  6. babu Says:
    October 21st, 2011 at 5:16 am

    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

Leave a Reply