Report to identify number of days a defect is assigned to a status

Overview

This sample custom report displays a grid of the changes made to incidents in the current project, grouped by the incident ID and name. It will display for each incident:

• The date/time that each assignment change was made
• The name of the person it was assigned FROM
• The name of the person it was assigned TO
• The number of hours since the incident was created that the change was made (aging)
• The number of days since the incident was created that the change was made (aging)

Creating the Custom Report

The following Entity SQL (ESQL) should be used in the Spira custom report writer:

select
  HC.ARTIFACT_ID as INCIDENT_ID,
  HC.ARTIFACT_DESC as INCIDENT_NAME,
  HC.CHANGE_DATE as ASSIGNMENT_DATE,
  HD.OLD_VALUE as OLD_STATUS,
  HD.NEW_VALUE as NEW_STATUS,
  DiffHours(INC.CREATION_DATE, HC.CHANGE_DATE) as AGING_HOURS,
  DiffDays(INC.CREATION_DATE, HC.CHANGE_DATE) as AGING_DAYS
from SpiraTestEntities.R_HistoryChangeSets as HC
inner join SpiraTestEntities.R_HistoryDetails as HD on HC.CHANGESET_ID = HD.CHANGESET_ID
inner join SpiraTestEntities.R_Incidents as INC on HC.ARTIFACT_ID = INC.INCIDENT_ID
where
  HD.FIELD_NAME = 'IncidentStatusId' and
  HC.ARTIFACT_TYPE_ID = 3 and
  HC.PROJECT_ID = ${ProjectId}
order by
  HC.ARTIFACT_ID,
  HC.CHANGE_DATE

Then click the 'Create Default Template' option to generate the following XSLT report template:

<?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 #</th>
			<th>Name</th>
			<th>Change Date</th>
			<th>Old Status</th>
			<th>New Status</th>
			<th>Aging (hours)</th>
			<th>Aging (days)</th>
		</tr>
      <xsl:for-each select="ROW">
        <tr>
			<td><xsl:value-of select="INCIDENT_ID"/></td>
			<td><xsl:value-of select="INCIDENT_NAME"/></td>
			<td>
              <xsl:call-template name="format-date">
                <xsl:with-param name="datetime" select="ASSIGNMENT_DATE" />
              </xsl:call-template>
			</td>
			<td><xsl:value-of select="OLD_STATUS"/></td>
			<td><xsl:value-of select="NEW_STATUS"/></td>
			<td><xsl:value-of select="AGING_HOURS"/></td>
			<td><xsl:value-of select="AGING_DAYS"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
  <xsl:template name="format-date">
    <xsl:param name="datetime"/>
    <xsl:variable name="date" select="substring-before($datetime, 'T')" />
    <xsl:variable name="year" select="substring-before($date, '-')" />
    <xsl:variable name="month" select="substring-before(substring-after($date, '-'), '-')" />
    <xsl:variable name="day" select="substring-after(substring-after($date, '-'), '-')" />
    <xsl:variable name="time" select="substring-before(substring-after($datetime, 'T'), '.')" />
    <xsl:variable name="monthname">
      <xsl:choose>
        <xsl:when test="$month='01'">
          <xsl:value-of select="'Jan'"/>
        </xsl:when>
        <xsl:when test="$month='02'">
          <xsl:value-of select="'Feb'"/>
        </xsl:when>
        <xsl:when test="$month='03'">
          <xsl:value-of select="'Mar'"/>
        </xsl:when>
        <xsl:when test="$month='04'">
          <xsl:value-of select="'Apr'"/>
        </xsl:when>
        <xsl:when test="$month='05'">
          <xsl:value-of select="'May'"/>
        </xsl:when>
        <xsl:when test="$month='06'">
          <xsl:value-of select="'Jun'"/>
        </xsl:when>
        <xsl:when test="$month='07'">
          <xsl:value-of select="'Jul'"/>
        </xsl:when>
        <xsl:when test="$month='08'">
          <xsl:value-of select="'Aug'"/>
        </xsl:when>
        <xsl:when test="$month='09'">
          <xsl:value-of select="'Sep'"/>
        </xsl:when>
        <xsl:when test="$month='10'">
          <xsl:value-of select="'Oct'"/>
        </xsl:when>
        <xsl:when test="$month='11'">
          <xsl:value-of select="'Nov'"/>
        </xsl:when>
        <xsl:when test="$month='12'">
          <xsl:value-of select="'Dec'"/>
        </xsl:when>
        <xsl:otherwise>
          <xsl:value-of select="''" />
        </xsl:otherwise>
      </xsl:choose>
    </xsl:variable>
    <xsl:value-of select="concat($day, '-' ,$monthname, '-', $year , ' ', $time)" />
  </xsl:template>
</xsl:stylesheet>

Viewing the Report

If you run this report in Excel or HTML format, it will generate the following output:

or if you want to export to Excel: