Definition of Cycle Time and Lead Time

  • In general, when a requirement (e.g.: user story) is identified by a team, it enters the product backlog.  Let us say the date any requirement is identified is called "CREATION_DATE". 
  • However, the requirements go through iteration (or Sprint) planning where it is prioritized. When a requirement is selected for an iteration to be worked on,  another date is required to denote when the team began working on the requirement. This date is called "START_DATE".
  • Typically, the Agile teams finish the work on a user story by the end of the iteration but sometimes it may be parked (due to risks or other project events) or the work on that requirement may be finished earlier than the release date (e.g.: release on demand functionality).  Then  there is a third date field required to capture the  date when work on that requirement was completed. This date is called the "END_DATE" or the "CLOSED_DATE" if the item is closed (See following section below)
  • Lead Time is  identified as the time it took for any item to be completed from the time it was identified. So, lead time is the difference between CREATION_DATE and END_TIME.
  • Cycle Time is identified as the time it took for any item  to be completed from the time actual work on that item was started. So, cycle time is the difference between START_DATE and END_DATE.

Prerequisites in Spira

  • Spira supports all these different dates .  However, in order to compute these metrics like Lead Time and Cycle Time, the workflow status should fill these dates appropriately. The CREATION_DATE for requirement, task, and defect are automatically created when these artifacts are created in Spira.
  • But, the updates of START_DATE and END_DATE in Spira are updated differently due to the Agile approaches followed. 
  • For requirement, the START_DATE and END_DATE are associated with the release 
  • For tasks, the START_DATE and END_DATE are associated with the task itself.
  • For Defects, the START_DATE is associated with the incident itself but the END_DATE is associated with the CLOSED_DATE as the defects are often closed prior to the release they are associated with or sometimes not associated with a release at all (based on business practices). 

The SQL Query

Let us start with the simple query illustrating how the lead time and cycle time are computed for tasks because all the dates are self-contained within the task artifact itself. Please note that we can get more sophisticated in this query by using case statements bringing only those data that meets certain statuses but for simplicity these are not applied.

select 
  R.TASK_ID, 
  R.NAME, 
  R.CREATION_DATE, 
  R.START_DATE, 
  R.END_DATE,  
  DiffDays(R.CREATION_DATE, R.END_DATE) as Lead_Time, 
  DiffDays(R.START_DATE, R.END_DATE) as Cycle_Time, 
  R.TASK_STATUS_NAME 
from SpiraTestEntities.R_Tasks as R 
where R.PROJECT_ID = ${ProjectId} and 
    R.IS_DELETED = False and 
    R.TASK_STATUS_NAME = "Completed" and
    R.START_DATE is not null and 
    R. END_DATE is not null

 

Query Explanation

  1. In this query, we are bringing the required fields from the Task artifact directly. 
  2. For Lead Time, we are using the Microsoft ESQL supported DiffDays to compute the number of days between CREATION_DATE and START_DATE.
  3. For Cycle Time, we are using the Microsoft ESQL supported DiffDays to compute the number of days between START_DATE and END_DATE. 
  4. Both Lead and Cycle time apply only when the task has reached its final status (i.e. Completed in this workflow)
  5. Depending upon the fields required as mandatory in the workflow, start_date and end_date may not be filled. If they are not filled, these metrics can't be computed. So, the start_date is not null and end_date is not null eliminates those data.
  6. The Is_Deleted = False removes tasks that are deleted and no longer relevant for this query.
  7. The PROJECT_ID = ${ProjectId} applies the current project selected. If this clause is removed, cross-project tasks will be retrieved. In such cases, the project related information  such as project_id also should be brought to know which project the tasks belong to.  Join with Projects table to get the project related information like the project name.

 

Creating the Default Template

  • In Spira, after confirming that the query works as desired, create the default XSLT template. 
  • If you want to make the dates appear in a specific date format, like "DD-MON-YYYY" format, add the appropriate XSLT template formatting around the date fields.
<td class="Date">
   <xsl:call-template name="format-date">
   <xsl:with-param name="datetime" select="CREATION_DATE" />
   </xsl:call-template>
</td>
  • The specific XSLT format for converting the date to "DD-MON-YYYY" format is below.
<xsl:template name="format-date">
    <xsl:param name="datetime"/>
    <xsl:variable name="date" select="substring-before($datetime, 'T')" />
    <xsl:variable name="year" select="substring-before($date, '-')" />
    <xsl:variable name="month" select="substring-before(substring-after($date, '-'), '-')" />
    <xsl:variable name="day" select="substring-after(substring-after($date, '-'), '-')" />
    <xsl:variable name="time" select="substring-before(substring-after($datetime, 'T'), '.')" />
    <xsl:variable name="monthname">
      <xsl:choose>
        <xsl:when test="$month='01'">
          <xsl:value-of select="'Jan'"/>
        </xsl:when>
        <xsl:when test="$month='02'">
          <xsl:value-of select="'Feb'"/>
        </xsl:when>
        <xsl:when test="$month='03'">
          <xsl:value-of select="'Mar'"/>
        </xsl:when>
        <xsl:when test="$month='04'">
          <xsl:value-of select="'Apr'"/>
        </xsl:when>
        <xsl:when test="$month='05'">
          <xsl:value-of select="'May'"/>
        </xsl:when>
        <xsl:when test="$month='06'">
          <xsl:value-of select="'Jun'"/>
        </xsl:when>
        <xsl:when test="$month='07'">
          <xsl:value-of select="'Jul'"/>
        </xsl:when>
        <xsl:when test="$month='08'">
          <xsl:value-of select="'Aug'"/>
        </xsl:when>
        <xsl:when test="$month='09'">
          <xsl:value-of select="'Sep'"/>
        </xsl:when>
        <xsl:when test="$month='10'">
          <xsl:value-of select="'Oct'"/>
        </xsl:when>
        <xsl:when test="$month='11'">
          <xsl:value-of select="'Nov'"/>
        </xsl:when>
        <xsl:when test="$month='12'">
          <xsl:value-of select="'Dec'"/>
        </xsl:when>
        <xsl:otherwise>
          <xsl:value-of select="''" />
        </xsl:otherwise>
      </xsl:choose>
    </xsl:variable>
    <xsl:value-of select="concat($day, '-' ,$monthname, '-', $year , ' ', $time)" />
  </xsl:template>
  • Save the custom query section and the report.

Report Output

Given below is the way this report would render

LeadTime and CycleTime Report Output

Please note that sometimes the business practices may make computing the lead time difficult and will require reviewing some of the data to address these inconsistencies where possible.