Great report for looking at Team Member Load

[SteveRC1]

We started using Agilo a few months ago and have just started our 3rd sprint.  It is working well for us.  The one thing that we wanted was to have a better look at how team members were doing with their commitments during the sprint.

Our dev lead created a report sql query that the team now relies on.  I though others may like to use it too.

I think it only works with Postgres though.

Hope you find it useful

Steve

Just create a new report with the sql below;

select 

   ( CASE WHEN (remaining_capacity.time_remaining = 0) THEN 

        'background: PaleGreen;'   

     WHEN round((remaining_commitment.time_remaining /remaining_capacity.time_remaining)::numeric,2) < .95 THEN

         'background: LightGoldenRodYellow;'

     WHEN round((remaining_commitment.time_remaining /remaining_capacity.time_remaining)::numeric,2) < 1.05 THEN

         'background: PaleGreen;'

     ELSE 'background: Pink;' END) as __style__,

   remaining_commitment.team_member as "Team Member", 

   round(remaining_capacity.time_remaining::numeric,2) as "Capacity remaining (hours)", 

   round(remaining_commitment.time_remaining::numeric,2) as "Commitment Remaining (hours)",

   round((remaining_capacity.time_remaining - remaining_commitment.time_remaining)::numeric,2) as "Hours Remaining",

   CASE WHEN (remaining_capacity.time_remaining = 0) THEN 1.00

     ELSE round((remaining_commitment.time_remaining /remaining_capacity.time_remaining)::numeric,2) END as Load

from (

SELECT

  ticket."owner" as team_member,

  sum(ticket_custom."value"::real) as time_remaining

FROM 

   public.ticket

join 

  ticket_custom

  on public.ticket.id = ticket_custom.ticket and

   ticket_custom."name" = 'remaining_time' and

   ticket_custom."value" <> '' and

   ticket_custom.ticket in 

      (select ticket from ticket_custom 

       where ticket_custom."name" = 'sprint' and

       ticket_custom."value" = (select "name" from agilo_sprint where 

      current_date between (timestamp with time zone 'epoch' + "start" * interval '1 second')::date and 

      (timestamp with time zone 'epoch' + sprint_end * interval '1 second')::date) and

      ticket.type in ('task','bug')

      )

      GROUP by team_member

      ) as remaining_commitment

left join 

(select sprint_data.interation_name, agilo_team_member."name" as team_member, 

   sum( 

   (case 

      when exception_dates.hours is not null then exception_dates.hours

      when extract(DOW from dates.a) = 1 then agilo_team_member.ts_mon

      when extract(DOW from dates.a) = 2 then agilo_team_member.ts_tue

      when extract(DOW from dates.a) = 3 then agilo_team_member.ts_wed

      when extract(DOW from dates.a) = 4 then agilo_team_member.ts_thu

      when extract(DOW from dates.a) = 5 then agilo_team_member.ts_fri

      when extract(DOW from dates.a) = 6 then agilo_team_member.ts_sat

      when extract(DOW from dates.a) = 7 then agilo_team_member.ts_sun

      else 0

      end) *

      (case when (dates.a = current_date) and (current_time between '09:00:00'::time and '17:00:00'::time) 

      then 1 - (extract(EPOCH from current_timestamp) - extract(EPOCH from current_date + '09:00:00'::time))/(extract(EPOCH from current_date + '17:00:00'::time) - extract(EPOCH from current_date + '09:00:00'::time)) 

       else 1 end)

      ) as time_remaining

from generate_series(current_date, current_date+50,'1 day') as dates(a)

join 

   (select (timestamp with time zone 'epoch' + "start" * interval '1 second')::date as sprint_start_date, 

      (timestamp with time zone 'epoch' + sprint_end * interval '1 second')::date as sprint_end_date, team, "name" as interation_name 

      from agilo_sprint where 

      current_date between (timestamp with time zone 'epoch' + "start" * interval '1 second')::date and 

      (timestamp with time zone 'epoch' + sprint_end * interval '1 second')::date) as sprint_data

   on dates.a between sprint_data.sprint_start_date and sprint_data.sprint_end_date

join agilo_team_member

   on agilo_team_member.team = sprint_data.team

left join 

   (select teammember, '0001-01-01'::date + date -1 as real_date, hours

      FROM agilo_calendar_entry) as exception_dates

   on exception_dates.teammember = agilo_team_member."name" and exception_dates.real_date = dates.a::date

group by sprint_data.interation_name, agilo_team_member."name") as remaining_capacity

on remaining_capacity.team_member = remaining_commitment.team_member 

order by remaining_commitment.team_member

[Stefano Rago]

Hi Steve,

On 03/mag/2012, at 22:12, SteveRC1 wrote:

We started using Agilo a few months ago and have just started our 3rd sprint.  It is working well for us.  The one thing that we wanted was to have a better look at how team members were doing with their commitments during the sprint.

Our dev lead created a report sql query that the team now relies on.  I though others may like to use it too.

Thanks a lot for sharing this!

Stefano Rago