Overview

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

Test case ID

Component

Test case Name

Execution status

Defect associated to Test case

Defect status

Last tester

Last executed

Due date (Cust01 on test case)

         

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:

Test CaseIdComponentsTest Case NameExecution StatusIncident IDIncident NameIncident StatusLast TesterLast ExecutedCustom 1
21Ability to create new bookPassed7Cannot add a new book to the systemAssignedFred Bloggs2019-05-13T13:13:352019-05-13T13:13:35
41Ability to create new authorFailed2Not able to add new authorNewJoe P Smith2019-05-13T10:22:35