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_ID | TEST_SET_NAME | TEST_CASE_ID | TEST_CASE_NAME | DESCRIPTION | EXPECTED_RESULT | ATTACHMENT_ID | FILENAME | INCIDENT_ID | INCIDENT_NAME |
---|
1 | Testing Cycle for Release 1.0 | 2 | Ability to create new book | User enters books name and author, then clicks Next | User taken to next screen in wizard | 14 | Expected Result Screenshot.png | | |
2 | Testing Cycle for Release 1.1 | 2 | Ability to create new book | User enters books name and author, then clicks Next | User taken to next screen in wizard | 14 | Expected Result Screenshot.png | | |
1 | Testing Cycle for Release 1.0 | 3 | Ability to edit existing book | Call | | | | | |
1 | Testing Cycle for Release 1.0 | 3 | Ability to edit existing book | User clicks link to view existing books | List of active books in system displayed | | | | |
1 | Testing Cycle for Release 1.0 | 3 | Ability to edit existing book | User clicks on link to edit a specific book | User taken to edit book details screen | | | | |
2 | Testing Cycle for Release 1.1 | 3 | Ability to edit existing book | Call | | | | | |
2 | Testing Cycle for Release 1.1 | 3 | Ability to edit existing book | User clicks link to view existing books | List of active books in system displayed | | | | |
2 | Testing Cycle for Release 1.1 | 3 | Ability to edit existing book | User clicks on link to edit a specific book | User taken to edit book details screen | | | | |
1 | Testing Cycle for Release 1.0 | 4 | Ability to create new author | User clicks submit button | Confirmation screen is displayed | | | | |
2 | Testing Cycle for Release 1.1 | 4 | Ability to create new author | User clicks submit button | Confirmation 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