Task Analysis by Type of Tasks

Overview

Every project can specific the types of tasks that are relevant to the project. While evaluating tasks, the progress of the task type also can provide significant insight into potential project schedule delays and other impediments. Whether a project is plan-driven, adaptive, or hybrid, looking into these task type level analysis can build the project team to self-organize and be proactive in avoid project delays. 

Task Type Setup in Spira

In Spira, the Task Type is set up as part of the template used for the product. Shown below is where the task type can be accessed and set up.

Query 

Given below is the Query

select 
   T.TASK_TYPE_NAME, 
   SUM(T.NOT_STARTED) as NS_COUNT, 
   SUM(T.IN_PROGRESS) as WIP_COUNT 
from
 (
  (select 
    Case when R.TASK_TYPE_NAME is null then "Unknown Type" else R.TASK_TYPE_NAME end as TASK_TYPE_NAME,
    COUNT(R.TASK_ID) as Not_Started, 
    0 as In_Progress 
   from 
    SpiraTestEntities.R_Tasks as R 
   where 
    R.PROJECT_ID = ${ProjectId} and 
    R.IS_DELETED = False and 
    R.TASK_STATUS_NAME ="Not Started"
   group by 
     R.TASK_TYPE_NAME)
   union
   (select
     Case when R.TASK_TYPE_NAME is null then "Unknown Type" else R.TASK_TYPE_NAME end as TASK_TYPE_NAME,
     0 as Not_Started, 
     COUNT(R.TASK_ID) as In_Progress 
    from 
     SpiraTestEntities.R_Tasks as R 
    where 
     R.PROJECT_ID = ${ProjectId} and 
     R.IS_DELETED = False and  
     R.TASK_STATUS_NAME ="In Progress"
    group by R.TASK_TYPE_NAME)
 ) as T
group by  
  T.TASK_TYPE_NAME

Query Explanation

1. This query combines inner queries. 
2. The first inner query focuses on counting the tasks associated by the task type for tasks in the not started state. It assigns 0 to the in progress state.
3. The second inner query focuses on counting the tasks associated by the task type for tasks in the in progress state. It assigns 0 to the not started state.
4. The outer query summarizes the results of the two inner queries.
5. The where clause in each inner query 
   1. filters for specific status (e.g.: TASK_STATUS_NAME = "In Progress")
   2. picks tasks that are not deleted (IS_DELETED = False)
   3. applies the current project selection (PROJECT_ID = ${ProjectId})

Output

Given below is the resulting data.

Graph 

Given below is the graph.

Graph Interpretation

1. The lean principle of flow preservation in any project requires why tasks remain in the WIP state. For the various task types mentioned, this may provide insights related to the direction and self-organization required for the people dimension as well as the type of processes (overhead or missing process) and compliance needed. While the graphs themselves will not give the reasons, it prompts one to use these KPIs to dig deeper. 
2. The next focus is also understanding why some tasks are waiting to be started. Analyzing the root causes may lead one to get detailed in the documentation (e.g.: Agreeing on a formal definition of done, incorporating the INVEST principle, business objective clarity, etc.). 
3. Further more, this analysis can also lead into whether the existing categories of tasks are sufficient, too granular (to be condensed) or needs to be refined (compliance, for instance, as an additional task type). Accordingly, the processes followed and emulated using workflows in Spira may also be refined or simplified.