Create a report of the number of Incidents and Requirements in past releases

Overview

A customer was looking for a simple report that would display a list of Incidents and Requirements, that belong to Releases that:

1. have an end date in the past
2. are not deleted
3. do not have a status 'Canceled'

It will display for each Release:

• The date that each Release was ended
• The name and  type of the Release (Major/Minor/Sprint)
• The number of Incidents and Requirements assigned to each Release

Solution

The solution is to use the following ESQL in a custom report section.

Creating the Custom Report

This article assumes you are familiar with the basics of writing custom reports in Spira.
In this example we will be using a custom report with a custom SQL section.

To create the report you need to:

1. Go to Administration  > Edit Reports
2. Create a new Report
3. Specify that it should allow generation in MS-Word, Excel, HTML and PDF formats
4. Choose to add a Custom Section:
   
5. Then click [Save] twice and you can run your report.

Step1.
Paste into the Query section information below:

SELECT RL.NAME AS Name, RL.RELEASE_TYPE_NAME AS ReleaseType, 
RL.REQUIREMENT_COUNT AS No_of_RQ,
COUNT(INC.INCIDENT_ID) AS No_of_INC,
RL.END_DATE AS EndDate
FROM SpiraTestEntities.R_Releases AS RL
FULL JOIN SpiraTestEntities.R_Incidents AS INC ON RL.RELEASE_ID = INC.RESOLVED_RELEASE_ID
WHERE 
DiffDays(CurrentDateTime(), RL.END_DATE) < 0 AND RL.IS_DELETED = false AND RL.RELEASE_STATUS_ID != 6 AND RL.PROJECT_ID = ${ProjectId}
GROUP BY RL.NAME, RL.RELEASE_TYPE_NAME, RL.REQUIREMENT_COUNT, RL.END_DATE
ORDER BY RL.END_DATE asc

Step 2.

Copy the template into that section from below:

<?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>Name</th>
    <th>ReleaseType</th>
    <th>No_of_RQ</th>
    <th>No_of_INC</th>
    <th>EndDate</th>
    </tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="Name"/></td>
        <td><xsl:value-of select="ReleaseType"/></td>
        <td><xsl:value-of select="No_of_RQ"/></td>
        <td><xsl:value-of select="No_of_INC"/></td>

        <td>

        <xsl:call-template name="format-date">
        <xsl:with-param name="datetime" select="EndDate"/>
         </xsl:call-template>


        </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>

Once that is done, you are ready to run your report.

Viewing the Report

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