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.
-- 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...
-- 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
-- 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.
-- 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...
Tech tips from JDS

Browser Console
Read More

Glide Variables
Read More

Understanding Database Indexes in ServiceNow
Read More

Fast-track ServiceNow upgrades with Automated Testing Framework (ATF)
Read More

Read More

Splunk .conf18
Read More

ServiceNow Catalog Client Scripts: G_Form Clear Values
Read More

Is DevPerfOps a thing?
Read More

The benefits of performance testing with LoadRunner
Read More

Monitoring Atlassian Suite with AppDynamics
Read More

5 quick tips for customising your SAP data in Splunk
Read More

How to maintain versatility throughout your SAP lifecycle
Read More

How to revitalise your performance testing in SAP
Read More

Reserve and import data through Micro Focus ALM
Read More

How to effectively manage your CMDB in ServiceNow
Read More

ServiceNow and single sign-on
Read More

How to customise the ServiceNow Service Portal
Read More

Integrating a hand-signed signature to an Incident Form in ServiceNow
Read More

Integrating OMi (Operations Manager i) with ServiceNow
Read More

Implementing an electronic signature in ALM
Read More

Service portal simplicity
Read More

Learning from real-world cloud security crises
Read More

Static Variables and Pointers in ServiceNow
Read More

Citrix and web client engagement on an Enterprise system
Read More

Understanding outbound web services in ServiceNow
Read More

How to solve SSL 3 recording issues in HPE VuGen
Read More

How to record Angular JS Single Page Applications (SPA)
Read More

Calculating Pacing for Performance Tests
Read More

Vugen and GitHub Integration
Read More

What’s new in LoadRunner 12.53
Read More

Filtered Reference Fields in ServiceNow
Read More

ServiceNow performance testing tips
Read More

Monitor Dell Foglight Topology Churn with Splunk
Read More

Straight-Through Processing with ServiceNow
Read More

Splunk: Using Regex to Simplify Your Data
Read More

ServiceNow Choice List Dependencies
Read More

Tips for replaying RDP VuGen scripts in BSM or LoadRunner
Read More

Incorporating iSPI metric reports into MyBSM dashboard pages
Read More

Using SV contexts to simulate stored data
Read More

What’s new in LoadRunner 12.02
Read More

Recycle Bin for Quality Center
Read More

LoadRunner Correlation with web_reg_save_param_regexp
Read More

LoadRunner 11.52
Read More

QC for Testers – Quiz
Read More

Agile Performance Tuning with HP Diagnostics
Read More

What’s new in HP Service Virtualization 2.30
Read More

Understanding LoadRunner Virtual User Days (VUDs)
Read More

Problems recording HTTPS with VuGen
Read More

Improving the management and efficiency of QTP execution
Read More

Performance testing Oracle WebCenter with LoadRunner
Read More

Generating custom reports with Quality Center OTA using Python
Read More

Asynchronous Communication: Scripting For Cognos
Read More

How to fix common VuGen recording problems
Read More

Monitoring Active Directory accounts with HP BAC
Read More

URL Attachments in Quality Center
Read More

What’s new in LoadRunner 11.00?
Read More

Restore old License Usage stats after upgrading Quality Center
Read More

Changing LoadRunner/VuGen log options at runtime
Read More

Restricting large attachments in Quality Center
Read More

Retrieving Quality Center user login statistics
Read More

A comparison of open source load testing tools
...
Read More

Worst practices in performance testing
Read More

LoadRunner Sales Questions
Read More

LoadRunner Analysis: Hints and tips
Read More

LoadRunner in Windows 7
HP Loadrunner 11 is now available. This new version now natively supports Windows 7 and Windows Server 2008. I ...
Read More

Using the QuickTest Professional “commuter” license
Read More

Installing HP Diagnostics
Read More

Understanding LoadRunner licensing
Read More

VuGen scripting for YouTube video
Read More

Creating a Web + MMS vuser
Read More

Why you should use backwards dates
Read More

How to get the host’s IP address from within VuGen
Read More

VuGen scripting for BMC Remedy Action Request System 7.1
Read More

Unique usernames for BPM scripts
Read More

Mapping drives for LoadRunner Windows monitoring
Read More

VuGen feature requests
Read More

LoadRunner script completion checklist
Read More

Querying Quality Center user roles
Read More

Querying the Quality Center Database
Read More

HPSU 2009 Presentation – Performance Testing Web 2.0
Read More

Scaling HP Diagnostics
Read More

Global variables aren’t really global in LoadRunner
Read More

Client-side certificates for VuGen
Read More

Detect malicious HTML/JavaScript payloads with WebInspect (e.g. ASPROX, Gumblar, Income Iframe)
Read More

VuGen code snippets
Read More

Integrating QTP with Terminal Emulators
Read More

Why you must add try/catch blocks to Java-based BPM scripts
Read More

Querying a MySQL database with LoadRunner
Read More

ANZTB 2009 Presentation: Performance Testing Web 2.0
Read More

How to make QTP “analog mode” steps more reliable
Read More

Testing multiple browsers in a Standardized Operating Environment (SOE)
Read More

DNS-based load balancing for virtual users
Read More

What’s new in LoadRunner 9.50?
Read More

Calculating the difference between two dates or timestamps
Read More

The “is it done yet” loop
Read More

Think time that cannot be ignored
Read More

Understanding aggregate variance within LoadRunner analysis
Read More

Load balancing vusers without a load balancer
Read More

Harvesting file names with VuGen
Read More

Parameterising Unix/Posix timestamps in VuGen
Read More

HP Software trial license periods
Read More

How to handle HTTP POSTs with a changing number of name-value pairs
Read More

VuGen string comparison behaviour
Read More

Persistent data in VuGen with MySQL
Read More

How to write a Performance Test Plan
Read More

Unable to add virtual machine
To get ...
Read More

LoadRunner scripting languages
Read More

WDiff replacement for VuGen
Read More

Testing web services with a standard Web Vuser
Read More

Why your BPM scripts should use Download Filters
Read More

Querying your web server logs
Read More

Importing IIS Logs into SQL Server
Read More

QTP “Uninstall was not completed” problem
Read More

VuGen correlation for SAP Web Dynpro
Read More

How to save $500 on your HP software license
Read More

Testing and monitoring acronyms
Read More

Solving VuGen script generation errors
Read More

An introduction to SiteScope EMS Topology
Read More

Using the BAC JMX Console
Read More
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.
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
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.
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 :(
Same here. Nor can I find the illusive Help > Documentation > Database Schema
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)