Feature #11253

Total time spent from subtasks on the issue list

Added by Florian Schwab over 5 years ago. Updated over 2 years ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:Jean-Philippe Lang% Done:

0%

Category:Time tracking
Target version:3.1.1
Resolution:Fixed

Description

On the issue list the progress bar includes the progress of the subtasks and the estimated time is the sum of the estimates of the subtasks but the spent time is not aggregated.

Please see the attached screenshot for details.

issue_with_subtasks.png (97.4 KB) Florian Schwab, 2012-06-25 15:59

2012-06-26-102630_345x120_scrot.png - added times (7.71 KB) Aaron Mueller, 2012-06-26 10:40

Feature11253-0.patch Magnifier (1.54 KB) Ben Dalling, 2013-01-16 20:27

spent_time_on_parents_hack.patch Magnifier - #11253_vs_redmine230 (1.06 KB) Henrik Leon, 2013-04-27 19:32

spent_time_on_parents_hack_redmine251.patch Magnifier - #11253_vs_redmine251 (1.02 KB) Scott Cunningham, 2014-04-24 20:58

total_spent_time_view_all_issues.png - view all issues (patch installed - parent task with a total instead of 0.5 hours - good!) (20.5 KB) Scott Cunningham, 2014-04-24 21:49

total_spent_time_issue.png - issue view (spent time already totalling sub tasks - standard redmine) (50.2 KB) Scott Cunningham, 2014-04-24 21:50

total_spent_time_details.png - spent time details (patch installed - no duplication - looks good) (39.5 KB) Scott Cunningham, 2014-04-24 21:51

total_spent_time_report.png - spent time report (patch installed - no duplication - looks good) (12.4 KB) Scott Cunningham, 2014-04-24 21:52


Related issues

Related to Redmine - Feature #11087: Calculate Total Time Spent on Parent Issue Closed
Related to Redmine - Defect #20456: 3.1-stable/3.1.0: missing commits (omitted from being mer... Closed
Related to Redmine - Defect #17550: Spent time in exported CSV is wrong Closed
Related to Redmine - Defect #16159: Wrong calculation of spent time field on Issues report Closed
Duplicated by Redmine - Defect #11566: Spent Time isn't added on Issue List page Closed
Duplicated by Redmine - Defect #12610: sub tasks sent time is not counted on the issues list Closed
Duplicated by Redmine - Defect #12572: Wrong calculation of the spent time for issue with descen... Closed
Duplicated by Redmine - Defect #16363: Spent time in list view is not the same as in single tick... Closed
Duplicated by Redmine - Patch #14483: Add column "cumulated spent time" to issue filters Closed
Duplicated by Redmine - Feature #14384: Add column "spent time" on list issues Closed

Associated revisions

Revision 14406
Added by Jean-Philippe Lang over 2 years ago

Adds "Total spent hours" column available on the issue list (#11253).

Revision 14407
Added by Jean-Philippe Lang over 2 years ago

Preload total spent time on the issue list with 1 query (#11253).

Revision 14533
Added by Toshi MARUYAMA over 2 years ago

Merged r14406 from trunk to 3.1-stable (#11253, #20456)

Adds "Total spent hours" column available on the issue list.

Revision 14534
Added by Toshi MARUYAMA over 2 years ago

Merged r14407 from trunk to 3.1-stable (#11253, #20456)

Preload total spent time on the issue list with 1 query.

Revision 14535
Added by Toshi MARUYAMA over 2 years ago

Merged r14408 from trunk to 3.1-stable (#11253, #20456)

Limit queries to given issues only.

Revision 14536
Added by Toshi MARUYAMA over 2 years ago

Merged r14409 from trunk to 3.1-stable (#11253, #20456)

Fixes methods comments.

Revision 14537
Added by Toshi MARUYAMA over 2 years ago

Merged r14410 from trunk to 3.1-stable (#11253, #20456)

Asserts that spent time is preloaded.

Revision 14596
Added by Toshi MARUYAMA over 2 years ago

Merged r14405 from trunk to 3.1-stable (#11253, #20456)

Don't display estimated hours when nil and total is 0.

History

#1 Updated by Jean-Philippe Lang over 5 years ago

  • Tracker changed from Defect to Feature
  • Subject changed from Aggregated time spent from subtasks to Aggregated time spent from subtasks on the issue list
  • Category changed from Issues to Time tracking

#2 Updated by Aaron Mueller over 5 years ago

I have the same issue here. It's not a feature, its a inconsistency in the number displayed. On the ticket detail page, the already spent times on the sub-tickets gets added together (see screenshot). On the ticket list view, the column for the spent times are just for the single ticket.

To be consistent, the number in the ticket list view should be the same as in the ticket detail view.

#3 Updated by Victor Hugo Bilouro over 5 years ago

I use the following query to obtain the information.

select story.id,
       story.subject,
       max(story.estimated_hours) story__estimated,
       sum(subtask.estimated_hours) subtask__estimated,
       sum(te.hours) subtask__spent_time

  from issues story

  join issues subtask
    on subtask.parent_id = story.id

  left
  join (select project_id, 
               issue_id,
               sum(hours) hours
          from time_entries 
         where project_id = 1
         group 
            by project_id, 
               issue_id         
        ) te
    on te.issue_id = subtask.id
   and te.project_id = subtask.project_id

 where story.project_id = 1
   and story.tracker_id = 4

 group 
    by story.id,
       story.subject

#5 Updated by Sylvain Langlade over 5 years ago

Just in case it can help, a quick patch for v1.4.4. Just add the following

      @available_columns.insert index, QueryColumn.new(:total_spent_hours,
        :sortable => "(SELECT COALESCE(SUM(hours), 0) FROM #{TimeEntry.table_name} WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id)",
        :default_order => 'desc',
        :caption => :label_overall_spent_time
      )

to the app/models/query.rb file, at line 382 (just after the same code for the spent_hours column). Restart apache2 and it should add the desired column

Warning : it really is just a quick hack, if you look at the :sortable entry, the SQL code isn't what it should be. It works for issue list, but it may fails miserably for some other part of Redmine... --Update-- : well, I should have guessed, when you order the list by this overall spent time, it fails as it sorts by the non-overall spent time...

HTH.

#6 Updated by Sylvain Langlade over 5 years ago

As a side note : that's weird, with my redmine.org's account, I can change this issue status and its assignation ! Have I missed something ??

#7 Updated by Allan Story de Almeida Martins over 5 years ago

+1

i have this problem in redmine 2.0.3

#9 Updated by Allan Story de Almeida Martins over 5 years ago

i use idea of Sylvain Langlade with query of Victor Hugo Bilouro and worked

#10 Updated by Aaron Mueller over 5 years ago

Whats the current status of this task?
Is there a chance to get this into the 2.2.0 release?

#11 Updated by Ben Dalling about 5 years ago

+1

#12 Updated by Evgeny Zhiryakov about 5 years ago

+All our company

#13 Updated by Ben Dalling about 5 years ago

Is anything going to be done about this anytime soon? This "feature" is really blocking useful reporting within my organisation. A fix so that the time estimated and time spent is calculated consistently would be greatly appreciated. I would also far prefer to update my Redmine instance from an official source rather than hack/configure my existing production system.

#14 Updated by Ovidiu Stanciu about 5 years ago

+1

Based on Sylvain's suggestion, I've managed to fix this issue on our v2.1.4 installation.

  • app/models/query.rb line 406:
    @available_columns.insert index, QueryColumn.new(:total_spent_hours,
      :sortable => "(SELECT COALESCE(SUM(hours), 0) FROM #{TimeEntry.table_name} WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id OR #{TimeEntry.table_name}.issue_id IN (SELECT child_issue.id FROM #{Issue.table_name} child_issue WHERE child_issue.parent_id = #{Issue.table_name}.id))",
      :default_order => 'desc',
      :caption => :label_spent_time
    )
    
  • app/helpers/queries_helper.rb line 59 (format the decimal value):
    elsif  column.name == :spent_hours || column.name == :total_spent_hours
      sprintf "%.2f", value
    

The total spent time per issue (including time spent on child issues) is nicely show in the issues list, sorting (taking into account only time spent on first level children) and exporting also work.

There is a smarter way to apply this fix rather than directly modifying Redmine scripts, but this is beyond me since I have almost zero experience with Ruby and RoR.

#15 Updated by Daniel Felix about 5 years ago

Ovidiu Stanciu wrote:

  • app/models/query.rb line 406:
    [...]

Hi,

this should be also achieved with this:

SELECT COALESCE(SUM(hours), 0) 
FROM #{TimeEntry.table_name} time_entry
LEFT JOIN #{Issue.table_name} child_issue  
        ON time_entry.issue_id = child_issue.id
WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id 
OR child_issue.parent_id IS NOT NULL

IN could be very slow in some cases with many subissues.

#16 Updated by Ben Dalling about 5 years ago

I have edited the code on our 2.1.2 system as a workaround for this problem. Many thanks to Victor, Sylvain, Ovidiu and Daniel (authors of notes 3, 5, 14 and 15 respectively) for providing the information to do this.

I am surprised to see that this issue (which is a definite hindrance to accurate reporting) has not yet become a release candidate after seven months of being initially reported.

#17 Updated by Daniel Felix about 5 years ago

  • Target version set to Candidate for next major release

@Ben
Can you provide a combined patch for this?

#18 Updated by Ben Dalling about 5 years ago

@Daniel,

I've never patched Redmine (apart from my previous hack) and I know next to nothing about Ruby, so if this is wrong, please let me know.

For this patch I downloaded the latest stable version (2.2.1), applied and tested the changes again. I then generated the attached patch file by following the guide at http://www.redmine.org/projects/redmine/wiki/Patch.

One side effect of this change is that any custom queries that referred to "Spent time" need to have the column re-added. If anybody can figure out a way around this, please let me know.

#19 Updated by Ben Dalling almost 5 years ago

Hi,

What else needs to be done with this feature to make it go into a scheduled release? It would be good to have this incorportated into a production version of Redmine ASAP.

Best wishes,

Ben

#20 Updated by Ovidiu Stanciu almost 5 years ago

Please take into account that both fixes in query.rb (mine and Daniel's) only take into account time spent on direct descendants when sorting. In case there are multiple levels, the order won't be consistent with the values displayed.

Example :

ISSUE_ID PARENT_ID TIME SPENT
1
2 1 10
3
4 3 5
5 4 10

If displaying only top level issues and sorting ascending by total time spent:

ISSUE_ID TOTAL TIME SPENT
3 15
1 10

15 comes before 10 because the query fails to take into account the 10 hours spent on issue 5, which is a third level child.

I imagine this is pretty hard to achieve in a single query and I think there should be some recursion involved, as when calculating :total_spent_hours on an issue.

This should be addressed in order to achieve a complete fix.

Best regards,
Ovidiu Stanciu

#21 Updated by Henrik Leon over 4 years ago

+1
running 2.3.0, was really hoping for this fix.

#22 Updated by Henrik Leon over 4 years ago

My first attempt on coding in redmine (and RoR), I'm not trained in software. Patch is same as Ben's above, but changed to work on 2.3.0.

#23 Updated by VD DV over 4 years ago

+1
I suppose that it will be the best solution to have two issue attributes for "Spent Time" that should be visible at Issues List and Issue details.
One of those two attributes might be "Spent Time" that should display time spent on single issue (without Time logs from child issues).
Another attribute might be "Cumulative Spent Time" that should contain all time logs from selected issue and all childs/grandchilds/grand...grandchilds.

#24 Updated by Ben Dalling over 4 years ago

I've just implemented Henrik's patch on 2.3.1 and it works. It also meets Ovidiu's criteria in that a change of spent time in the sub-task of a sub-task is reflected in the parent.

#25 Updated by David Lukas Müller over 4 years ago

I'm using Redmine 2.3.1. The fix from Bed Dalling (note-24) seams to work for the Web User Interface (HTML). Thank youGreat work!

Anyway there still seams to be a minor problem within the REST API (JSON) regarding the same or a similar problem:
  • Currently the estimated_hours of the parent issue is the sum of the child issues (in REST API as well as in Web Interface). --> good
  • But the spent_hours are accumulated in the Web Interface but not in the REST API --> could be improved

Fixing that minor inconsistency for estimated_hours and spent_hours would simplify matters, when generating project reports using the REST API (JSON) - if the project makes use of parent issues to model a Work Breakdown Structure.

In addition "VD DV"s proposal in note-23 could further simpliy matters.

See #5303#note-10 for another issue regarding REST API and spent_hours.

Best Regards,
David

#26 Updated by Vu Hong about 4 years ago

I'm using Redmine 2.3.3 and tried Henrik's patch. But it didn't work and the spend time column was disappeared.
Please help me....

UPDATED: It's working
I forgot add spent time in option view of issues list.
Be lucky.

#27 Updated by Vu Hong about 4 years ago

But when I sorted a spent time column, It displayed Error 500.
How can I fix it??
Thanks.

#28 Updated by Daniel Felix about 4 years ago

To solve such a bug, we need more info. Error 500 is nothing which helps us. Please provide corresponding log entries.

#29 Updated by Anton Statutov almost 4 years ago

This issue is also actual for spent time reports.

#30 Updated by Maxime Vez almost 4 years ago

+1 please fix this inconsistency

#31 Updated by Toshi MARUYAMA almost 4 years ago

  • Duplicated by Defect #16363: Spent time in list view is not the same as in single ticket view added

#32 Updated by Scott Cunningham over 3 years ago

I'm using Redmine 2.5.1 and used Henrik Leon's patch. I've uploaded my diff file as the line numbers are different (code is the same, just different line numbers).

For me this patch is useful because I use sub tasks to clearly define the steps to be done for small jobs which occur often and require specific steps per our ISO9001 procedures without creating a separate project every time. This way, a main request can be generated by a user with reporter roles and I can copy sub tasks to the request. Time logged now shows on the main request and I can filter out the tiny tasks to clearly show what requests have been handled and at what cost in time.

If you don't already know:
  • Keep a copy of your original issue_query.rb file.
  • Restart your servers - if redmine servers keep going down, then there is an error in the file.
  • After installing the patch, the Spent Time column will be gone. You must go back into Admin -> Settings -> Task Tracking and add the column back to your table column preference.
  • If you log time on the parent task, it will also add that time into the total.

Patch: spent_time_on_parents_hack_redmine251.patch

Screenshot: total_spent_time_view_all_issues.png
Screenshot: total_spent_time_issue.png
Screenshot: total_spent_time_details.png
Screenshot: total_spent_time_report.png

Please note I have not heavily tested this patch. It could certainly have side effects, but for me, it looks like it's working as I wish.

#33 Updated by Enmos Proje over 3 years ago

+1

#34 Updated by Okke Hendriks over 3 years ago

+1

#35 Updated by WDS D almost 3 years ago

+1

#36 Updated by Malcolm Thompson over 2 years ago

We've just installed Redmine 3.0.3 and we're having the same problem - is there a patch for this version?

#37 Updated by Jean-Philippe Lang over 2 years ago

  • Subject changed from Aggregated time spent from subtasks on the issue list to Total time spent from subtasks on the issue list
  • Status changed from New to Closed
  • Assignee set to Jean-Philippe Lang
  • Target version changed from Candidate for next major release to 3.1.0
  • Resolution set to Fixed

Feature added for 3.1.0. There are now 2 columns available: "Spent time" and "Total spent time". The second one aggregates time spent on the issue and its descendants.

#38 Updated by Ryosuke Hirai over 2 years ago

Thanks for this great feature, Jean-Philippe!

it seems that issue_query.rb @ Revistion 14406 was not applied to the source code of 3.1.0. I could not find this part after downloading.
Let me add: Having applied this part by myself, I was able to see "total spent time" column in the query list. This is a great feature in deed.

#39 Updated by Mischa The Evil over 2 years ago

  • Related to Defect #20456: 3.1-stable/3.1.0: missing commits (omitted from being merged from trunk) added

#40 Updated by Mischa The Evil over 2 years ago

Ryosuke Hirai wrote:

it seems that issue_query.rb @ Revistion 14406 was not applied to the source code of 3.1.0. I could not find this part after downloading.

Good catch ;) Redmine 3.1.0 is indeed missing this feature due to the fact that the related commits weren't merged in source:/branches/3.1-stable when 3.1.0 got tagged. They actually still aren't. I've reported these issues in #20456.

Thanks for reporting this omission. Mischa.

#41 Updated by Toshi MARUYAMA over 2 years ago

I have merged trunk r14406 to r14410 to 3.1-stable.

#42 Updated by Jean-Philippe Lang over 2 years ago

  • Target version changed from 3.1.0 to 3.1.1

#43 Updated by Go MAEDA over 2 years ago

  • Duplicated by Patch #14483: Add column "cumulated spent time" to issue filters added

#44 Updated by Go MAEDA about 2 years ago

  • Duplicated by Feature #14384: Add column "spent time" on list issues added

#45 Updated by Go MAEDA about 1 year ago

  • Related to Defect #17550: Spent time in exported CSV is wrong added

#46 Updated by Go MAEDA about 1 year ago

  • Related to Defect #16159: Wrong calculation of spent time field on Issues report added

Also available in: Atom PDF