Create a report to display all artifacts across the system owned by a specific person

Overview

A customer was looking for a simple report that would display a list of Artifacts along with Product name, in this format:

Where included only active Artifacts (that have IS_DELETED parameter set to false).

Solution

The solution is to use the following ESQL in a custom report section:

(SELECT INC.INCIDENT_ID AS ArtifactID, 
'Incident' AS ArtifactType,
INC.INCIDENT_TYPE_NAME AS ArtifactSubType,
INC.NAME AS ArtifactName,
INC.PROJECT_NAME AS ProjectName,
INC.OWNER_NAME as OwnerName
FROM SpiraTestEntities.R_Incidents AS INC
inner join SpiraTestEntities.R_Users as US on INC.OWNER_ID = US.USER_ID
WHERE INC.IS_DELETED =  false)

UNION

(SELECT TK.TASK_ID AS ArtifactID, 
'Task' AS ArtifactType,
TK.TASK_TYPE_NAME AS ArtifactSubType,
TK.NAME AS ArtifactName,
TK.PROJECT_NAME AS ProjectName,
TK.OWNER_NAME as OwnerName
FROM SpiraTestEntities.R_Tasks AS TK
inner join SpiraTestEntities.R_Users as US on TK.OWNER_ID = US.USER_ID
WHERE TK.IS_DELETED =  false)

UNION

(SELECT TC.TEST_CASE_ID AS ArtifactID, 
'TestCase' AS ArtifactType,
TC.TEST_CASE_TYPE_NAME AS ArtifactSubType,
TC.NAME AS ArtifactName,
TC.PROJECT_NAME AS ProjectName,
TC.OWNER_NAME as OwnerName
FROM SpiraTestEntities.R_TestCases AS TC
inner join SpiraTestEntities.R_Users as US on TC.OWNER_ID = US.USER_ID
WHERE TC.IS_DELETED =  false)

UNION

(SELECT RK.Risk_ID AS ArtifactID, 
'Risk' AS ArtifactType,
RK.RISK_TYPE_NAME AS ArtifactSubType,
RK.NAME AS ArtifactName,
RK.PROJECT_NAME AS ProjectName,
RK.OWNER_NAME as OwnerName
FROM SpiraTestEntities.R_Risks AS RK
inner join SpiraTestEntities.R_Users as US on RK.OWNER_ID = US.USER_ID
WHERE RK.IS_DELETED =  false)

UNION

(SELECT RQ.REQUIREMENT_ID AS ArtifactID, 
'Requirement' AS ArtifactType,
RQ.REQUIREMENT_TYPE_NAME AS ArtifactSubType,
RQ.NAME AS ArtifactName,
RQ.PROJECT_NAME AS ProjectName,
RQ.OWNER_NAME as OwnerName
FROM SpiraTestEntities.R_Requirements AS RQ
inner join SpiraTestEntities.R_Users as US on RQ.OWNER_ID = US.USER_ID
WHERE RQ.IS_DELETED =  false)

In case you'd like to specify a user to get this report for - please add below expression to the WHERE statement in each section:

AND [TABLE_NAME].OWNER_ID = 'USER_ID_HERE'

Replace 'USER_ID_HERE' with the ID of the desired user to apply the filter and corresponding [TABLE_NAME]. abbreviation according to the statement (INC. for Incidents, TK. for Tasks, TC. for Test Cases, etc.)

Then, either choose to create the 'Default Template' for this query, or use the following styled XSLT:

<?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>ArtifactID</th>
<th>ArtifactType</th>
<th>ArtifactSubType</th>
<th>ArtifactName</th>
<th>ProjectName</th>
<th>OwnerName</th>
</tr>
<xsl:for-each select="ROW">
<tr>
<td><xsl:value-of select="ArtifactID"/></td>
<td><xsl:value-of select="ArtifactType"/></td>
<td><xsl:value-of select="ArtifactSubType"/></td>
<td><xsl:value-of select="ArtifactName"/></td>
<td><xsl:value-of select="ProjectName"/></td>
<td><xsl:value-of select="OwnerName"/></td>
</tr>
</xsl:for-each>
</table>
</xsl:template>
</xsl:stylesheet>

When you run the report, you will get: