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:

TEST_SET_IDTEST_SET_NAMETEST_CASE_IDTEST_CASE_NAMEDESCRIPTIONEXPECTED_RESULTATTACHMENT_IDFILENAMEINCIDENT_IDINCIDENT_NAME
1Testing Cycle for Release 1.02Ability to create new bookUser enters books name and author, then clicks NextUser taken to next screen in wizard14Expected Result Screenshot.png  
2Testing Cycle for Release 1.12Ability to create new bookUser enters books name and author, then clicks NextUser taken to next screen in wizard14Expected Result Screenshot.png  
1Testing Cycle for Release 1.03Ability to edit existing bookCall     
1Testing Cycle for Release 1.03Ability to edit existing bookUser clicks link to view existing booksList of active books in system displayed    
1Testing Cycle for Release 1.03Ability to edit existing bookUser clicks on link to edit a specific bookUser taken to edit book details screen    
2Testing Cycle for Release 1.13Ability to edit existing bookCall     
2Testing Cycle for Release 1.13Ability to edit existing bookUser clicks link to view existing booksList of active books in system displayed    
2Testing Cycle for Release 1.13Ability to edit existing bookUser clicks on link to edit a specific bookUser taken to edit book details screen    
1Testing Cycle for Release 1.04Ability to create new authorUser clicks submit buttonConfirmation screen is displayed    
2Testing Cycle for Release 1.14Ability to create new authorUser clicks submit buttonConfirmation screen is displayed    

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