Creating Requirements Traceability Matrices with Custom Fields

Overview

The business need is to usually to generate a RTM that contains one or more custom property, for example you may have an external requirement identifier or test case identifier that you want to display.

Custom Requirements-Test Case Matrix

To create a custom requirements to test case matrix, you can use the following ESQL query:

select
  'RQ:' + cast (RQ.REQUIREMENT_ID as String) as REQUIREMENT_ID,
  RQ.NAME as REQUIREMENT_NAME,
  RQ.REQUIREMENT_TYPE_NAME,
  RQ.REQUIREMENT_STATUS_NAME,
  RQ.RELEASE_VERSION_NUMBER,
  RQ.CUST_01 as RQ_CUST_01,  
  'TC:' + cast (RTC.TEST_CASE_ID as String) as TEST_CASE_ID,
  RTC.TEST_CASE_NAME,
  TC.CUST_01 as TC_CUST_01
from SpiraTestEntities.R_Requirements as RQ
left join SpiraTestEntities.R_RequirementTestCases as RTC on RQ.REQUIREMENT_ID = RTC.REQUIREMENT_ID 
left join SpiraTestEntities.R_TestCases as TC on RTC.TEST_CASE_ID = TC.TEST_CASE_ID 
where RQ.PROJECT_ID = ${ProjectId}
order by RQ.INDENT_LEVEL

which with the following default template:

<?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>REQUIREMENT_TYPE_NAME</th><th>REQUIREMENT_STATUS_NAME</th><th>RELEASE_VERSION_NUMBER</th><th>RQ_CUST_01</th><th>TEST_CASE_ID</th><th>TEST_CASE_NAME</th><th>TC_CUST_01</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="REQUIREMENT_ID"/></td><td><xsl:value-of select="REQUIREMENT_NAME"/></td><td><xsl:value-of select="REQUIREMENT_TYPE_NAME"/></td><td><xsl:value-of select="REQUIREMENT_STATUS_NAME"/></td><td><xsl:value-of select="RELEASE_VERSION_NUMBER"/></td><td><xsl:value-of select="RQ_CUST_01"/></td><td><xsl:value-of select="TEST_CASE_ID"/></td><td><xsl:value-of select="TEST_CASE_NAME"/></td><td><xsl:value-of select="TC_CUST_01"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

will generate the following output:

Custom Requirements-Requirements Matrix

To create a custom requirements to requirements matrix (bidirectionally), you can use the following ESQL query:

(select
  'RQ:' + cast (RQ1.REQUIREMENT_ID as String) as REQUIREMENT_ID,
  RQ1.NAME as REQUIREMENT_NAME,
  RQ1.REQUIREMENT_TYPE_NAME,
  RQ1.REQUIREMENT_STATUS_NAME,
  RQ1.RELEASE_VERSION_NUMBER,
  RQ1.CUST_01 as RQ1_CUST_01,  
  'RQ:' + cast (RQ2.REQUIREMENT_ID as String) as LINKED_REQUIREMENT_ID,
  RQ2.NAME as LINKED_REQUIREMENT_NAME,
  RQ2.CUST_01 as RQ2_CUST_01
from SpiraTestEntities.R_Requirements as RQ1
left join SpiraTestEntities.R_ArtifactAssociations as AA on RQ1.REQUIREMENT_ID = AA.SOURCE_ARTIFACT_ID and AA.SOURCE_ARTIFACT_TYPE_ID = 1
left join SpiraTestEntities.R_Requirements as RQ2 on AA.DEST_ARTIFACT_ID = RQ2.REQUIREMENT_ID and AA.DEST_ARTIFACT_TYPE_ID = 1
where RQ1.PROJECT_ID = ${ProjectId})
union
(select
  'RQ:' + cast (RQ1.REQUIREMENT_ID as String) as REQUIREMENT_ID,
  RQ1.NAME as REQUIREMENT_NAME,
  RQ1.REQUIREMENT_TYPE_NAME,
  RQ1.REQUIREMENT_STATUS_NAME,
  RQ1.RELEASE_VERSION_NUMBER,
  RQ1.CUST_01 as RQ1_CUST_01,  
  'RQ:' + cast (RQ2.REQUIREMENT_ID as String) as LINKED_REQUIREMENT_ID,
  RQ2.NAME as LINKED_REQUIREMENT_NAME,
  RQ2.CUST_01 as RQ2_CUST_01
from SpiraTestEntities.R_Requirements as RQ1
left join SpiraTestEntities.R_ArtifactAssociations as AA on RQ1.REQUIREMENT_ID = AA.DEST_ARTIFACT_ID and AA.DEST_ARTIFACT_TYPE_ID = 1
left join SpiraTestEntities.R_Requirements as RQ2 on AA.SOURCE_ARTIFACT_ID = RQ2.REQUIREMENT_ID and AA.SOURCE_ARTIFACT_TYPE_ID = 1
where RQ1.PROJECT_ID = ${ProjectId})

which with the following default template:

<?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>REQUIREMENT_TYPE_NAME</th><th>REQUIREMENT_STATUS_NAME</th><th>RELEASE_VERSION_NUMBER</th><th>RQ1_CUST_01</th><th>LINKED_REQUIREMENT_ID</th><th>LINKED_REQUIREMENT_NAME</th><th>RQ2_CUST_01</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="REQUIREMENT_ID"/></td><td><xsl:value-of select="REQUIREMENT_NAME"/></td><td><xsl:value-of select="REQUIREMENT_TYPE_NAME"/></td><td><xsl:value-of select="REQUIREMENT_STATUS_NAME"/></td><td><xsl:value-of select="RELEASE_VERSION_NUMBER"/></td><td><xsl:value-of select="RQ1_CUST_01"/></td><td><xsl:value-of select="LINKED_REQUIREMENT_ID"/></td><td><xsl:value-of select="LINKED_REQUIREMENT_NAME"/></td><td><xsl:value-of select="RQ2_CUST_01"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

will generate the following output:

In both cases we have simply chosen  CUST_01 for the custom fields to show. In reality you'd want to display the custom property that contains the external identifier that you need.