Why do we need this analysis?

The important thing about task is that they represent activities signifying the 'Definition of Done'. These activities can be part of a known release that contains iterations/sprints or the committed iterations. Frequently task analysis is limited to a release or iteration.

However, it is also possible that backlog refinement activities is required to be done so that the requirements or the user stories are detailed appropriately for the team to engage in affinity estimation (right sizing using T-shirt or Coffee cup approaches). Only then, the epics are disaggregated into features and the features are broken further into multiple user stories or large user stories split into smaller stories to facilitate adequate prioritization in sprint/iteration planning. This level of analysis focuses on the 'Definition of Ready' and is equally important. When tasks are not identified outside of any release or iteration or identified tasks not completed, then, this also impacts the productivity of the team or questions the value of the work delivered in releases and iterations.

SQL Query

This query is slightly involved. Please review the Query Explanation for details.

select 
   case when TT.RELEASE_ID = 0 then "Unknown Release" else R.NAME end as NAME, 
   SUM(TT.COMPLETED) as DONE,
   SUM(TT.NOT_STARTED) as NO, 
   SUM(TT.IN_PROGRESS) as WIP,
   SUM(TT.TOTAL) as TOTAL
from
(
  (select 
     Case when TK.RELEASE_ID is null then 0 else TK.RELEASE_ID end as RELEASE_ID, 
     TK.TASK_STATUS_NAME, 
     COUNT(TK.TASK_ID) as Not_Started, 0 as In_Progress, 0 as Completed, 0 as Total
    from 
     SpiraTestEntities.R_Tasks as TK
    where 
      TK.PROJECT_ID = ${ProjectId} and 
      TK.TASK_STATUS_NAME = 'Not Started' and 
      TK.IS_DELETED = False
    group by 
      TK.RELEASE_ID, TK.TASK_STATUS_NAME)
  union
   (select 
     Case when TK.RELEASE_ID is null then 0 else TK.RELEASE_ID end as RELEASE_ID, 
     TK.TASK_STATUS_NAME, 
     0 as Not_Started, COUNT(TK.TASK_ID) as In_Progress, 0 as Completed, 0 as Total
    from 
      SpiraTestEntities.R_Tasks as TK
    where 
       TK.PROJECT_ID = ${ProjectId} and 
       TK.TASK_STATUS_NAME = 'In Progress' and 
       TK.IS_DELETED = False
    group by 
       TK.RELEASE_ID, TK.TASK_STATUS_NAME)
  union
    (select  
      Case when TK.RELEASE_ID is null then 0 else TK.RELEASE_ID end as RELEASE_ID, 
      TK.TASK_STATUS_NAME, 
      0 as Not_Started, 0 as In_Progress, COUNT(TK.TASK_ID) as Completed, 0 as Total
     from 
       SpiraTestEntities.R_Tasks as TK
     where 
       TK.PROJECT_ID = ${ProjectId} and 
       TK.TASK_STATUS_NAME = 'Completed' and 
       TK.IS_DELETED = False
     group by 
       TK.RELEASE_ID, TK.TASK_STATUS_NAME)
   union
     (select 
       Case when TK.RELEASE_ID is null then 0 else TK.RELEASE_ID end as RELEASE_ID, 
       "Total" as TASK_STATUS_NAME, 
       0 as Not_Started, 0 as In_Progress, 0 as Completed, COUNT(TK.TASK_ID) as Total
      from 
        SpiraTestEntities.R_Tasks as TK
      where 
        TK.PROJECT_ID = ${ProjectId} and 
        TK.IS_DELETED = False
      group by 
       TK.RELEASE_ID, TK.TASK_STATUS_NAME)) as TT
left join SpiraTestEntities.R_Releases as R on 
    R.PROJECT_ID = ${ProjectId} and TT.RELEASE_ID = R.RELEASE_ID
group by 
   TT.RELEASE_ID, R.NAME
having 
   sum(TT.TOTAL) > 10
order by 
   TOTAL desc

Query Explanation

  1. This query is made of multiple similar subqueries joined by the union statement with an outer query for summing the results and filtering the results using the having clause.
  2. Each inner subquery checks in the SELECT clause if tasks are assigned to a release and brings 0 when tasks are not assigned to any release so that this information can be manipulated in the outer query.
  3. While each inner subquery counts the number of tasks in the SELECT clause, the WHERE clauses applies
    1. filtering for specific status (TASK_STATUS_NAME = 'Not Started') except for the last inner subquery to get the total without a status.
    2. picking tasks that are not deleted (IS_DELETED = False)
    3. applying the current project selection (PROJECT_ID = ${ProjectId})
    4. Groups the result as identified in the select clause
  4. The outer query translates RELEASE_ID = 0 to a text "Unknown Release" and sums the completed task count as DONE, Not Started task counts as NO, In Progress task counts as WIP and the total counts TOTAL.
  5. The outer query also applies
    1. Groups results according to the SELECT clause fields
    2. Applies the Having SQL clause to apply pareto thoughts on where the maximum focus should apply (e.g.: Where TASK COUNTS > 10 so that one can dig deeper in to the behavioral patterns contributing to executing the required product/project activities.
    3. The results are ordered by total in the descending order for analysis.

Results Output

Given below is the results of this query.

Creating a Custom Report

If you wanted this data to be present in a report form, follow the steps below.

  1. Use the "Edit Reports" section under the System Administration (You should have the appropriate permissions)
  2. Create a New Report
  3. Fill the details like name, description, category, format, etc.
  4. Click on the "Add new Custom Section" under the Custom Section 
  5. Give a name to the custom section at a minimum
  6. Fill the query section with SQL Query from the above section 
  7. Preview the Results
  8. Click on "Create Default Template"
  9. Modify the XSLT template if desired for additional customization (Optional)
  10. Save the custom section and the Custom Report (Yes, two Save buttons must be clicked) 

Graph Output

When this data is represented, it can be represented either as bar graph or line graph as shown below.

Bar GraphBar Chart
Line GraphLine Graph

 

Good Practices - Graph Interpretation

  1. While both the bar graph and the line graph equally represent the information clearly, it is possible that the review team look at the "TOTAL" column alone in the bar chart and be concerned. Since the total represents Not Started, In Progress, and Completed, it only represents the volume of work. So, the management review can focus on whether too much work is taken on in a sprint (prioritization inflation, estimation accuracy, etc.) or why too much backlog refinement is not happening in the unassigned releases. 
  2. If the focus is not the volume of work, then the line graph can be seen where it is possible to quickly remove the TOTAL line (which always will be at the top because it aggregates the information) from consideration and focus on the Not Started and In Progress areas.