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:

REQUIREMENT_IDREQUIREMENT_NAMETEST_CASE_IDTEST_CASE_NAMETEST_RUN_IDEXECUTION_STATUS_NAMEINCIDENT_ID
RQ4Ability to add new books to the systemTC2Ability to create new bookTR38Not RunIN21
RQ4Ability to add new books to the systemTC2Ability to create new bookTR38Not RunIN29
RQ4Ability to add new books to the systemTC2Ability to create new bookTR38Not RunIN32
RQ4Ability to add new books to the systemTC2Ability to create new bookTR1FailedIN7
RQ4Ability to add new books to the systemTC2Ability to create new bookTR61FailedIN66
RQ4Ability to add new books to the systemTC2Ability to create new bookTR77FailedIN67
RQ4Ability to add new books to the systemTC2Ability to create new bookTR276FailedIN77
RQ4Ability to add new books to the systemTC44Sample Test for ZFTR90FailedIN70
RQ7Ability to associate books with different subjectsTC4Ability to create new authorTR4FailedIN2
RQ7Ability to associate books with different subjectsTC4Ability to create new authorTR28FailedIN62