Background

Given we have a requirement with four associated incidents:

There is a Custom Report view named SpiraTestEntities.R_ArtifactAssociations that stores all the associated artifact. If you query the associations for requirement with ID=4 you may not get some of the associated incidents:

What if you need to get all four incidents in your report?

Create a Report

First, you need to create a Custom report:

  1. Go to Administration  > Edit Reports
  2. Click Add new Report
  3. Specify that it should allow generating report file in MS-Word, Excel, HTML and PDF formats (upon need)
  4. Click on Add New Custom Section:
  6. Insert the ESQL query, depending on what type of associations you'd like to get (see specific examples below)

 

Writing query

To extract associated incidents, we used different view to get all the items: SpiraTestEntities.R_RequirementIncidents

This query is selecting all the records where Requirement ID = 4 from RequirementIncidents Custom Report view:

SELECT
RQIN.REQUIREMENT_ID AS SourceRQ, 
RQ.Name as RequirementName,
RQIN.INCIDENT_ID AS DestinationInc_ID, 
INC.NAME AS IncidentName
FROM SpiraTestEntities.R_RequirementIncidents AS RQIN
INNER JOIN SpiraTestEntities.R_Incidents AS INC ON RQIN.INCIDENT_ID=INC.INCIDENT_ID
INNER JOIN SpiraTestEntities.R_Requirements AS RQ ON RQIN.REQUIREMENT_ID = RQ.REQUIREMENT_ID
WHERE RQIN.REQUIREMENT_ID = 4

The result of query execution can be seen after click on Preview results button:

Do not forget to click on Create Default Template or simply copy/paste below provided code into the Template box:

<?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>SourceRQ</th><th>RequirementName</th><th>DestinationInc_ID</th><th>IncidentName</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="SourceRQ"/></td><td><xsl:value-of select="RequirementName"/></td><td><xsl:value-of select="DestinationInc_ID"/></td><td><xsl:value-of select="IncidentName"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>