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:

TEST_CASE_IDNAMENUMBER_FAILEDNUMBER_PASSEDNUMBER_BLOCKEDNUMBER_CAUTIONNUMBER_NOT_RUN
2Ability to create new book10003
3Ability to edit existing book01010
4Ability to create new author11002
5Ability to edit existing author01100
8Book management00000
9Author management00000
12Person loses book and needs to report loss00003
16Open Up Web Browser01000
17Login to Application02000
48Tests the archiving of data in the system10000