Custom Report that Displays Test Sets, Test Cases, Test Steps and Linked Incidents/Attachments

Prerequisite 

To get the result of below ESQL query execution, the following conditions should met:
1. A list of test sets (at least one test set)
2. Test set should contain test cases (at least one test case)
3. That test case to have a test steps (at least one test step)
4. Each test step must have a linked attachment and incident (both)

Creating the Custom Report

The columns therefore we need to include are:

• Test Set Name (and ID)
• Test Case Name (and ID)
• Test Step #
• Description
• Expected Result
• Attachment ID (and filename)
• Incident ID (and Name)

The Entity SQL (ESQL) used for this report is:

select TX.TEST_SET_ID, TX.NAME as TEST_SET_NAME, TC.TEST_CASE_ID, TC.NAME as TEST_CASE_NAME, TS.DESCRIPTION, TS.EXPECTED_RESULT, AA.ATTACHMENT_ID, AT.FILENAME, INC.INCIDENT_ID, INC.NAME as INCIDENT_NAME
from SpiraTestEntities.R_TestSets as TX
join SpiraTestEntities.R_TestSetTestCases as TXC on TX.TEST_SET_ID = TXC.TEST_SET_ID
join SpiraTestEntities.R_TestCases as TC on TXC.TEST_CASE_ID = TC.TEST_CASE_ID
join SpiraTestEntities.R_TestSteps as TS on TC.TEST_CASE_ID = TS.TEST_CASE_ID
join SpiraTestEntities.R_ArtifactAttachments as AA on AA.ARTIFACT_ID = TS.TEST_STEP_ID 
join SpiraTestEntities.R_Attachments as AT on AA.ATTACHMENT_ID = AT.ATTACHMENT_ID
join SpiraTestEntities.R_ArtifactAssociations as AX on AX.SOURCE_ARTIFACT_ID = TS.TEST_STEP_ID
join SpiraTestEntities.R_Incidents as INC on AX.DEST_ARTIFACT_ID = INC.INCIDENT_ID
where TX.PROJECT_ID = ${ProjectId}
and AA.ARTIFACT_TYPE_ID = 7
and AX.SOURCE_ARTIFACT_TYPE_ID = 7
and AX.DEST_ARTIFACT_TYPE_ID = 3

Click on Create Default Template button or simply copy/paste following 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_SET_ID</th><th>TEST_SET_NAME</th><th>TEST_CASE_ID</th><th>TEST_CASE_NAME</th><th>DESCRIPTION</th><th>EXPECTED_RESULT</th><th>ATTACHMENT_ID</th><th>FILENAME</th><th>INCIDENT_ID</th><th>INCIDENT_NAME</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="TEST_SET_ID"/></td><td><xsl:value-of select="TEST_SET_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="EXPECTED_RESULT"/></td><td><xsl:value-of select="ATTACHMENT_ID"/></td><td><xsl:value-of select="FILENAME"/></td><td><xsl:value-of select="INCIDENT_ID"/></td><td><xsl:value-of select="INCIDENT_NAME"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

This will give the following result:

You can then choose to sort by any of the columns by adding a simple order by clause:

order by TX.NAME, TX.TEST_SET_ID