Solution
First, you need to create new custom report, for that follow the steps:
- Go to Administration > Edit Reports
- Create a new Report, give it a meaningful name
- Specify that it should allow generation in MS-Word, Excel, HTML and PDF formats
- Choose to add a Custom Section:
![](/Support/Attachment/135225.aspx)
- Into the Custom Query section paste below ESQL query:
SELECT TRI.INCIDENT_ID, INC.NAME AS INCIDENT_NAME, TRS.TEST_CASE_ID, TRS.TEST_CASE_NAME,
TRS.DESCRIPTION, TRS.TEST_RUN_STEP_ID, TRS.EXECUTION_STATUS_NAME, TRS.START_DATE, TRS.END_DATE, TRS.EXPECTED_RESULT, TRS.ACTUAL_RESULT,
TRS.SAMPLE_DATA
FROM SpiraTestEntities.R_TESTRUNINCIDENTS AS TRI
INNER JOIN SpiraTestEntities.R_TESTRUNSTEPS AS TRS ON TRI.TEST_RUN_ID=TRS.TEST_RUN_ID
JOIN SpiraTestEntities.R_INCIDENTS AS INC ON TRI.INCIDENT_ID=INC.INCIDENT_ID
WHERE TRS.PROJECT_ID = ${ProjectId}
Please note that you can filter also by Test execution status - it can be failed, passed, caution, N/A
In that case there will be an additional filtering criteria in WHERE statement:
AND TRS.EXECUTION_STATUS_NAME = 'FAILED'
6. Click on Create Default Template or simply copy and paste below 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>INCIDENT_ID</th><th>INCIDENT_NAME</th><th>TEST_CASE_ID</th><th>TEST_CASE_NAME</th><th>DESCRIPTION</th><th>TEST_RUN_STEP_ID</th><th>EXECUTION_STATUS_NAME</th><th>START_DATE</th><th>END_DATE</th><th>EXPECTED_RESULT</th><th>ACTUAL_RESULT</th><th>SAMPLE_DATA</th></tr>
<xsl:for-each select="ROW">
<tr><td><xsl:value-of select="INCIDENT_ID"/></td><td><xsl:value-of select="INCIDENT_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="DESCRIPTION"/></td><td><xsl:value-of select="TEST_RUN_STEP_ID"/></td><td><xsl:value-of select="EXECUTION_STATUS_NAME"/></td><td><xsl:value-of select="START_DATE"/></td><td><xsl:value-of select="END_DATE"/></td><td><xsl:value-of select="EXPECTED_RESULT"/></td><td><xsl:value-of select="ACTUAL_RESULT"/></td><td><xsl:value-of select="SAMPLE_DATA"/></td>
</tr>
</xsl:for-each>
</table>
</xsl:template>
</xsl:stylesheet>
7. Save the report and now it is ready to be ran.
Sample output
Here is the sample output after running the report:
![](/Support/Attachment/149971.aspx)