Background

Given you have added a new custom status for Incidents so to use the transition step when needed - if the bug has been fixed then moved to this status.
Considering the bug is assigned to the test case it needs to be re-tested after the fix.

This report should help to get the list of such test cases very quickly.

 

Solution

First we need to make sure that the specific status already exist and verify the ID of that status.
For that you need to do the following:

1. Go to Admin menu -> Incidents -> Status
2. Find the status that is supposed to be an identifier for such bugs/incidents
3. In the ID column you should see its id (e.g. IS41) - that will be the value to use later in the ESQL query
For this example we created a status called 'Ready to test' and its ID is 41

Once you have an ID of the status to filter by, you will need to create a new report:

  1. Go to Administration  > Edit Reports
  2. Create a new Report, give it a meaningful name
  3. Specify that it should allow generation in MS-Word, Excel, HTML and PDF formats
  4. Choose to add a Custom Section:

  5. Into the Custom Query section paste the below script: 
SELECT
TCI.TEST_CASE_ID,	
TC.NAME AS TESTCASE_NAME,
TCI.INCIDENT_ID,
INC.NAME AS INCIDENT_NAME,
INC.INCIDENT_STATUS_NAME
FROM SpiraTestEntities.R_TestCaseIncidents as TCI
INNER JOIN SpiraTestEntities.R_Incidents AS INC ON TCI.INCIDENT_ID = INC.INCIDENT_ID
INNER JOIN SpiraTestEntities.R_TestCases AS TC ON TCI.TEST_CASE_ID = TC.TEST_CASE_ID
WHERE INC.INCIDENT_STATUS_ID = 41 AND TC.PROJECT_ID = ${ProjectId}

6. Click on Create Default Template or simply copy and paste below XSLT:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">
  <xsl:template match="/RESULTS">
    <table class="DataGrid"><tr><th>TEST_CASE_ID</th><th>TESTCASE_NAME</th><th>INCIDENT_ID</th><th>INCIDENT_NAME</th><th>INCIDENT_STATUS_NAME</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="TEST_CASE_ID"/></td><td><xsl:value-of select="TESTCASE_NAME"/></td><td><xsl:value-of select="INCIDENT_ID"/></td><td><xsl:value-of select="INCIDENT_NAME"/></td><td><xsl:value-of select="INCIDENT_STATUS_NAME"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

Save the report and now it is ready to be ran.

Sample output should provide the list of the test cases (for this particular ProjectID) that matching criteria: test case that have an incident (bug) assigned with the status 'Ready to Test" so these test cases now can be re-tested: