Monday, May 31, 2010

Daily Timesheet Comment in Project Server 2007

Project server, Out of the Box, Timesheet (my timesheet) allow us to enter comments for each task (single comments for whole week). But it does not allow us to enter daily comments for each task.
For Example, in one week I have worked on 2 tasks , as - Task 1 - Mon , Tue, Wed ; Task2 – Thu , Fri
OOB I can only enter 2 comments (one for Task 1 and one for Task 2) , there is no provision for entering comments for individual day. So it is difficult for a manger to find out what a resource has done on any day (date).
We can accomplish this with the help of ‘Enterprise Custom Field’ features in following way…..
  • Add New Custom fields -
    • Open Server Setting page and click on Enterprise Custom Field Definition link (Server Settings -> Enterprise Data section -> Enterprise Custom Field Definition)
    • On Custom Fields and Lookup Table page , Click on New Field button
    • Create new comment field of type Task for Monday
    • Similar way create comment fields for other day e.g. Comment(Tue), Comment(Wed),Comment(Thu), Comment(Fri), Comment(Sat), Comment(Sun)
  • Edit MyTimeSheet View - Now add all these field into my timesheet entry screen (Mytimesheet view)
    • On Server Setting Page , Click on Manage View link (Server Settings -> Look and Feel section -> Manage Views)
    • On Manage View page , Click on my timesheet view under Timesheet Section
    • Add all the comments fields in the order of day (Comment(Mon) .. Comment(Sun))
    • Comment Will appear in time sheet entry screen , like this
      Now user can enter daily comments for each individual task.
  •  
  • Access comment data - How to access these comment data and mapped them with Timesheet task and date in report -
    SELECT v.TS_UID as TimesheetId,v.TS_LINE_UID as TimesheetLineId, v.TEXT_VALUE as Comment,
    cast((
    case
    when c.MD_PROP_NAME like 'Comment(Mon)' then p.startdate
    when c.MD_PROP_NAME like 'Comment(Tue)' then DATEADD(day, 1, p.startdate)
    when c.MD_PROP_NAME like 'Comment(Wed)' then DATEADD(day, 2, p.startdate)
    when c.MD_PROP_NAME like 'Comment(Thu)' then DATEADD(day, 3, p.startdate)
    when c.MD_PROP_NAME like 'Comment(Fri)' then DATEADD(day, 4, p.startdate)
    when c.MD_PROP_NAME like 'Comment(Sat)' then DATEADD(day, 5, p.startdate)
    when c.MD_PROP_NAME like 'Comment(Sun)' then DATEADD(day, 6, p.startdate)
    else ''
    End
    ) as varchar) as CommentDate
    FROM
    [ProjectServer_Published].[dbo].[MSP_TIMESHEET_CUSTOM_FIELD_VALUES] v
    INNER JOIN
    [ProjectServer_Published].[dbo].[MSP_CUSTOM_FIELDS] c
    ON c.MD_PROP_UID = v.MD_PROP_UID
    Inner Join MSP_Timesheet t ON
    t.TimesheetUID = v.TS_UID
    Inner Join MSP_TimesheetPeriod p
    On p.PeriodUID = t.PeriodUID
    WHERE c.MD_PROP_NAME in ('Comment(Mon)','Comment(Tue)','Comment(Wed)','Comment(Thu)','Comment(Fri)','Comment(Sat)','Comment(Sun)')
  •  
  • Remove Comment Column - How to remove existing Comment Column
    • Make a copy TimesheetPart.htc under C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS\PWA\LIBRARY
    • Open TimesheetPart.htc and add the following line (function onload()):
      for(var i = 0; i < idGrid.rows.length; i++) {idGrid.rows.deleteCell(3)};
    • It will look like this
      …..
      …….
      if (typeof(idGrid) != 'undefined')
      {
      idGrid.attachEvent("OnRowSelectedStateChange", ChangeControlStates);
      idGrid.attachEvent("OnLoadCompleted",onGirdLoadComplete);
      idGrid.attachEvent("OnDataChanged",OnDataChanged);
      for(var i = 0; i < idGrid.rows.length; i++)
      {
       idGrid.rows.deleteCell(3); // Comment Column
      }
      }
      ……

2 comments:

TalaT said...

Hi,
this doesn't work in project 2010 :(

ocean freight said...

How come it doesn't work in project 2010??