Overview

All the Spira artifacts follow a certain URL syntax that can be used to access the specific artifact item assuming the user is part of the project and has the right permissions to view it. 

The generic format consists of the following elements separated by the delimiter "/" after the protocol syntax. The final string is appended with the .aspx after the Artifact Item.

So, if Library Information Systems is having a Project ID = 1 and we are looking for Incident #4, then the URL would look like the following:

https://champions.spiraservice.net/1/Incident/4.aspx

Illustration

• In the following diagram, you can see that the Library Information Systems is associated with the PR1 (Project ID = 1)

• In the following diagram showing Incident #4, you can see that the URL string is constructed as explained before.
• Note that the browser (Chrome used here) is hiding the protocol (https://) but is required depending upon whether you have unsecured access (http) or secured access (https).

SQL Query

Given below is the example query just to construct this URL. To confirm that the right project id and incident id were brought, these additional fields are also included in the where clause. 

select 
  R.PROJECT_ID, 
  R.INCIDENT_ID, 
 ("https://champions.spiraservice.net/" + 
  Cast (R.PROJECT_ID as Edm.String) + 
  "/Incident/" + 
  Cast(R.INCIDENT_ID as Edm.String) + 
  ".aspx") as URL
from 
  SpiraTestEntities.R_Incidents as R 
where 
  R.PROJECT_ID = ${ProjectId} and
  R.IS_DELETED = False

Query Explanation

1. The Project ID and Incident ID here are numeric. They have to be cast to String by using Cast and Edm.String datatype.
2. The resulting strings are concatenated using the + operator.
3. The final constructed string is given a name URL
4. The IS_DELETED = False includes only valid incidents that are not deleted

Preview Output

Given below is the output showing the resulting URL string. 

XSLT Report Configuration

While the above display  works, often, people want to see the an abbreviated clickable link instead of the URL itself. This requires  some XSLT transformation on the 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>PROJECT_ID</th><th>INCIDENT_ID</th><th>URL</th><th>Clickable Link</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="PROJECT_ID"/></td>
	     <td><xsl:value-of select="INCIDENT_ID"/></td>
	     <td><xsl:value-of select="URL"/></td>
              <td><xsl:variable name="link" select="URL"/>
                 <a href="{$link}" target="_blank">View IN-<xsl:value-of select="INCIDENT_ID"/></a></td>
              </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

XSLT Explanation

1. The lines 10 and 11 apply the required transformation to the fourth column we have added to the three columns already brought from the SQL Query. 
2. Since HTML anchor tags  have three components, the link, the browser window to open, and the name of the link, we have defined a variable link defined in 10 that constructs the HTML anchor href based on the URL we have already computed. 
3. The line 11 expands on this href along with the target to open the link in a new browser window (target = "_blank") and the abbreviated link name (View IN- prefix followed by the INCIDENT-ID).

Report Output

Given below is the report output. Clicking on View IN-1 will open the Incident #1 in the Library Information Systems Project #1 as we have explained before. 

1. Please note that an active user session must be present for this action and if the session is no longer active, you will be required to login.
2. The prefix (View IN-) can be changed but please note that empty white spaces are not always treated well by browsers and so it is recommended to have some non-white space characters as a prefix.