Creating a Requirements Traceability Report of Requirements Tests and Defects

Creating the Custom Report

The following Entity SQL (ESQL) should be used in the Spira custom report writer:

select
    'RQ' + cast (RQ.REQUIREMENT_ID as String) as REQUIREMENT_ID,
    RQ.NAME as REQUIREMENT_NAME,
    'TC' + cast (TC.TEST_CASE_ID as String) as TEST_CASE_ID,
    TC.NAME as TEST_CASE_NAME,
	'TR' + cast (TR.TEST_RUN_ID as String) as TEST_RUN_ID,
	TR.EXECUTION_STATUS_NAME,
	'IN' + cast (TRI.INCIDENT_ID as String) as INCIDENT_ID
from SpiraTestEntities.R_Requirements as RQ
	 join SpiraTestEntities.R_RequirementTestCases as RT on RQ.REQUIREMENT_ID = RT.REQUIREMENT_ID
     join SpiraTestEntities.R_TestCases as TC on RT.TEST_CASE_ID = TC.TEST_CASE_ID
	 join SpiraTestEntities.R_TestRuns as TR on TR.TEST_CASE_ID = TC.TEST_CASE_ID
	 join SpiraTestEntities.R_TestRunIncidents as TRI on TR.TEST_RUN_ID = TRI.TEST_RUN_ID
where RQ.PROJECT_ID = ${ProjectId}

Click on Create Default Template or simply copy and paste the following XSLT code:

<?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>REQUIREMENT_ID</th><th>REQUIREMENT_NAME</th><th>TEST_CASE_ID</th><th>TEST_CASE_NAME</th><th>TEST_RUN_ID</th><th>EXECUTION_STATUS_NAME</th><th>INCIDENT_ID</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="REQUIREMENT_ID"/></td><td><xsl:value-of select="REQUIREMENT_NAME"/></td><td><xsl:value-of select="TEST_CASE_ID"/></td><td><xsl:value-of select="TEST_CASE_NAME"/></td><td><xsl:value-of select="TEST_RUN_ID"/></td><td><xsl:value-of select="EXECUTION_STATUS_NAME"/></td><td><xsl:value-of select="INCIDENT_ID"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

Click Save twice, to be able to apply the changes.

That will generate the following output: