Creating a Report of Test Cases and Test Runs in Hierarhical Order

The steps are:

1. Create a new custom report in Administration. Make sure Excel is listed as one of the output formats
2. Add a new custom section to the report
3. In this custom section, add the following ESQL query depending on which version of Spira you have:
   
   (a) For Spira v5.x databases and newer:

   select
     TF.NAME as FOLDER_NAME, TC.TEST_CASE_ID, TC.NAME, TR.EXECUTION_STATUS_NAME as RUN_STATUS,
     TR.END_DATE as EXECUTION_DATE, TS.POSITION, TS.DESCRIPTION as STEP_DESCRIPTION,
     TS.EXPECTED_RESULT, TS.EXECUTION_STATUS_NAME as STEP_STATUS, TS.ACTUAL_RESULT,
     TF.NAME + '-' + TC.NAME + '-' + cast (TR.TEST_RUN_ID as string) + '-' + cast (TS.POSITION as string) as location
   from SpiraTestEntities.R_TestRuns as TR inner join SpiraTestEntities.R_TestCases as TC on TR.TEST_CASE_ID = TR.TEST_CASE_ID
   inner join SpiraTestEntities.R_TestRunSteps as TS on TS.TEST_RUN_ID = TR.TEST_RUN_ID
   inner join SpiraTestEntities.R_TestCaseFolders as TF on TF.TEST_CASE_FOLDER_ID = TC.TEST_CASE_FOLDER_ID
   where TC.PROJECT_ID = ${ProjectId}
   order by location

   (note: Spira v5.x and later sorts test cases differently so the ordering of test cases will be by name vs. hierarchical position)
   
   (b) For Spira v4.x databases:

   select TC.TEST_CASE_ID, TC.NAME, TR.EXECUTION_STATUS_NAME as RUN_STATUS, TR.END_DATE as EXECUTION_DATE, TS.POSITION, TS.DESCRIPTION as STEP_DESCRIPTION, TS.EXPECTED_RESULT, TS.EXECUTION_STATUS_NAME as STEP_STATUS, TS.ACTUAL_RESULT, TC.INDENT_LEVEL + '-' + cast (TR.TEST_RUN_ID as string) + '-' + cast (TS.POSITION as string) as location
   from SpiraTestEntities.R_TestRuns as TR inner join SpiraTestEntities.R_TestCases as TC on TR.TEST_CASE_ID = TR.TEST_CASE_ID
   inner join SpiraTestEntities.R_TestRunSteps as TS on TS.TEST_RUN_ID = TR.TEST_RUN_ID
   where TC.PROJECT_ID = ${ProjectId}
   order by location

4. Click on the button to 'Create Default Template'. Now save your custom report.
5. Go to Reports
6. Choose your new report
7. Choose MS-Excel as the output format
8. Click on the button to generate the report
9. You now have an Excel sheet with the required list of test runs and test steps: