Report of Requirements Test Coverages

Creating the Custom Report

a) Number of Test Cases per Requirement

The following custom report can be used to generate a table of requirements along with the number of test cases associated with them.

select R.REQUIREMENT_ID, R.NAME, R.RELEASE_VERSION_NUMBER, R.COVERAGE_COUNT_TOTAL
from SpiraTestEntities.R_Requirements as R
where R.PROJECT_ID = ${ProjectId} and R.IS_DELETED = False

Then the following report template can be used to display the table:

<?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>Requirement Id</th><th>Name</th><th>Release</th><th># Test Cases</th></tr>
      <xsl:for-each select="ROW">
        <tr><td>RQ:<xsl:value-of select="REQUIREMENT_ID"/></td><td><xsl:value-of select="NAME"/></td><td><xsl:value-of select="RELEASE_VERSION_NUMBER"/></td><td><xsl:value-of select="COVERAGE_COUNT_TOTAL"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

a) Test Cases With No Mapped Requirements

The following custom report can be used to generate a table of test cases that don't have any mapped requirements along with their status name:

select TC.TEST_CASE_ID, TC.NAME, TC.TEST_CASE_STATUS_NAME
from SpiraTestEntities.R_TestCases as TC
left join SpiraTestEntities.R_RequirementTestCases as RT on TC.TEST_CASE_ID = RT.TEST_CASE_ID
where TC.PROJECT_ID = ${ProjectId} and TC.IS_DELETED = False and RT.REQUIREMENT_ID is NULL

Then the following report template can be used to display the table:

<?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 Id</th><th>Name</th><th>Status</th></tr>
      <xsl:for-each select="ROW">
        <tr><td>TC:<xsl:value-of select="TEST_CASE_ID"/></td><td><xsl:value-of select="NAME"/></td><td><xsl:value-of select="TEST_CASE_STATUS_NAME"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

Executing the Report

When you run the report in the sample project assuming you have a mixture of requirements and test cases with some mapped and some not mapped, you will get the following: