Scenario

Spira provides Test Sets to support those customers that continue to package test cases in a collection for testing for specific scenarios. When test sets are  not marked completed, then how can we graphically list the number of test cases in a test set?

SQL Query

select 
    R.TEST_SET_ID, 
    COUNT(R.TEST_CASE_ID) as TC_COUNT
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 and 
   TS.TEST_SET_STATUS_NAME <> 'Completed'
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} 
group by 
  R.TEST_SET_ID
order by 
  R.TEST_SET_ID

Query Explanation

  1. We bring the test set id from R_TestSetTestCases entity aggregating the number of test cases in this entity.
  2. Since we are reporting on test sets that are not marked completed, we connect with the R_TestSets entity joining on test_set_id and project_id while suppressing deleted test_sets and selecting any test set not in the completed state.
  3. Since test cases can be created but marked deleted, we connect with the R_TestCases entity joining on test_case_id and project_id while suppressing the deleted test_cases.
  4. We apply the current project selection on the final result
  5. We apply the group by (required for aggregation) and the ordering of the result set by test_set_id for convenience.

Output

Given below is the graphical output in the donut chart along with the result set that connects with the donut chart. 

Test Cases in a Test Set in not "Completed" status