Retrieving requirement associations

Background

You may need to get the artifact associations and it is very easy thing to do.
Sometimes, it can be challenging if you have a reverse dependency - that makes the artifact to be visible in the list of associations but the record is not retrieved by simple query.

Given we'd like to retrieve a list of associations for requirement RQ:34:

Simply querying the SpiraTestEntities.R_ArtifactAssociations custom report view:

SELECT value R FROM SpiraTestEntities.R_ArtifactAssociations as R 
WHERE R.Source_Artifact_ID = 34

Output result will be only 3 artifacts out of 4 shown on the initial Association screen:

Why is that?

Solution

The trick is that one of the associations has type set to "Prerequisite-for" that means it is a reverse of "Depends-on" link type.
In that case RQ:34 is not a source artifact any more, but becomes a destination for that particular one - RQ:35 is associated with RQ:34 with type "Dependent-on"

This creates a reverse link to RQ:34 and it is now a "Prerequisite-for":

So, to get all the requirement's associations we have to use a modified query that retrieves all the records for RQ:34:

SELECT AA.DEST_ARTIFACT_ID AS AssociatedArtID, 
AA.SOURCE_ARTIFACT_ID AS SourceArtifact, 
AA.DEST_ARTIFACT_TYPE_NAME AS AssociatedArtifact, 
AA.ARTIFACT_LINK_TYPE_NAME AS Link
FROM SpiraTestEntities.R_ArtifactAssociations AS AA
WHERE 
(AA.SOURCE_ARTIFACT_ID = 34 OR AA.DEST_ARTIFACT_ID = 34) 
AND AA.SOURCE_ARTIFACT_TYPE_ID = 1

The output result of the execution will be:

So we got all four associated artifacts for the given requirement, now including reverse depends-on.

Click the Create Default Template to generate the XSLT template or simply paste the 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>AssociatedArtID</th><th>SourceArtifact</th><th>AssociatedArtifact</th><th>Link</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="AssociatedArtID"/></td><td><xsl:value-of select="SourceArtifact"/></td><td><xsl:value-of select="AssociatedArtifact"/></td><td><xsl:value-of select="Link"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

Click save twice an your report is now ready for use from the report center module.