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)

Extract all Requirements associated to each Release

Insert this query into the Custom section of reports page:

SELECT 
    RQ.REQUIREMENT_ID AS Requirement_ID,
    RQ.NAME AS Requirement_Name,
    RL.RELEASE_ID AS Release_ID,
    RL.NAME AS Release_Name,
    AA.CREATION_DATE AS Association_Date,
    AA.CREATOR_NAME AS Associated_By
FROM SpiraTestEntities.R_REQUIREMENTS AS RQ
INNER JOIN SpiraTestEntities.R_ArtifactAssociations AS AA 
    ON (AA.SOURCE_ARTIFACT_TYPE_ID = 1 AND AA.SOURCE_ARTIFACT_ID = RQ.REQUIREMENT_ID AND AA.DEST_ARTIFACT_TYPE_ID = 4)
    OR (AA.DEST_ARTIFACT_TYPE_ID = 1 AND AA.DEST_ARTIFACT_ID = RQ.REQUIREMENT_ID AND AA.SOURCE_ARTIFACT_TYPE_ID = 4)
INNER JOIN SpiraTestEntities.R_RELEASES AS RL 
    ON (AA.SOURCE_ARTIFACT_TYPE_ID = 4 AND AA.SOURCE_ARTIFACT_ID = RL.RELEASE_ID)
    OR (AA.DEST_ARTIFACT_TYPE_ID = 4 AND AA.DEST_ARTIFACT_ID = RL.RELEASE_ID)
WHERE RQ.PROJECT_ID = ${ProjectId}
  AND RQ.IS_DELETED = False
ORDER BY RQ.REQUIREMENT_ID ASC, RL.NAME ASC

Verify the results by clicking on Preview Results:

Press on Create Default Template or simply copy the following code: 

<?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>Release_ID</th><th>Release_Name</th><th>Association_Date</th><th>Associated_By</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="Release_ID"/></td><td><xsl:value-of select="Release_Name"/></td><td><xsl:value-of select="Association_Date"/></td><td><xsl:value-of select="Associated_By"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

Click Save on both pages of the report, then it is ready for use. Note that you this report will provide one row per requirement and will list all associations to all releases. You can customize this further if you want to only show requirements for a particular release.

Extract all the Risks associated to a Requirement

Insert this query into the Custom section instead:

SELECT AA.SOURCE_ARTIFACT_ID AS Requirement_ID
	  ,RQ.[NAME] AS Requirement_Name
	  ,AA.DEST_ARTIFACT_ID AS Associated_Artifact_ID
	  ,RK.[NAME] AS Risk_Name
	  ,AA.CREATION_DATE AS Created_ON
      ,AA.CREATOR_NAME AS Created_By
      ,AA.ARTIFACT_LINK_TYPE_NAME AS Link_Type
  FROM SpiraTestEntities.R_ArtifactAssociations AS AA
  INNER JOIN SpiraTestEntities.R_REQUIREMENTS AS RQ ON RQ.REQUIREMENT_ID = AA.SOURCE_ARTIFACT_ID
  INNER JOIN SpiraTestEntities.R_RISKS AS RK ON AA.DEST_ARTIFACT_ID = RK.RISK_ID
  WHERE AA.SOURCE_ARTIFACT_TYPE_ID = 1 AND AA.DEST_ARTIFACT_TYPE_ID = 14 AND RQ.PROJECT_ID = ${ProjectId}

Verify the results:

Press on Create Default Template or simply copy the following code: 

<?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>Associated_Artifact_ID</th><th>Risk_Name</th><th>Created_ON</th><th>Created_By</th><th>Link_Type</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="Associated_Artifact_ID"/></td><td><xsl:value-of select="Risk_Name"/></td><td><xsl:value-of select="Created_ON"/></td><td><xsl:value-of select="Created_By"/></td><td><xsl:value-of select="Link_Type"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

Click Save on both report pages, so to make it ready for use from Report Center.