Creating a custom report to display all artifacts owned by u

Creating a Custom Report

Locate the report in Administration > Reporting> Edit Reports
If it is a standard report make sure to clone it first before you can edit it
Click on 'Add new Custom Section'

Building the Query - all owners and artifacts across the Products

To get the list of Incidents and Tasks that are assigned to each user, the following query should be used:

SELECT
 INC.INCIDENT_ID, INC.NAME, TK.TASK_ID, TK.NAME as TaskName, INC.OWNER_NAME 
FROM 
 SpiraTestEntities.R_Tasks as TK
INNER JOIN SpiraTestEntities.R_Incidents AS INC ON TK.OWNER_ID = INC.OWNER_ID
INNER JOIN SpiraTestEntities.R_Projects AS PR ON PR.PROJECT_ID = TK.PROJECT_ID 
AND PR.PROJECT_ID = INC.PROJECT_ID
WHERE 
INC.IS_DELETED = false
and TK.IS_DELETED = false
and PR.IS_ACTIVE = true
ORDER BY INC.OWNER_NAME

In this section SELECT statement extracts required columns, INNER JOIN verifies that the owner for the incident and task is the same for each row.
WHERE statement filters the records by active products and not deleted artifacts so to ignore all deleted items (still in the database) and disabled products.
ORDER BY is sorting records by owner alphabetically.

Once copied, paste the query into the Custom Section query window. Please click the "Create Default Template" button, so the application generates XSLT code for you:

Or just copy and paste below XSLT code into the Template box:

<?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>INCIDENT_ID</th><th>NAME</th><th>TASK_ID</th><th>TaskName</th><th>OWNER_NAME</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="INCIDENT_ID"/></td><td><xsl:value-of select="NAME"/></td><td><xsl:value-of select="TASK_ID"/></td><td><xsl:value-of select="TaskName"/></td><td><xsl:value-of select="OWNER_NAME"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

Building the query - for a particular owner

In order to get the results generated for one particular owner only, use the below given ESQL query:

SELECT
 INC.INCIDENT_ID, INC.NAME, TK.TASK_ID, TK.NAME as TaskName, PR.PROJECT_ID, INC.OWNER_NAME 
FROM 
 SpiraTestEntities.R_Tasks as TK
INNER JOIN SpiraTestEntities.R_Incidents AS INC ON TK.OWNER_ID = INC.OWNER_ID
INNER JOIN SpiraTestEntities.R_Projects AS PR ON PR.PROJECT_ID = TK.PROJECT_ID
WHERE TK.PROJECT_ID = 1 AND TK.PROJECT_ID = INC.PROJECT_ID 
and INC.IS_DELETED = false
and TK.IS_DELETED = false
AND PR.IS_ACTIVE = TRUE
ORDER BY INC.OWNER_NAME

Do not forget to click to the Create Default Template before clicking Save.

You can also replace WHERE TK.PROJECT_ID = 1 with any other ProductId. Or you can query all the results for the currently selected Product by replacing WHERE TK.PROJECT_ID = 1 with TK.PROJECT_ID = ${ProjectId}.

Alternatively, use the special token ${ProjectGroupId} to limit the query results to a specific Program:

SELECT
 INC.INCIDENT_ID, INC.NAME, TK.TASK_ID, TK.NAME as TaskName, PR.PROJECT_ID, INC.OWNER_NAME 
FROM 
 SpiraTestEntities.R_Tasks as TK
INNER JOIN SpiraTestEntities.R_Incidents AS INC ON TK.OWNER_ID = INC.OWNER_ID
INNER JOIN SpiraTestEntities.R_Projects AS PR ON PR.PROJECT_ID = TK.PROJECT_ID 
       AND PR.PROJECT_ID = INC.PROJECT_ID
WHERE INC.IS_DELETED = false
and TK.IS_DELETED = false
and PR.IS_ACTIVE = true
AND TK.PROJECT_ID = ${ProjectId}
ORDER BY INC.OWNER_NAME

XSLT template for this modification of the ESQL query can be copied from the below section

<?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>INCIDENT_ID</th><th>NAME</th><th>TASK_ID</th><th>TaskName</th><th>PROJECT_ID</th><th>OWNER_NAME</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="INCIDENT_ID"/></td><td><xsl:value-of select="NAME"/></td><td><xsl:value-of select="TASK_ID"/></td><td><xsl:value-of select="TaskName"/></td><td><xsl:value-of select="PROJECT_ID"/></td><td><xsl:value-of select="OWNER_NAME"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

or you can just click Create XSLT Template next to Preview Result, in order to generate it automatically.

Click Save, then name the current section of the given report and click Save again.

The result should be similar to below given screenshot:

Creating a custom report to display all artifacts owned by users