Context Overview

In the Agile context, depending on the size of the feature or user story in the backlog, the agile delivery team can associate existing test cases and create new test cases. Such an approach is part of the backlog refinement process to facilitate efficient sprint planning sessions. However, these user stories are not part of the sprint until they are assigned to the sprint as part of the sprint planning. Therefore, the list of new test cases created based on sprint level commitments is desired to ensure that the acceptance criteria meets the sprint goals.

In the context of Spira, features or user stories are in the requirement artifact. The release has the start date and end date indicating the timebox of the sprint. The test cases are traceable to the requirements mapped to the sprint and have a creation date. An important criteria to identify the test cases created after the sprint has began is an accurate anchor - release "START DATE". These details can be used to create the query for this custom report. 

SQL Query

select 
  RQ.REQUIREMENT_ID, 
  RQ.NAME as REQUIREMENT_NAME, 
  TC.TEST_CASE_ID, 
  TC.NAME as TEST_CASE_NAME, 
  RL.RELEASE_ID, 
  RL.NAME as RELEASE_NAME, 
  TC.CREATION_DATE, 
  RL.START_DATE 
from 
  SpiraTestEntities.R_Requirements as RQ 
join SpiraTestEntities.R_RequirementTestCases as RTC on 
  RTC.REQUIREMENT_ID = RQ.REQUIREMENT_ID
join SpiraTestEntities.R_Releases as RL on 
  RL.RELEASE_ID = RQ.RELEASE_ID and RL.IS_DELETED = False
join SpiraTestEntities.R_TestCases as TC on 
  TC.TEST_CASE_ID = RTC.TEST_CASE_ID and TC.IS_DELETED = False
where 
  RQ.PROJECT_ID = ${ProjectId} and 
  RQ.IS_DELETED = False and 
  TC.CREATION_DATE > RL.START_DATE
order by 
  RQ.REQUIREMENT_ID, 
  TC.TEST_CASE_ID

Query Explanation

  1. The artifact id and their names are brought in the SELECT list.
  2. Since all artifacts are called "NAME", the qualifier "AS" is required to differentiate names of requirements, test cases, and releases.
  3. The R_RequirementTestCases entity is joined with Requirements and Test Cases to get additional fields and connect with Releases table
  4. The anchor condition is the Test Case creation date greater than Release start date (TC.CREATION_DATE > RL.START_DATE) 

Results

Given below is a screenshot of how this would appear.

New TC on RQ

Additional Thoughts

Sometimes, it is possible that you create the release in advance as a placeholder before the sprint starts. In such cases, the START_DATE must be accurately set when the sprint starts.

In the SQL, the release type = sprint is not set as this query can be used even in non-agile settings. If the release type is specifically set to Sprint and other release types are to be excluded, then, the RL.RELEASE_TYPE = "Sprint" must be added.

In the report, the date displays DateTimestamp. If the time stamp in the display setting needs to be modified, then, the XSLT can be modified.