Creating new 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.

Actual Statuses

 The builds in Spira have following statuses:

Currently custom report table SpiraTestEntities.R_Builds has only the following columns:

BUILD_ID
BUILD_STATUS_ID
RELEASE_ID
PROJECT_ID
NAME
DESCRIPTION

As you can see there is no Build_Status_Name, so when you execute the standard query (by just choosing Builds from the pre-defined query list):

select value R from SpiraTestEntities.R_Builds as R where R.PROJECT_ID = ${ProjectId}

It usually displays something similar to 

To get the status name of the build in the report paste into the Query section information below:

select B.BUILD_ID,	
CASE
     WHEN (B.BUILD_STATUS_ID) = 1 THEN "Failed"
WHEN (B.BUILD_STATUS_ID) = 2 THEN "Succeeded"
WHEN (B.BUILD_STATUS_ID) = 3 THEN "Unstable"
WHEN (B.BUILD_STATUS_ID) = 4 THEN "Aborted"
ELSE "MON"
   END  
AS BUILD_STATUS, B.CREATION_DATE	
 from SpiraTestEntities.R_Builds as B where B.PROJECT_ID = ${ProjectId}

Feel free to modify the list of the columns, to include or not the required columns.

Do not forget to click on Create Default Template so to generate a template for the end users, or simply copy below given into the corresponding field:

<?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>BUILD_ID</th>
    <th>BUILD_STATUS</th>
    <th>CREATION_DATE</th>
    </tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="BUILD_ID"/>
        </td>
        <td>
        <xsl:value-of select="BUILD_STATUS"/>
        </td>
        <td class="Date">
        <xsl:call-template name="format-date">
        <xsl:with-param name="datetime" select="CREATION_DATE" />
        </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>

The final report, using above XSLT template will look like: