AnsweredAssumed Answered

Roll-up daily time slice for specific start and end date

Question asked by deepak.patro on Sep 16, 2014
Latest reply on Oct 2, 2014 by Kathryn_Ellis



I have a requirement that I am running out of ideas on how to fulfill. Have been trying this for some time now.


The attached code 'mp_data_dump_obs_selection_daily.sql', gives me the Task Date and Task Hours on a Daily basis.


  • this report generates by regular calendar dates (any date you enter in the parameters), but it gives me the daily tasks instead of rolling up to a total.  So for example, if I worked on a task each day for 1 hour called Coding, it would give me the information as follows:
    • Monday, Windows 1 hr
    • Tuesday, Windows 1 hr, etc.


The attached code 'mp_data_dump_obs_selection.sql' gives me Task Date and Task Hours on a Weekly basis.


  • This is based on the fiscal calendar or Monday to Sunday data.  It rolls up the information by task, so you can a total rather than breaking it out by day.  But if my month falls in the middle of the week, then when I run the report to gather what the actual hours are for a given month, I have to subtract out the end of the previous month and the start of the next month to get the correct month data.


When I run the actual data for a given month, I have to use the MP-Data Dump: OBS Selection Report and then subtract out the hours prior to the given month and after. So for example in the April report, I had to go in and remove the hours from 3/31 and also 5/1 – 5/4 to give me the total number of hours for the month of April.


The above report can only give me the Monday to Sunday numbers, hence the reason explained above and the other report gives me every task for the week.


Here is what I need:


  • Start and End Date – I need to be able to enter my time period that would use the daily period not the weekly period (that is given to me in the OBS Selection Report).  For example, I need to enter 8/1 to 8/31 and get that time period, even though those dates may end up mid-week.


  • Task Hours – I need these to roll up to a total for the week, not give me a daily time for each task.  So if I entered one hour each day for Not at Work, I need the report to show 5 hrs to Not at Work for the week.  


I basically need to combine both the reports. However, one report gets it data from timesheet tables and another gets it data from timeslice tables.


Can somebody help me with the same? Thanks in advance.