Creating the Custom Report

The following custom report can be used to generate a table of incidents (defects) along with the number of times each one has been reopened:

select
  HC.ARTIFACT_ID as INCIDENT_ID,
  HC.ARTIFACT_DESC as INCIDENT_NAME,
  count(HC.CHANGESET_ID) as REOPEN_COUNT
from SpiraTestEntities.R_HistoryChangeSets as HC
inner join SpiraTestEntities.R_HistoryDetails as HD on HC.CHANGESET_ID = HD.CHANGESET_ID
where
  HD.FIELD_NAME = 'IncidentStatusId' and
  HC.ARTIFACT_TYPE_ID = 3 and
  HD.NEW_VALUE = 'Reopen' and
  HC.PROJECT_ID = ${ProjectId}
group by HC.ARTIFACT_ID, HC.ARTIFACT_DESC
order by REOPEN_COUNT desc

Then the following report template can be used to display the table:

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

Executing the Report

When you run the report in a product that has several defects that have been reopened, you will see the following:

The list of defects is displayed, along with the reopen count, in descending order.

To get a graph of the number of defects in each reopen count interval (1,2,3,4) you could simply use an Excel graph, but using Spira's custom graphing feature, you can do it entirely within the application:

Creating the Graph

First, you need to create a Custom Graph :

  1. Go to Administration  > Edit Graphs
  2. Click Add New Custom Graph
  3. Specify that it applies to the Product.
  4. Insert the ESQL query into the Query section
(select 1 as ReopenCount, count (XZ.INCIDENT_ID) as IncidentCount from
(select
  HC.ARTIFACT_ID as INCIDENT_ID,
  count(HC.CHANGESET_ID) as REOPEN_COUNT
from SpiraTestEntities.R_HistoryChangeSets as HC
inner join SpiraTestEntities.R_HistoryDetails as HD on HC.CHANGESET_ID = HD.CHANGESET_ID
where
  HD.FIELD_NAME = 'IncidentStatusId' and
  HC.ARTIFACT_TYPE_ID = 3 and
  HD.NEW_VALUE = 'Reopen' and
  HC.PROJECT_ID = ${ProjectId}
group by HC.ARTIFACT_ID, HC.ARTIFACT_DESC) as XZ
where XZ.REOPEN_COUNT = 1)
union
(select 2 as ReopenCount, count (XZ.INCIDENT_ID) as IncidentCount from
(select
  HC.ARTIFACT_ID as INCIDENT_ID,
  count(HC.CHANGESET_ID) as REOPEN_COUNT
from SpiraTestEntities.R_HistoryChangeSets as HC
inner join SpiraTestEntities.R_HistoryDetails as HD on HC.CHANGESET_ID = HD.CHANGESET_ID
where
  HD.FIELD_NAME = 'IncidentStatusId' and
  HC.ARTIFACT_TYPE_ID = 3 and
  HD.NEW_VALUE = 'Reopen' and
  HC.PROJECT_ID = ${ProjectId}
group by HC.ARTIFACT_ID, HC.ARTIFACT_DESC) as XZ
where XZ.REOPEN_COUNT = 2)
union
(select 3 as ReopenCount, count (XZ.INCIDENT_ID) as IncidentCount from
(select
  HC.ARTIFACT_ID as INCIDENT_ID,
  count(HC.CHANGESET_ID) as REOPEN_COUNT
from SpiraTestEntities.R_HistoryChangeSets as HC
inner join SpiraTestEntities.R_HistoryDetails as HD on HC.CHANGESET_ID = HD.CHANGESET_ID
where
  HD.FIELD_NAME = 'IncidentStatusId' and
  HC.ARTIFACT_TYPE_ID = 3 and
  HD.NEW_VALUE = 'Reopen' and
  HC.PROJECT_ID = ${ProjectId}
group by HC.ARTIFACT_ID, HC.ARTIFACT_DESC) as XZ
where XZ.REOPEN_COUNT = 3)
union
(select 4 as ReopenCount, count (XZ.INCIDENT_ID) as IncidentCount from
(select
  HC.ARTIFACT_ID as INCIDENT_ID,
  count(HC.CHANGESET_ID) as REOPEN_COUNT
from SpiraTestEntities.R_HistoryChangeSets as HC
inner join SpiraTestEntities.R_HistoryDetails as HD on HC.CHANGESET_ID = HD.CHANGESET_ID
where
  HD.FIELD_NAME = 'IncidentStatusId' and
  HC.ARTIFACT_TYPE_ID = 3 and
  HD.NEW_VALUE = 'Reopen' and
  HC.PROJECT_ID = ${ProjectId}
group by HC.ARTIFACT_ID, HC.ARTIFACT_DESC) as XZ
where XZ.REOPEN_COUNT = 4)

When you preview the graph data, you will see:

Finally, you can now display the following bar graph of the number of incidents (defects) that have been opened one or more times:

You can also choose the donut chart visualization if that is preferable: