JDS Australia

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…

Related posts:

1. HP Software Trial License Periods HP software tools all have have different trial licensing periods....
2. Querying Quality Center user roles Quality Center stores the user/role relationship in a strange way...
3. Restricting large attachments in Quality Center Once your Quality Center users discover that they can add...
4. Restore old License Usage stats after upgrading Quality Center Following the upgrade of Quality Center from v.9 to v.10,...

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.