Displaying a Requirements Test Coverage Report by Release

Requirements Coverage Report by Release

Overview

When you view the list of requirements in Spira, you can see the overall test coverage for each requirement as a special Test Coverage column next to the requirement name:

However this is the overall test coverage for the requirement, across all releases it has been tested in.

Suppose you want the test coverage for just a specific release and/or sprint? How would you get that?

At a summary level, you can use the Requirements Regression Coverage home page widget:

However, to get the full list of requirements that makes up this data, you would need to use a custom report instead.

Requirements Test Coverage By Release Report

This section assumes you know how to create a new custom report in Spira, as described in this guide. Create a new Custom Report and choose the option to add a new Custom Section:

In the custom section, choose a name such as "Requirements Test Coverage by Release" and then enter the following ESQL query:

select RQ.REQUIREMENT_ID, RQ.NAME as REQUIREMENT_NAME, RTC.TEST_CASE_ID, TCR.EXECUTION_STATUS_ID, TCR.EXECUTION_STATUS_NAME
from
  SpiraTestEntities.R_Requirements as RQ left join
  SpiraTestEntities.R_RequirementTestCases as RQT on RQ.REQUIREMENT_ID = RQT.REQUIREMENT_ID join
  SpiraTestEntities.R_ReleaseTestCases as RTC on RQT.TEST_CASE_ID = RTC.TEST_CASE_ID left join
    (select TR1.TEST_CASE_ID, TR1.EXECUTION_STATUS_ID, TR1.EXECUTION_STATUS_NAME
    from SpiraTestEntities.R_TestRuns as TR1 join
      (select TR.TEST_CASE_ID, max(TR.END_DATE) as EXECUTION_DATE
      from SpiraTestEntities.R_TestRuns as TR where TR.RELEASE_ID in {${ReleaseAndChildIds}} and TR.PROJECT_ID = ${ProjectId} and TR.END_DATE is not null
      group by TR.TEST_CASE_ID) as TR2 on TR1.TEST_CASE_ID = TR2.TEST_CASE_ID and TR1.END_DATE = TR2.EXECUTION_DATE
    where TR1.RELEASE_ID in {${ReleaseAndChildIds}} and TR1.PROJECT_ID = ${ProjectId}) as TCR on RTC.TEST_CASE_ID = TCR.TEST_CASE_ID
where
  RTC.PROJECT_ID = ${ProjectId} and
  RTC.RELEASE_ID in {${ReleaseAndChildIds}}
group by RQ.REQUIREMENT_ID, RQ.NAME, RTC.TEST_CASE_ID, TCR.EXECUTION_STATUS_ID, TCR.EXECUTION_STATUS_NAME
order by RQ.NAME, RQ.REQUIREMENT_ID, TCR.EXECUTION_STATUS_ID

For the XSLT template, instead of using the option to auto-generate, use this template instead:

<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:key name="groups" match="/RESULTS/ROW" use="REQUIREMENT_ID" />
  <xsl:template match="/RESULTS">
    <table class="DataGrid">
		<tr>
			<th>Req ID</th>
			<th>Requirement Name</th>
			<th>Test Case Coverage</th>
			<th>Graph</th>
		</tr>
		<xsl:apply-templates select="ROW[generate-id() = generate-id(key('groups', REQUIREMENT_ID)[1])]"/>
	</table>
  </xsl:template>
  <xsl:template match="ROW">
        <tr>
			<td>RQ:<xsl:value-of select="REQUIREMENT_ID"/></td>
			<td><xsl:value-of select="REQUIREMENT_NAME"/></td>
			<td>
				<xsl:for-each select="key('groups', REQUIREMENT_ID)">
					TC:<xsl:value-of select="TEST_CASE_ID"/> = 
					<xsl:choose>
						<xsl:when test="EXECUTION_STATUS_NAME != ''">
							<xsl:value-of select="EXECUTION_STATUS_NAME " />
						</xsl:when>
						<xsl:otherwise>
							<xsl:text>Not Run</xsl:text>
						</xsl:otherwise>
					</xsl:choose>, 
				</xsl:for-each>
			</td>
			<td>
				<div style="display:inline-block; white-space: nowrap">
					<xsl:for-each select="key('groups', REQUIREMENT_ID)">
						<xsl:choose>
							<xsl:when test="EXECUTION_STATUS_NAME = 'Passed'">
								<div style="display:inline-block; background-color:lime; height: 20px; width: 20px"></div>
							</xsl:when>
							<xsl:when test="EXECUTION_STATUS_NAME = 'Failed'">
								<div style="display:inline-block; background-color:red; height: 20px; width: 20px"></div>
							</xsl:when>
							<xsl:when test="EXECUTION_STATUS_NAME = 'Blocked'">
								<div style="display:inline-block; background-color:yellow; height: 20px; width: 20px"></div>
							</xsl:when>
							<xsl:when test="EXECUTION_STATUS_NAME = 'Caution'">
								<div style="display:inline-block; background-color:orange; height: 20px; width: 20px"></div>
							</xsl:when>
							<xsl:otherwise>
								<div style="display:inline-block; background-color:silver; height: 20px; width: 20px"></div>
							</xsl:otherwise>
						</xsl:choose>				
					</xsl:for-each>
				</div>
			</td>
        </tr>
    </xsl:template>
</xsl:stylesheet>

When you run the new custom report, you will see a table like the following:

You can run this custom report for any release in the product.

*Note that this report template will only look good in HTML format because of the inline graph. If you want to display in PDF or MS-Word, use this simplified template instead:

<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:key name="groups" match="/RESULTS/ROW" use="REQUIREMENT_ID" />
  <xsl:template match="/RESULTS">
    <table class="DataGrid">
		<tr>
			<th>Req ID</th>
			<th>Requirement Name</th>
			<th>Test Case Coverage</th>
		</tr>
		<xsl:apply-templates select="ROW[generate-id() = generate-id(key('groups', REQUIREMENT_ID)[1])]"/>
	</table>
  </xsl:template>
  <xsl:template match="ROW">
        <tr>
			<td>RQ:<xsl:value-of select="REQUIREMENT_ID"/></td>
			<td><xsl:value-of select="REQUIREMENT_NAME"/></td>
			<td>
				<xsl:for-each select="key('groups', REQUIREMENT_ID)">
					TC:<xsl:value-of select="TEST_CASE_ID"/> = 
					<xsl:choose>
						<xsl:when test="EXECUTION_STATUS_NAME != ''">
							<xsl:value-of select="EXECUTION_STATUS_NAME " />
						</xsl:when>
						<xsl:otherwise>
							<xsl:text>Not Run</xsl:text>
						</xsl:otherwise>
					</xsl:choose>, 
				</xsl:for-each>
			</td>
        </tr>
    </xsl:template>
</xsl:stylesheet>