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 AA.SOURCE_ARTIFACT_ID AS Release_ID
      ,RL.NAME AS ReleaseName
      ,AA.DEST_ARTIFACT_ID AS Associated_Requirement_ID
	  ,RQ.NAME AS Associated_Requirement_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
  LEFT OUTER JOIN SpiraTestEntities.R_RELEASES AS RL ON RL.[RELEASE_ID] = AA.SOURCE_ARTIFACT_ID
  LEFT JOIN SpiraTestEntities.R_REQUIREMENTS AS RQ ON AA.DEST_ARTIFACT_ID = RQ.REQUIREMENT_ID
  WHERE AA.SOURCE_ARTIFACT_TYPE_ID = 4 AND AA.DEST_ARTIFACT_TYPE_ID = 1 AND RQ.PROJECT_ID = ${ProjectId}

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>Release_ID</th><th>ReleaseName</th><th>Associated_Requirement_ID</th><th>Associated_Requirement_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="Release_ID"/></td><td><xsl:value-of select="ReleaseName"/></td><td><xsl:value-of select="Associated_Requirement_ID"/></td><td><xsl:value-of select="Associated_Requirement_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 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.