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

In this article, we will create a custom report that displays a list of users in the system and the associated effort for tasks, incidents and test cases.

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: