Custom Report of Test Sets, Test Cases, Test Runs by Folder and Release

Desired Format

The customer was looking for a report that looked like the following:

Spira Custom Report

Using the Spira custom reporting features, we created the following ESQL query that retrieved the test sets by test folder and release, and included using JOINs the test case instances and test runs. We made the test set folder JOIN and test run JOIN both outer joins so that test sets that were not in a folder and test cases not yet executed were included.

select
TX.RELEASE_VERSION_NUMBER as RELEASE_NAME,
TXF.NAME as TEST_SET_FOLDER_NAME,
TX.NAME as TEST_SET_NAME,
TX.CREATOR_NAME,
TX.PLANNED_DATE,
TXC.POSITION as TEST_CASE_POSITION,
TC.NAME as TEST_CASE_NAME,
TR.EXECUTION_STATUS_NAME,
TR.TESTER_NAME,
TR.END_DATE as EXECUTION_DATE
from SpiraTestEntities.R_TestSets as TX
left join SpiraTestEntities.R_TestSetFolders as TXF on TX.TEST_SET_FOLDER_ID = TXF.TEST_SET_FOLDER_ID 
join SpiraTestEntities.R_TestSetTestCases as TXC on TX.TEST_SET_ID = TXC.TEST_SET_ID
join SpiraTestEntities.R_TestCases as TC on TXC.TEST_CASE_ID = TC.TEST_CASE_ID
left join SpiraTestEntities.R_TestRuns as TR on TXC.TEST_SET_TEST_CASE_ID = TR.TEST_SET_TEST_CASE_ID 
where TX.IS_DELETED = False and TC.IS_DELETED = False

When you preview the data, it looks like the following:

When you click the button to generate the default XSLT template, you get a table that displays this data. We modified this slightly to make the heading names mixed-case and added better date formatting. The resulting XSLT template to use is:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" version="1.0" exclude-result-prefixes="msxsl">
   <xsl:template match="/RESULTS">
      <table class="DataGrid">
         <tr>
            <th>Release Name</th>
            <th>Test Set Folder Name</th>
            <th>Test Set Name</th>
            <th>Test Set Creator</th>
            <th>Test Set Planned Date</th>
            <th>Test Instance Order (Sequencing)</th>
            <th>Test Instance Name</th>
            <th>Test Run - Execution Status</th>
            <th>Test Run - Tester</th>
            <th>Test Run - Execution Date</th>
         </tr>
         <xsl:for-each select="ROW">
            <tr>
               <td>
                  <xsl:value-of select="RELEASE_NAME" />
               </td>
               <td>
                  <xsl:value-of select="TEST_SET_FOLDER_NAME" />
               </td>
               <td>
                  <xsl:value-of select="TEST_SET_NAME" />
               </td>
               <td>
                  <xsl:value-of select="CREATOR_NAME" />
               </td>
                <td class="Date">
                  <xsl:call-template name="format-date">
                    <xsl:with-param name="datetime" select="PLANNED_DATE" />
                  </xsl:call-template>
                </td>
               <td>
                  <xsl:value-of select="TEST_CASE_POSITION" />
               </td>
               <td>
                  <xsl:value-of select="TEST_CASE_NAME" />
               </td>
               <td>
                  <xsl:value-of select="EXECUTION_STATUS_NAME" />
               </td>
               <td>
                  <xsl:value-of select="TESTER_NAME" />
               </td>
                <td class="Date">
                  <xsl:call-template name="format-date">
                    <xsl:with-param name="datetime" select="EXECUTION_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>

This was then saved as a new custom report.

Sample Report Output

When you run this custom report inside Spira with the sample "Library Information System" project, you will get the following output (in this case from Excel):