Displaying a Requirements - Risk Exposure Report

Preconditions

In order to use this report, it assumes that you have already some requirements in your SpiraPlan product:

It also assumes you have some risks in your SpiraPlan product:

and finally, that you have mapped some of these requirements to risks (and vice-versa):

Creating the Custom Report

With the necessary data in place, simply create a new custom report, choosing the following options:

• Name: Requirements - Aggregate Risk Exposure
• Category: Requirements
• Format(s):
  • HTML
  • Excel (printable)
  • Word (current)
  • PDF

Then add a custom section to this report, with the following query and template:

Report Query

select R.REQUIREMENT_ID, R.REQUIREMENT_NAME, sum(R.RISK_EXPOSURE) as RISK_EXPOSURE
from
((select RQ.REQUIREMENT_ID, RQ.NAME as REQUIREMENT_NAME, RK.RISK_ID, RK.NAME as RISK_NAME, RK.RISK_IMPACT_NAME, RK.RISK_PROBABILITY_NAME, RK.RISK_EXPOSURE
from SpiraTestEntities.R_Requirements as RQ
left join SpiraTestEntities.R_ArtifactAssociations as AA on RQ.REQUIREMENT_ID = AA.SOURCE_ARTIFACT_ID and AA.SOURCE_ARTIFACT_TYPE_ID = 1
left join SpiraTestEntities.R_Risks as RK on AA.DEST_ARTIFACT_ID = RK.RISK_ID and AA.DEST_ARTIFACT_TYPE_ID = 14
where RQ.PROJECT_ID = ${ProjectId}
and RQ.IS_DELETED = false)
union
(select RQ.REQUIREMENT_ID, RQ.NAME as REQUIREMENT_NAME, RK.RISK_ID, RK.NAME as RISK_NAME, RK.RISK_IMPACT_NAME, RK.RISK_PROBABILITY_NAME, RK.RISK_EXPOSURE
from SpiraTestEntities.R_Requirements as RQ
left join SpiraTestEntities.R_ArtifactAssociations as AA on RQ.REQUIREMENT_ID = AA.DEST_ARTIFACT_ID and AA.DEST_ARTIFACT_TYPE_ID = 1
left join SpiraTestEntities.R_Risks as RK on AA.SOURCE_ARTIFACT_ID = RK.RISK_ID and AA.SOURCE_ARTIFACT_TYPE_ID = 14
where RQ.PROJECT_ID = ${ProjectId}
and RQ.IS_DELETED = false)) as R
group by R.REQUIREMENT_ID, R.REQUIREMENT_NAME
order by RISK_EXPOSURE desc

Report Template

For the report template, we added some additional logic into the XSLT:

• If the Risk Exposure is > 0 and < 10, it will be shaded yellow.
• If the Risk Exposure is >= 10 and < 20, it will be shaded orange.
• If the Risk Exposure is >= 20, it will be shaded red.

<?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>Requirement Name</th><th>Risk Exposure</th></tr>
      <xsl:for-each select="ROW">
        <tr>
		<td>RQ:<xsl:value-of select="REQUIREMENT_ID"/></td>
		<td><xsl:value-of select="REQUIREMENT_NAME"/></td>
		<xsl:choose>
			<xsl:when test="RISK_EXPOSURE &gt; 0 and RISK_EXPOSURE &lt; 10">
				<td style="background-color: yellow"><xsl:value-of select="RISK_EXPOSURE"/></td>
			</xsl:when>
			<xsl:when test="RISK_EXPOSURE &gt;= 10 and RISK_EXPOSURE &lt; 20">
				<td style="background-color: orange"><xsl:value-of select="RISK_EXPOSURE"/></td>
			</xsl:when>
			<xsl:when test="RISK_EXPOSURE &gt;= 20">
				<td style="background-color: red"><xsl:value-of select="RISK_EXPOSURE"/></td>
			</xsl:when>
			<xsl:otherwise>
				<td><xsl:value-of select="RISK_EXPOSURE"/></td>
			</xsl:otherwise>
		</xsl:choose>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

Click Save on the popup, and Save on the main report page. The report is now ready to use.

Running the Report

Now that you have created the report, go to the main Reports tab and choose to run the report in one of the supported formats. You will now see the following:

which will render something like: