The desired report we'd like to have looks something like:
Inside SpiraTeam or SpiraTest, go to Administration > System > Edit Reports.
Now do the following:
- Create a new custom report
- Give it a name and description
- You can leave the header and footer blank for now
- Enable the various formats that you want to be made available (typically include HTML and Excel for this kind of tabular report)
- Set the category as "Test Case Reports"
- Add a custom section. Give the section a name (the description, header, footer can be left blank).
- Paste the ESQL code displayed below into the Query field
- Click 'Preview Results' to display the results of the query
- Click 'Create Default Template' to generate a simple tabular grid to display the data.
- Now save the section
- Now save the report
The Query to Use
The following Entity SQL (ESQL) should be used:
select TC.TEST_CASE_ID, TC.NAME,
SUM(CASE WHEN TS.EXECUTION_STATUS_ID = 1 THEN 1 ELSE 0 END) as NUMBER_FAILED,
SUM(CASE WHEN TS.EXECUTION_STATUS_ID = 2 THEN 1 ELSE 0 END) as NUMBER_PASSED,
SUM(CASE WHEN TS.EXECUTION_STATUS_ID = 5 THEN 1 ELSE 0 END) as NUMBER_BLOCKED,
SUM(CASE WHEN TS.EXECUTION_STATUS_ID = 6 THEN 1 ELSE 0 END) as NUMBER_CAUTION,
SUM(CASE WHEN TS.EXECUTION_STATUS_ID = 3 THEN 1 ELSE 0 END) as NUMBER_NOT_RUN
from SpiraTestEntities.R_TestCases as TC
inner join SpiraTestEntities.R_TestSteps as TS
on TC.TEST_CASE_ID = TS.TEST_CASE_ID
where TC.PROJECT_ID = ${ProjectId}
group by TC.TEST_CASE_ID, TC.NAME
The Finished Report
The following report will be generated: