Customer Request

A customer used test sets to group a collection of test cases  listing the specific test steps. This was done as part of the test planning to facilitate discussion about details for the expected result and appropriately ensure that the test steps had the required sample data for subsequent test execution. 

Customer Request

SQL Query

Given below is the query to pull this type of report.

select
   TSC.TEST_SET_ID,
   TSC.TEST_SET_NAME,
   TSC.TEST_CASE_ID,
   TC.NAME as TEST_CASE_NAME,
   TC.DESCRIPTION as TEST_CASE_DESCRIPTION,
   TSP.TEST_STEP_ID as TEST_STEP_NUMBER,
   TSP.DESCRIPTION as STEP_DESCRIPTION,
   TSP.EXPECTED_RESULT
from
   SpiraTestEntities.R_TestSetTestCases as TSC
left join SpiraTestEntities.R_TestCases as TC on
   TSC.TEST_CASE_ID = TC.TEST_CASE_ID and TC.PROJECT_ID = TSC.PROJECT_ID
left join SpiraTestEntities.R_TestSets as TS on
    TSC.TEST_SET_ID = TS.TEST_SET_ID and TS.PROJECT_ID = TSC.PROJECT_ID
left join SpiraTestEntities.R_TestSteps as TSP on
    TSP.TEST_CASE_ID = TC.TEST_CASE_ID and TSP.PROJECT_ID = TSC.PROJECT_ID
where
  TSC.PROJECT_ID = ${ProjectId} and
  TS.IS_DELETED = False and
  TC.IS_DELETED = False and
  TSP.IS_DELETED = False

Query Explanation

  1. Since there are test sets and test cases, we need to use the bridge table SpiraTestEntities.R_TestSetTestCases (aliased as TSC)
  2. This bridge table is joined with Test Sets (aliased as TS) to bring the TEST SET specific details
  3. The same bridge table is joined with Test Cases (aliased as TC) to bring the TEST  CASE specific details
  4. Since the request also requires Test Steps that are part of the Test Cases, the TEST CASE table is joined with TEST STEPS (aliased as TSP) .
  5. All join conditions also should bring the PROJECT_ID to be the same.
  6. While left outer join is used here to bring everything on the left even when matched conditions on the right don't exist, if preferred, you can also use inner join.
  7. In the where clause, we remove data where any record deleted is not brought and the project selected (${ProjectId}) token is used.

Result

Once the SQL query is working and you have created a default XSLT template, then, the report would render as follows. This report can be saved in HTML or Excel format.

SQL Output