Using a Custom Report to Find Test Cases Not in a Test Set

Creating the Custom Report

To get the list of test cases that are not in a particular test set, we will make use of the Entity SQL EXISTS clause. This works similar to a traditional database SQL NOT IN clause.

Using the custom report, custom section editor, paste in the following Entity SQL query:

select TC.TEST_CASE_ID, TC.NAME as TEST_CASE_NAME
from SpiraTestEntities.R_TestCases as TC
where TC.PROJECT_ID = ${ProjectId}
and not exists (select TS.TEST_CASE_ID from SpiraTestEntities.R_TestSetTestCases as TS where TS.TEST_SET_ID = 6 and TS.TEST_CASE_ID = TC.TEST_CASE_ID)

As you can see, we're asking the system to return the test case ID and name, where the test case is in the current project and is not part of Test Set ID = 6. You can of course change the test set id to be any one you want.

Once you have entered in this query, click the Preview Results button and you should see something like:

Next, we choose the option to Create Default Template and then take its generated output and make the name of the headings look nicer and add the Test Case (TC) prefix to the first column:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">
  <xsl:template match="/RESULTS">
    <table class="DataGrid"><tr><th>Test Case #</th><th>Test Case Name</th></tr>
      <xsl:for-each select="ROW">
        <tr><td>TC:<xsl:value-of select="TEST_CASE_ID"/></td><td><xsl:value-of select="TEST_CASE_NAME"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

Now you can save the report.

Running the Custom Report

Now to run the report, go to the main Reports tab in Spira and choose the option to generate the report. For this example, we'll use the Excel option: