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.
<https://lh6.googleusercontent.com/-jyLdWm0oBF0/T6LmlXn-MnI/AAAAAAAAFOU/TpFOv-YrnI4/s1600/sample_report.png> 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 -- Follow Agilo on Twitter: http://twitter.com/agilofortrac Please support us by reviewing and voting on: http://userstories.com/products/8-agilo-for-scrum http://ohloh.net/p/agilo-trac http://freshmeat.net/projects/agiloforscrum You have received this message because you are subscribed to the "Agilo for Trac" Google Group. This group is focused on supporting Agilo for Trac users and is moderated by Agilo Software GmbH <http://www.agilosoftware.com>. To post to this group, send email to [email protected] To unsubscribe from this group, send an email to [email protected] For more options, visit this group at http://groups.google.com/group/agilo
