How to retrieve the test sets - test instances - results within particular folder/path in Quality Center

Many times , you would be running the tests in multiple test sets and with multiple instances. Those multiple test sets would be in different folders under and under different hierarchy

Quality center has option to get this information using Analysis graphs, and thus you would be able to find what is the progress of each test set. But is has its own limitations. Like, if you want to find which test in each of sets is failed, or who has been the responsible tester for those tests , or any more information, then we are handicapped using Analysis reports.

Well , if you learn the excel reports, you life would be made easy. I have briefly mentioned on How to access excel reports in one of previous blog

So how shall we do go ahead?

Let me explain how the test sets, test lab folders, test cases are arranged. Below diagram from Quality Help file shows the Entity relationship diagram

The TESTCYCL table is the one that stores test set details. Hence it has information on instances of design tests within test set. If you not aware of instance, Instance is use of a test in a test set. If the test set contains a test more than once , then it will have same numbers of instances in the test set. This value is stored in column TC_TEST_INSTANCE column of TESTCYCL table.

The TEST table contains all the tests that are defined in test plan. The main and bit complex folder is the one that stores the path. It is CYCLE table, where it contain entry for each folder that is created in tree view. But how is the tree structure of quality center folder stored?Each row in this table corresponds to folder and each of them have a Father ID indicating ID of another folder under which it falls.

Suppose we have folder structure like
Folder1/Folder2/Folder3/Folder4
Then  for folder1
ID = 1 , Father ID = 0
For folder 2
ID = 2, Father ID = ID of folder1 = 1
For folder 3
ID = 3, Father ID = ID of folder2 = 2
etc.

Hence the table has to be self joined to retrieve exact test cases/sets within a folder.

From the diagram, we can easily write down the following join conditions
TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
TEST.TS_TEST_ID = TESTCYCL.TC_TEST_ID


Now say you want to retrieve all the test sets/cases within Folder2 till the level of folder 4. Here you have 4 levels of folder. Assume for lowest level Folder4 as representing z, then folder3 as y, then folder2 as x,  then folder1 as w


Then we have write the self joins as below


and CYCLE.CY_FOLDER_ID = z.CF_item_id
and z.cf_father_id = y.CF_item_id
and y.cf_father_id = x.CF_item_id
and x.cf_father_id = w.CF_item_id
and x.cf_item_name = 'Folder2'
and w.cf_item_name = 'Folder1'


The final query would be 


select Test.TS_NAME as TestCase, TESTCYCL.TC_STATUS as Status,CYCLE.CY_CYCLE as TestSet, TESTCYCL.TC_ACTUAL_TESTER as Tester
from CYCLE,TEST,TESTCYCL,CYCL_FOLD z ,CYCL_FOLD y,
CYCL_FOLD x,CYCL_FOLD w
where TEST.TS_TEST_ID = TESTCYCL.TC_TEST_ID
and TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
and CYCLE.CY_FOLDER_ID = z.CF_item_id
and z.cf_father_id = y.CF_item_id
and y.cf_father_id = x.CF_item_id
and x.cf_father_id = w.CF_item_id
and x.cf_item_name = 'Folder2'
and w.cf_item_name = 'Folder1'