Querying the Quality Center Database

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?

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

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

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.

Pls share BUG table fields from query

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?

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)