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:

  1. Create a new custom report
  2. Give it a name and description
  3. You can leave the header and footer blank for now
  4. Enable the various formats that you want to be made available (typically include HTML and Excel for this kind of tabular report)
  5. Set the category as "Test Case Reports"
  6. Add a custom section. Give the section a name (the description, header, footer can be left blank).
  7. Paste the ESQL code displayed below into the Query field
  8. Click 'Preview Results' to display the results of the query
  9. Click 'Create Default Template' to generate a simple tabular grid to display the data.
  10. Now save the section
  11. 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: