Scenario

Currently, the test set can be assigned with different test owners. Shown below is a specific test set (TX:40) with two test cases (TC2, TC4). The test set owner is different from the test case owners. 

Test Set with Test Cases having different owners

SQL Query

Given below is the SQL Query that needs to be added to the custom query segment in a new report.

select 
 R.TEST_SET_ID, 
 R.TEST_SET_NAME, 
 TS.OWNER_NAME as TestSetOwner,
 R.TEST_CASE_ID, 
 R.TEST_CASE_NAME, 
 R.OWNER_NAME as TestCaseOwner, 
 TC.EXECUTION_STATUS_NAME as TestCaseExecutionStatus 
from 
 SpiraTestEntities.R_TestSetTestCases as R 
join 
 SpiraTestEntities.R_TestSets as TS on 
 R.TEST_SET_ID = TS.TEST_SET_ID and 
 TS.IS_DELETED = False and 
 R.PROJECT_ID = TS.PROJECT_ID
join 
 SpiraTestEntities.R_TestCases as TC on 
 R.TEST_CASE_ID = TC.TEST_CASE_ID and 
 TC.IS_DELETED = False and 
 R.PROJECT_ID = TC.PROJECT_ID
where 
 R.PROJECT_ID = ${ProjectId}
order by
 R.TEST_SET_ID, R.TEST_CASE_ID

 

SQL Query Explanation

  1. Since the test set has a set of test cases, we start with the SpiraTestEntities.R_TestSetTestCases entity.
  2. While this entity has all the relevant information of the test cases and test sets, we need to join with their individual tables to remove any deleted test sets or test cases.
  3. The join with the SpiraTestEntities.R_TestSets matches with the TEST_SET_ID and PROJECT_ID along with IS_DELETED = False.
  4. We bring the TEST_SET_OWNER from the SpiraTestEntities.R_TestSets entity.
  5. The join with the SpiraTestEntities.R_TestCases matches with the TEST_CASE_ID and PROJECT_ID along with the IS_DELETED = False.
  6. We bring the Test Case EXECUTION_STATUS_NAME from the SpiraTestEntities.R_TestCases entity.
  7. Finally the WHERE clauses applies the current project_id selected.
  8. The order by clause sorts the results by TEST_SET_ID first and TEST_CASE_ID within that test set collection.

The Output

Given below is the example of the report output. Highlighted in red box is the specific information aligned with the example scenario (TX:40) with two test cases (TC2, TC4). It identifies the test set owner along with the respective test case owner and current test case execution status.

 Report showing Test Set Owner with Test Case Owners and Test Case Execution Status