none
select which users doesn't logged their work yesterday on MS Project 2013 RRS feed

  • Question

  • We use MS Project 2013 to control people work report.

    I wanna make a SQL query that will catch all users that doesn't set their work yesterday on WebProject App.

    My intention on that is to alert them by e-mail about this.

    If the user doesn't logged his work for three or more days consecutive, his coordinator must be alerted too.

    Is there any configuration property on MS Project to do this ?

    Thanks! Bye

    Tuesday, June 10, 2014 5:31 PM

Answers

  • Indeed you need to go through the PSI.

    Here are some link for you to start with the PSI:

    • http://msdn.microsoft.com/en-us/library/office/ms488627(v=office.15).aspx
    • http://msdn.microsoft.com/en-us/library/office/ee767706(v=office.15).aspx
    • http://msdn.microsoft.com/en-us/library/office/ms481966(v=office.15).aspx
    • http://msdn.microsoft.com/en-us/library/office/ms420315(v=office.15).aspx

    Hope this helps,


    Guillaume Rouyre, MBA, MCP, MCTS |

    Wednesday, June 11, 2014 1:46 PM
    Moderator

All replies

  • Hi

    You may query the table MSP_EpmAssignmentByDay if the task updates are approved an published

    Sample query to check if the user has reported time for yesterday

    select resourcename,ResourceEmailAddress  from MSP_EpmResource
    where ResourceUID in ( select resourceuid from MSP_EpmAssignment where assignmentuid in (
    select assignmentuid from MSP_EpmAssignmentByDay where
    Convert(varchar(10),TimeByDay,101) = Convert(varchar(10),DATEADD(d,-1,getdate()),101) and AssignmentWork >0 and AssignmentActualWork =0))

    Note: This is a sample Query , you may modify it accordingly. you can query the table "MSP_TimesheetActual" in the same way if you want to look at the timesheet tables

    Refer Project Server SDK to get more information for Reporting Schema

    --Happy Troubleshooting


    Thanks --Ajith


    Wednesday, June 11, 2014 12:54 PM
    Moderator
  • Thanks for your help Ajith Jose !

    I am confused because I read this post

    http://qa.social.technet.microsoft.com/Forums/projectserver/en-US/5a8b3061-1892-4e1f-b2a9-cf147b1f2a91/reporting-how-to-query-resource-task-updates-done-via-mytask-not-timesheet?forum=projectserver2010general

     and the user have the same problem as I.

    We need the information before it had been approved and published.

    And according to that post, we need to access some PSI module to get this information.

    But in my researches, I didn't understood how this PSI works as well .

    Do I need to create a program and access this PSI thought webservice and catch each data and save it in another database, and then I query this database?

    Thanks! 

    Wednesday, June 11, 2014 1:23 PM
  • Indeed you need to go through the PSI.

    Here are some link for you to start with the PSI:

    • http://msdn.microsoft.com/en-us/library/office/ms488627(v=office.15).aspx
    • http://msdn.microsoft.com/en-us/library/office/ee767706(v=office.15).aspx
    • http://msdn.microsoft.com/en-us/library/office/ms481966(v=office.15).aspx
    • http://msdn.microsoft.com/en-us/library/office/ms420315(v=office.15).aspx

    Hope this helps,


    Guillaume Rouyre, MBA, MCP, MCTS |

    Wednesday, June 11, 2014 1:46 PM
    Moderator