Create a report:
This article assumes you are familiar with the basics of writing custom reports in Spira.
In this example we will build a custom report with custom ESQL section.
To create the report you need to:
- Go to Administration > Edit Reports
- Click on Create a new Report (at the bottom of the list of existing Reports)
- Specify that it should allow generation in HTML and Excel formats.
- Choose to add a Custom Section:
SQL Query:
1. Insert the ESQL query below:
select TE.USER_NAME, TE.PROJECT_ID, TE.PROJECT_NAME, TE.EFFORT_DATE, TE.ARTIFACT_TYPE_NAME, TE.ARTIFACT_ID, TE.ARTIFACT_NAME, TE.EFFORT_MINUTES
from SpiraTestEntities.R_TimesheetEntries as TE
where TE.EFFORT_DATE >= DATETIME'2024-11-01 00:00:00' and TE.EFFORT_DATE <= DATETIME'2024-11-30 23:59:59'
order by TE.EFFORT_DATE asc
The output of this will be the content of the R_TimesheetEntries custom report view. We have hard-coded in some sample dates for the date-range to illustrate how you can filter the data returned. You might want to make it dynamic and use the current week (for example).
XSLT Template:
2. Insert the provided XSLT template 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>User</th><th>Project ID</th><th>Project Name</th><th>Time Entry Date</th><th>Artifact Type</th><th>Artifact ID</th><th>Artifact Name</th><th>Time Reported</th></tr>
<xsl:for-each select="ROW">
<tr>
<td><xsl:value-of select="USER_NAME"/></td>
<td>PR:<xsl:value-of select="PROJECT_ID"/></td>
<td><xsl:value-of select="PROJECT_NAME"/></td>
<td class="Date">
<xsl:call-template name="format-date">
<xsl:with-param name="datetime" select="EFFORT_DATE" />
</xsl:call-template>
</td>
<td><xsl:value-of select="ARTIFACT_TYPE_NAME"/></td>
<td><xsl:value-of select="ARTIFACT_ID"/></td>
<td><xsl:value-of select="ARTIFACT_NAME"/></td>
<td>
<xsl:value-of select="EFFORT_MINUTES div 60"/>:<xsl:value-of select="format-number(EFFORT_MINUTES mod 60, '00')"/>
</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>
Running the report and expected output
As a result of the report execution in HTML format, you should get something like:
and in Excel format:
