Creating a Report of Incidents' Reopen Count

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:

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.