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

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

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