Overview

When test cases are created, a tester can estimate how long the test may take to execute. This is the estimated duration. When the test case is executed and finished, the time taken to complete the test case execution. This is the actual duration. In the Test Case artifact of Spira, both these fields exist.

Good Practice - Test Case Execution Considerations

  • When test cases are written, as part of the test case workflow, it is important to understand when this estimated hours need to be filled. If it is done too early such as the draft or prior to approval, it is possible that someone fills this information with a random arbitrary number. In subsequent stages, since data exists, this data may not be updated as part of the subsequent workflow steps. It is recommended that at some important state, such as Approved or Ready For Test, the workflow requires the estimated duration to be a required field.
  • The actual duration is filled when all the test case is finished. If the test case is in a different state like blocked or caution waiting for resolution, the actual duration is not yet completed available. So, don't look for actual duration for partially completed test case where the test run is not marked finished.
  • The actual duration is automatically computed based on how long it took to execute. So, if there is an excessive amount of pause, then, additional thoughts are required to capture that amount of time to be excluded. This could be done by using a custom property to track the time to be excluded in the test run and this information should be updated manually.
  • It is important that we have data for the estimated duration and actual duration for this graph to be relevant.

SQL Query

Given below is the SQL Query to develop this graph.

select 
   case 
       when R.OWNER_NAME is not null  then R.OWNER_NAME 
       else "Unassigned" 
    end as OWNER,
  sum(R.ESTIMATED_DURATION) as PENDING, 
  sum(R.ACTUAL_DURATION) as DONE
from 
   SpiraTestEntities.R_TestCases as R 
where 
  R.PROJECT_ID = ${ProjectId} and 
  R.IS_DELETED = False and
  R.EXECUTION_STATUS_NAME NOT IN {"Not Run"} 
group by 
  R.OWNER_NAME

Query Explanation

  • In the CASE WHEN statement, we are evaluating if any test case currently has an unassigned owner. If it is the case, we are calling that owner as "Unassigned"
  • We are summing the estimated hours and calling the aggregate field as PENDING.
  • We are summing the actual hours and calling the aggregate field as DONE. Note that by definition, this is only capturing the actual hours for the last test run.
  • The where clause applies the current project selection ${ProjectId}, eliminates delete cases from consideration, and eliminates test cases not in executed. Note that if you have other test case status to be excluded based on your definition, please include them here.

Data Output

Given below is an example of the data output.

Estimated to Actual Hours in Test Case by Owner

Graph Output

Given below is a graphical representation of the above data.

Estimated to Actual Hours for Test Case by Test Case Owner

Good Practice - Interpretation Considerations

  • When interpreting the data, please note that the "Unassigned" category may have hours based on work already done by previously assigned owner. So, don't interpret this to mean that the test case was executed by someone.  This state only means that the current test case ownership is unknown.
  • If you would like to focus only certain types of test cases (like priority test cases,  test case types, any custom property flags, etc.) as required by your business process, include them in the where clause.
  • If your test case workflows eliminates certain workflows besides not run, include them in the where clause as noted.