locked
2007: Query if Timesheet has been submitted by Resource/ResourceOwner RRS feed

  • Question

  • Hi,

    as far as I know the workflow is as follows:

    1. Project Planning, Publishing, etc
    2. Resource creates timesheet from period and completes it.
    3. Resource submits Timesheet
    4. Timesheetmanager approves Timesheet
    5. Resource Owner imports the Timesheets into "myTasks" and submits them
    6. PM gets a notification when opening Project Professional

    Can I (sql) query somehow if the Resource Owner has already imported and submitted the Timesheets already ? I did not find a column in the database for this question yet.

    Regards

    Sven

    Tuesday, August 17, 2010 11:07 AM

Answers

  • Hi Sven,

    as Marc wrote, this information is not available in RDB at least I am not aware of any fleg "imported".

    You could use Data Analysis with cube MSP_Project_Timesheet to compare data Actual Work Billable of timesheets with actual work of project (My Tasks page). You can also add a calculated field to display only differences - if your process was completed, it should be 0. However, actual work ist only displayed in the cube, when it was submitted, approved and published.

    Saved, submitted and approved (not yet published) data are only availbable in Published DB (please be aware of the fact that MS
    does not support queries in DBs other than Reporting!!!). However, with the query in the link below, you will only find if there is unsubmitted (saved), submitted or approved but not published data. This data is not timephased! http://us.generation-nt.com/answer/checking-needed-updates-help-170716911.html.

    Perhaps a combination of these possibilities will help you?

    Regards
    Barbara

    Thursday, August 19, 2010 5:18 AM

All replies

  • what I meant is something like in this blog entry

    http://www.ureader.com/msg/11174073.aspx

    Do you have a working query?

    Tuesday, August 17, 2010 1:36 PM
  • Sven first of all do you use 2010 or 2007? I assume 2007.

    Generally the workflow goes as follow:

    1. Project Planning, Publishing, etc
    2. Resource creates timesheet from period and completes it.
    3. Resource submits Timesheet
    4. Timesheetmanager approves Timesheet
    5. Resource navigates to the My Task page, and imports the timesheet to update the tasks.
    6. Resource my changes remaining work for the task which have been imported and submits the task update
    7. PM gets a notification outlining that resource updates pending his approval
    8. PM can either approve or reject task update ( this can be done in Project Pro or PWA)
    9. If PM ignores the notification and opens a proejct where updates are pending, then PM gets a notification when opening Project Professional
    10. Now the PM can approve the task and the task is updated.

    I am not sure what you mean by Resource Owner, I dont believe that is a standard term in Project Server, you may confuse this with the OwnerResourceUID in a SQL table, which I believe is the project owner

    You should know that only the "Assignment Owner" can update tasks. In most cases the assignment owner is the person who submits the timesheet ( you can find the assignment default owner within Server Settings > Manage Users), but they can also change from task to task and can be managed in the schedule.

    I hope this makes sense.


    Marc Soester [MVP] http://marcsoester.blogspot.com
    Wednesday, August 18, 2010 2:32 AM
  • Good morning,

    thanks Marc, but I was not asking for the Workflow. I was asking for a SQL query with which I can figure out if step 5 and step 6 of your list

    • Resource navigates to the My Task page, and imports the timesheet to update the tasks.
    • Resource my changes remaining work for the task which have been imported and submits the task update

    are already completed. The timesheet status does not give insight into this.

    Regards

    Sven

    Wednesday, August 18, 2010 7:24 AM
  • Hi Sven,

    hmmmm, I believe that you will not find this in the reporting database. i can imagine that you will get this information of the draft DB? sorry, but I have not had to do this type of report before. It may makes sense to post this question in the Project Server Developer forum.


    Marc Soester [MVP] http://marcsoester.blogspot.com
    Wednesday, August 18, 2010 11:41 PM
  • Hi Sven,

    as Marc wrote, this information is not available in RDB at least I am not aware of any fleg "imported".

    You could use Data Analysis with cube MSP_Project_Timesheet to compare data Actual Work Billable of timesheets with actual work of project (My Tasks page). You can also add a calculated field to display only differences - if your process was completed, it should be 0. However, actual work ist only displayed in the cube, when it was submitted, approved and published.

    Saved, submitted and approved (not yet published) data are only availbable in Published DB (please be aware of the fact that MS
    does not support queries in DBs other than Reporting!!!). However, with the query in the link below, you will only find if there is unsubmitted (saved), submitted or approved but not published data. This data is not timephased! http://us.generation-nt.com/answer/checking-needed-updates-help-170716911.html.

    Perhaps a combination of these possibilities will help you?

    Regards
    Barbara

    Thursday, August 19, 2010 5:18 AM