Display Report of Task, Incident, Test Case Effort by Person

Creating the Custom Report

The following Entity SQL (ESQL) should be used in the Spira custom report writer:

select GP.User_Name, sum(GP.Test_Case_Effort) as Test_Case_Effort, sum(GP.Task_Effort) as Task_Effort, sum(GP.Incident_Effort) as Incident_Effort
from
((select US.FIRST_NAME + ' ' + US.LAST_NAME as User_Name, sum(TC.ESTIMATED_DURATION) as Test_Case_Effort, 0 as Task_Effort, 0 as Incident_Effort
from SpiraTestEntities.R_TestCases as TC
inner join SpiraTestEntities.R_Users as US on TC.OWNER_ID = US.USER_ID
where TC.PROJECT_ID = ${ProjectId} and TC.IS_DELETED = False
group by US.FIRST_NAME, US.LAST_NAME)
union all
(select US.FIRST_NAME + ' ' + US.LAST_NAME as User_Name, 0 as Test_Case_Effort, sum(TK.PROJECTED_EFFORT) as Task_Effort, 0 as Incident_Effort
from SpiraTestEntities.R_Tasks as TK
inner join SpiraTestEntities.R_Users as US on TK.OWNER_ID = US.USER_ID
where TK.PROJECT_ID = ${ProjectId} and TK.IS_DELETED = False
group by US.FIRST_NAME, US.LAST_NAME)
union all
(select US.FIRST_NAME + ' ' + US.LAST_NAME as User_Name, 0 as Test_Case_Effort, 0 as Task_Effort, sum(IC.PROJECTED_EFFORT) as Incident_Effort
from SpiraTestEntities.R_Incidents as IC
inner join SpiraTestEntities.R_Users as US on IC.OWNER_ID = US.USER_ID
where IC.PROJECT_ID = ${ProjectId} and IC.IS_DELETED = False
group by US.FIRST_NAME, US.LAST_NAME)) as GP
group by GP.User_Name

Then click the 'Create Default Template' option to generate the following XSLT report 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>User_Name</th><th>Test_Case_Effort</th><th>Task_Effort</th><th>Incident_Effort</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="User_Name"/></td><td><xsl:value-of select="Test_Case_Effort"/></td><td><xsl:value-of select="Task_Effort"/></td><td><xsl:value-of select="Incident_Effort"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

Viewing the Report

If you run this report in Excel or HTML format, it will generate the following output:

Note that we are not filtering by productor release. You can add an appropriate set of where clauses to add that in, if you want to limit the data by release or product.