Scenario

Spira provides Test Sets to support those customers that continue to package test cases in a collection for testing for specific scenarios.  The test cases inside the test set can have different test case owners. As test sets emerge over time, looking at the total test cases owned by someone on tests not completed will be helpful. This article helps to graphically map this request.

SQL Query

select 
    Concat
     (Concat(Cast(R.TEST_SET_ID as Edm.String), " - ") , 
       case when 
        R.OWNER_NAME is null then "Unassigned" 
       else 
        R.OWNER_NAME end
      ) as TestSetOwner,
    COUNT(R.TEST_CASE_ID) as TC_OWNER_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, R.OWNER_NAME
order by 
    R.TEST_SET_ID, R.OWNER_NAME

Query Explanation

1. The Concat function only uses two arguments. Since, we need "Test Set ID" and "Test Case Owner" separated by "-" for readability, we need to use two Concat opertors.
2. The TEST_SET_ID is an integer and must be converted to string using the CAST operator.
3. The Test_Case owner may be unassigned to and so in such cases we need CASE/ELSE statement to mark test cases without a owner to "Unassigned" owner.
4. We bring the test set id from R_TestSetTestCases entity aggregating the number of test cases in this entity.
5. 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.
6. 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.
7. We apply the current project selection on the final result
8. 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 bar chart. The line graph also may be a good representation.