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:

  1. Go to Administration  > Edit Reports
  2. Click on Create a new Report (at the bottom of the list of existing Reports)
  3. Specify that it should allow generation in MS-Word, Excel, HTML and PDF formats
  4. Choose to add a Custom Section:

SQL Query:

5. Paste into the Query section the query itself and then Save it:

SELECT 
  Combined_Date,
    SUM(CASE WHEN TC.EXECUTION_STATUS_NAME = 'Passed' THEN 1 ELSE 0 END) AS Total_Passed,
    SUM(CASE WHEN TC.EXECUTION_STATUS_NAME = 'Passed' THEN 1 ELSE 0 END) + 
    SUM(CASE WHEN TC.EXECUTION_STATUS_NAME = 'Failed' THEN 1 ELSE 0 END) AS Total_Executed
FROM 
    SpiraTestEntities.R_TestRuns AS TR
INNER JOIN 
    SpiraTestEntities.R_TestCases AS TC 
    ON TR.END_DATE = TC.EXECUTION_DATE
WHERE 
    TR.PROJECT_ID = ${ProjectId}
GROUP BY 
    TruncateTime(TR.END_DATE) AS Combined_Date

Query Explanation:

SELECT statement summarizes all the test runs, that have status 'Passed' and the test runs that were executed.

INNER JOIN statement checks the execution date to be the same as end date for test case execution.

Running the query

To run the query and see the results in the table view - click on Preview Results button. The sample output of that action looks like this:

The current output is the summary number of the executions and total passed tests, that is not the same as we would like to achieve.

If you click on Generate Default Template, then the output report will display exactly the same data. Now, lets modify the XSLT code so to summarize the rows as desired: last row should display 3 + the rest of the total executed tests = 16, the previous 13, and so on.

What we need to modify is:
1. The date-time to be displayed in friendly format
2. The calculation of the last row to know the number of executed runs for each day in total

Copy and paste the XSLT template that is ready to use, instead of the default 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>Execution Date for Test Set</th>
        <th>Passed Test Case</th>
        <th>Total Executed</th>
        <th>Running Total Passed</th> </tr>
      <xsl:for-each select="ROW">
        <tr>
          <td class="Date">
          <xsl:call-template name="format-date">
          <xsl:with-param name="datetime" select="Combined_Date"/>
          </xsl:call-template>
          </td>
          <td><xsl:value-of select="Total_Passed"/></td>
          <td><xsl:value-of select="Total_Executed"/></td>
          <td>
            <xsl:variable name="currentPassed" select="Total_Passed"/>
            <xsl:variable name="previousPassedSum" select="sum(preceding-sibling::ROW/Total_Passed)"/>
            <xsl:value-of select="$currentPassed + $previousPassedSum"/>
          </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>

Row 12-16 modifies the date format, row 20-22 adds a new column called Running Total Passed so to calculate the total passed test for the specific date.

Running this version gives the following output: