Creating a Summary Report of Test Cases and Defects

Overview

A customer was looking for a simple report that would display a list of test cases along with raised defects, in this format:

where the Due Date was being stored in a custom Spira date field (Custom 01).

The solution is to use the following ESQL in a custom report section:

select TC.TEST_CASE_ID, TC.COMPONENT_IDS, TC.NAME as TEST_CASE_NAME, TC.EXECUTION_STATUS_NAME,
        IC.INCIDENT_ID, IC.NAME as INCIDENT_NAME, IC.INCIDENT_STATUS_NAME,
        TC.OWNER_NAME, TC.EXECUTION_DATE, TC.CUST_01
from SpiraTestEntities.R_TestCases as TC
inner join SpiraTestEntities.R_TestCaseIncidents as TN on TC.TEST_CASE_ID = TN.TEST_CASE_ID
inner join SpiraTestEntities.R_Incidents as IC on TN.INCIDENT_ID = IC.INCIDENT_ID
where TC.PROJECT_ID = ${ProjectId} and TC.IS_DELETED = False

Then, either choose to create the 'Default Template' for this query, or use the following styled XSLT:

<?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>Test CaseId </th><th>Components</th><th>Test Case Name</th><th>Execution Status</th><th>Incident ID</th><th>Incident Name</th><th>Incident Status</th><th>Last Tester</th><th>Last Executed</th><th>Custom 1</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="TEST_CASE_ID"/></td><td><xsl:value-of select="COMPONENT_IDS"/></td><td><xsl:value-of select="TEST_CASE_NAME"/></td><td><xsl:value-of select="EXECUTION_STATUS_NAME"/></td><td><xsl:value-of select="INCIDENT_ID"/></td><td><xsl:value-of select="INCIDENT_NAME"/></td><td><xsl:value-of select="INCIDENT_STATUS_NAME"/></td><td><xsl:value-of select="OWNER_NAME"/></td><td><xsl:value-of select="EXECUTION_DATE"/></td><td><xsl:value-of select="CUST_01"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

When you run the report, you will get: