Custom Incident Reopen Count Report

If you use the Spira custom reporting engine, the following ESQL will create a list of open incidents in the current project with the number reopen activities (from the history) displayed in a column to the right:

select INC.INCIDENT_ID, INC.NAME, count (HIS.ARTIFACT_HISTORY_ID) as REOPEN_COUNT
from SpiraTestEntities.R_Incidents as INC join SpiraTestEntities.R_HistoryDetails as HIS
on INC.INCIDENT_ID = HIS.ARTIFACT_ID
where INC.PROJECT_ID = ${ProjectId}
and INC.IS_DELETED = False
and HIS.ARTIFACT_TYPE_ID = 3
and INC.INCIDENT_STATUS_IS_OPEN_STATUS = True
and HIS.FIELD_NAME = 'IncidentStatusId'
and (HIS.NEW_VALUE = 'Open' or HIS.NEW_VALUE = 'Reopen' or HIS.NEW_VALUE = 'Assigned')
group by INC.INCIDENT_ID, INC.NAME
order by REOPEN_COUNT desc

Note that this report uses the Names of the open statuses that you want to count as reopened:

  • Open
  • Reopen
  • Assigned

You may want to change these to match your project.

When you use the Create Default Template option, Spira will create the following table for you:

<?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>INCIDENT_ID</th><th>NAME</th><th>REOPEN_COUNT</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="INCIDENT_ID"/></td><td><xsl:value-of select="NAME"/></td><td><xsl:value-of select="REOPEN_COUNT"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

When you generate the report, you will get the following data:

INCIDENT_IDNAMEREOPEN_COUNT
7Cannot add a new book to the system2
63Error on the screen - db timeout1
64I cannot login to the system1
65page missing on author creation screen1
70Issue on the book creation screen1
73Failed with missing page1
75Bug on the book screen1
76Bug on the book page 1231
78error found on page XYZ1
79Issue found doing XYZ1
 

You can change the report to display data for all projects, a single program, a specific release, or group of releases by adjusting the WHERE clause part.