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:
- Go to Administration > Edit Reports
- Create a new Report
- Specify that it should allow generation in MS-Word, Excel, HTML and PDF formats
- Choose to add a Custom Section:
- 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: