Project

General

Profile

Actions

Feature #11253

closed

Total time spent from subtasks on the issue list

Added by Florian Schwab over 12 years ago. Updated about 9 years ago.

Status:
Closed
Priority:
Normal
Category:
Time tracking
Target version:
Start date:
Due date:
% Done:

0%

Estimated time:
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.


Files

issue_with_subtasks.png (97.4 KB) issue_with_subtasks.png Florian Schwab, 2012-06-25 15:59
2012-06-26-102630_345x120_scrot.png (7.71 KB) 2012-06-26-102630_345x120_scrot.png added times Aaron Fischer, 2012-06-26 10:40
Feature11253-0.patch (1.54 KB) Feature11253-0.patch Ben Dalling, 2013-01-16 20:27
spent_time_on_parents_hack.patch (1.06 KB) spent_time_on_parents_hack.patch #11253_vs_redmine230 Henrik Leon, 2013-04-27 19:32
spent_time_on_parents_hack_redmine251.patch (1.02 KB) spent_time_on_parents_hack_redmine251.patch #11253_vs_redmine251 Scott Cunningham, 2014-04-24 20:58
total_spent_time_view_all_issues.png (20.5 KB) total_spent_time_view_all_issues.png view all issues (patch installed - parent task with a total instead of 0.5 hours - good!) Scott Cunningham, 2014-04-24 21:49
total_spent_time_issue.png (50.2 KB) total_spent_time_issue.png issue view (spent time already totalling sub tasks - standard redmine) Scott Cunningham, 2014-04-24 21:50
total_spent_time_details.png (39.5 KB) total_spent_time_details.png spent time details (patch installed - no duplication - looks good) Scott Cunningham, 2014-04-24 21:51
total_spent_time_report.png (12.4 KB) total_spent_time_report.png spent time report (patch installed - no duplication - looks good) Scott Cunningham, 2014-04-24 21:52

Related issues

Related to Redmine - Feature #11087: Calculate Total Time Spent on Parent IssueClosed

Actions
Related to Redmine - Defect #20456: 3.1-stable/3.1.0: missing commits (omitted from being merged from trunk)ClosedJean-Philippe Lang

Actions
Related to Redmine - Defect #17550: Spent time in exported CSV is wrongClosed

Actions
Related to Redmine - Defect #16159: Wrong calculation of spent time field on Issues reportClosed

Actions
Related to Redmine - Defect #10527: time_spent does not sort properly for this caseClosed

Actions
Has duplicate Redmine - Defect #11566: Spent Time isn't added on Issue List pageClosed

Actions
Has duplicate Redmine - Defect #12610: sub tasks sent time is not counted on the issues listClosed

Actions
Has duplicate Redmine - Defect #12572: Wrong calculation of the spent time for issue with descendantsClosed

Actions
Has duplicate Redmine - Defect #16363: Spent time in list view is not the same as in single ticket viewClosed

Actions
Has duplicate Redmine - Patch #14483: Add column "cumulated spent time" to issue filtersClosed

Actions
Has duplicate Redmine - Feature #14384: Add column "spent time" on list issuesClosed

Actions
Actions #1

Updated by Jean-Philippe Lang over 12 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
Actions #2

Updated by Aaron Fischer over 12 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.

Actions #3

Updated by Victor Hugo Bilouro over 12 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
Actions #5

Updated by Sylvain Langlade over 12 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.

Actions #6

Updated by Sylvain Langlade over 12 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 ??

Actions #7

Updated by Allan Story de Almeida Martins over 12 years ago

+1

i have this problem in redmine 2.0.3

Actions #9

Updated by Allan Story de Almeida Martins about 12 years ago

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

Actions #10

Updated by Aaron Fischer about 12 years ago

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

Actions #11

Updated by Ben Dalling almost 12 years ago

+1

Actions #12

Updated by Evgeny Zhiryakov almost 12 years ago

+All our company

Actions #13

Updated by Ben Dalling almost 12 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.

Actions #14

Updated by Ovidiu Stanciu almost 12 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.

Actions #15

Updated by Daniel Felix almost 12 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.

Actions #16

Updated by Ben Dalling almost 12 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.

Actions #17

Updated by Daniel Felix almost 12 years ago

  • Target version set to Candidate for next major release

ben eeg
Can you provide a combined patch for this?

Actions #18

Updated by Ben Dalling almost 12 years ago

Daniel Lopez,

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.

Actions #19

Updated by Ben Dalling over 11 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

Actions #20

Updated by Ovidiu Stanciu over 11 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

Actions #21

Updated by Henrik Leon over 11 years ago

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

Actions #22

Updated by Henrik Leon over 11 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.

Actions #23

Updated by VD DV over 11 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.

Actions #24

Updated by Ben Dalling over 11 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.

Actions #25

Updated by David Lukas Müller about 11 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

Actions #26

Updated by Vu Hong almost 11 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.

Actions #27

Updated by Vu Hong almost 11 years ago

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

Actions #28

Updated by Daniel Felix almost 11 years ago

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

Actions #29

Updated by Anton Statutov almost 11 years ago

This issue is also actual for spent time reports.

Actions #30

Updated by Maxime Vez over 10 years ago

+1 please fix this inconsistency

Actions #31

Updated by Toshi MARUYAMA over 10 years ago

  • Has duplicate Defect #16363: Spent time in list view is not the same as in single ticket view added
Actions #32

Updated by Scott Cunningham over 10 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.

Actions #33

Updated by Enmos Proje over 10 years ago

+1

Actions #34

Updated by Okke Hendriks over 10 years ago

+1

Actions #35

Updated by WDS D over 9 years ago

+1

Actions #36

Updated by Malcolm Thompson over 9 years ago

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

Actions #37

Updated by Jean-Philippe Lang over 9 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.

Actions #38

Updated by Ryosuke Hirai over 9 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.

Actions #39

Updated by Mischa The Evil over 9 years ago

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

Updated by Mischa The Evil over 9 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.

Actions #41

Updated by Toshi MARUYAMA about 9 years ago

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

Actions #42

Updated by Jean-Philippe Lang about 9 years ago

  • Target version changed from 3.1.0 to 3.1.1
Actions #43

Updated by Go MAEDA about 9 years ago

  • Has duplicate Patch #14483: Add column "cumulated spent time" to issue filters added
Actions #44

Updated by Go MAEDA almost 9 years ago

  • Has duplicate Feature #14384: Add column "spent time" on list issues added
Actions #45

Updated by Go MAEDA almost 8 years ago

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

Updated by Go MAEDA almost 8 years ago

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

Updated by Go MAEDA almost 4 years ago

  • Related to Defect #10527: time_spent does not sort properly for this case added
Actions

Also available in: Atom PDF