none
Query that return last date of Actual Work in a project RRS feed

  • Question

  • Dear all,

    We are running Microsoft Project Server 2010 SP1 (October 2011 CU).

    I am trying to find a way to filter out,using a query, projects that have Actual Work > 0 hours prior a certain date but not after that date.

    I would prefer to do it with a query on the publsihed db but if it for sme reason is more effecient to do it using reporting db then it is fine.

    Best regards,

    Per

     

    Tuesday, December 13, 2011 11:05 AM

Answers

  • I ended up with a similar query as Miguel. here is how I got there:

    Here is one that returns all the Days where there is a non-zero Actual work value prior to 12/25/11 for any task and it returns the Project name, task name resource name and the date:

    SELECT     MSP_EpmProject_UserView.ProjectName, MSP_EpmTask_UserView.TaskName AS Task_Name, MSP_EpmResource_UserView.ResourceName, 
                          MSP_EpmAssignmentByDay_UserView.TimeByDay
    FROM         MSP_EpmTask_UserView INNER JOIN
                          MSP_EpmProject_UserView ON MSP_EpmTask_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID INNER JOIN
                          MSP_EpmAssignment_UserView ON MSP_EpmTask_UserView.TaskUID = MSP_EpmAssignment_UserView.TaskUID INNER JOIN
                          MSP_EpmResource_UserView ON MSP_EpmAssignment_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID INNER JOIN
                          MSP_EpmAssignmentByDay_UserView ON MSP_EpmAssignment_UserView.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID
    WHERE     (MSP_EpmAssignmentByDay_UserView.AssignmentActualWork > 0) AND (MSP_EpmAssignmentByDay_UserView.TimeByDay < CONVERT(DATETIME, 
                          '2011-12-25 00:00:00', 102))
    GROUP BY MSP_EpmTask_UserView.TaskName, MSP_EpmProject_UserView.ProjectName, MSP_EpmResource_UserView.ResourceName, 
                          MSP_EpmAssignmentByDay_UserView.TimeByDay
    

    Then I just remove the date from the select and it then shows only the tasks that have non-zero actual work prior to 12/25/11

    SELECT     MSP_EpmProject_UserView.ProjectName, MSP_EpmTask_UserView.TaskName AS Task_Name, MSP_EpmResource_UserView.ResourceName                      
    FROM         MSP_EpmTask_UserView INNER JOIN
                          MSP_EpmProject_UserView ON MSP_EpmTask_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID INNER JOIN
                          MSP_EpmAssignment_UserView ON MSP_EpmTask_UserView.TaskUID = MSP_EpmAssignment_UserView.TaskUID INNER JOIN
                          MSP_EpmResource_UserView ON MSP_EpmAssignment_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID INNER JOIN
                          MSP_EpmAssignmentByDay_UserView ON MSP_EpmAssignment_UserView.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID
    WHERE     (MSP_EpmAssignmentByDay_UserView.AssignmentActualWork > 0) AND (MSP_EpmAssignmentByDay_UserView.TimeByDay < CONVERT(DATETIME, 
                          '2011-12-25 00:00:00', 102))
    GROUP BY MSP_EpmTask_UserView.TaskName, MSP_EpmProject_UserView.ProjectName, MSP_EpmResource_UserView.ResourceName
    
    

    then if you want you can remove the Task and Resource names to get just the project name:

    SELECT     MSP_EpmProject_UserView.ProjectName
    FROM         MSP_EpmTask_UserView INNER JOIN
                          MSP_EpmProject_UserView ON MSP_EpmTask_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID INNER JOIN
                          MSP_EpmAssignment_UserView ON MSP_EpmTask_UserView.TaskUID = MSP_EpmAssignment_UserView.TaskUID INNER JOIN
                          MSP_EpmResource_UserView ON MSP_EpmAssignment_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID INNER JOIN
                          MSP_EpmAssignmentByDay_UserView ON MSP_EpmAssignment_UserView.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID
    WHERE     (MSP_EpmAssignmentByDay_UserView.AssignmentActualWork > 0) AND (MSP_EpmAssignmentByDay_UserView.TimeByDay < CONVERT(DATETIME, 
                          '2011-12-25 00:00:00', 102))
    GROUP BY MSP_EpmProject_UserView.ProjectName
    


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn
    • Marked as answer by PeYl Wednesday, December 14, 2011 7:25 AM
    Tuesday, December 13, 2011 10:12 PM
    Moderator

All replies

  • Hi PeYI,

    Sorry, but I didnt understand what are trying to attain. Do you want a list in Excel, Reporting Services, a view in Project Center?

     

    Cheers!


    Miguel Soler
    Tuesday, December 13, 2011 3:36 PM
  • I just want to create a SQL query then I have no problem to export that query result as an Excel.

    I don't know which Project Server field to use to identify "this is the last day (i.e. date) in the project where a resource has reported Actual Work". :-)

    Tuesday, December 13, 2011 5:05 PM
  • Hi again Peyl,

     

    With the information I´m understaing, I guess you want a SQL query with a parameter date, to filter all the projects the Sum of the Actual prior the date parameter is greater than 0, no? Correct if I´m wrong.

    I would do something like this:

    SELECT [ProjectName] ,sum([AssignmentActualWork])
      FROM [PWA_Reporting].[dbo].[MSP_EpmAssignmentByDay_UserView]
      inner join [PWA_Reporting].[dbo].MSP_EpmProject_UserView on
      [PWA_Reporting].[dbo].MSP_EpmProject_UserView.ProjectUID= [PWA_Reporting].[dbo].[MSP_EpmAssignmentByDay_UserView].ProjectUID
      --Change the date
      where TimeByDay< '2010-05-24'
      group by ProjectName

     

    Hope that helps you.

    Best regards!


    Miguel Soler
    Tuesday, December 13, 2011 8:01 PM
  • I ended up with a similar query as Miguel. here is how I got there:

    Here is one that returns all the Days where there is a non-zero Actual work value prior to 12/25/11 for any task and it returns the Project name, task name resource name and the date:

    SELECT     MSP_EpmProject_UserView.ProjectName, MSP_EpmTask_UserView.TaskName AS Task_Name, MSP_EpmResource_UserView.ResourceName, 
                          MSP_EpmAssignmentByDay_UserView.TimeByDay
    FROM         MSP_EpmTask_UserView INNER JOIN
                          MSP_EpmProject_UserView ON MSP_EpmTask_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID INNER JOIN
                          MSP_EpmAssignment_UserView ON MSP_EpmTask_UserView.TaskUID = MSP_EpmAssignment_UserView.TaskUID INNER JOIN
                          MSP_EpmResource_UserView ON MSP_EpmAssignment_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID INNER JOIN
                          MSP_EpmAssignmentByDay_UserView ON MSP_EpmAssignment_UserView.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID
    WHERE     (MSP_EpmAssignmentByDay_UserView.AssignmentActualWork > 0) AND (MSP_EpmAssignmentByDay_UserView.TimeByDay < CONVERT(DATETIME, 
                          '2011-12-25 00:00:00', 102))
    GROUP BY MSP_EpmTask_UserView.TaskName, MSP_EpmProject_UserView.ProjectName, MSP_EpmResource_UserView.ResourceName, 
                          MSP_EpmAssignmentByDay_UserView.TimeByDay
    

    Then I just remove the date from the select and it then shows only the tasks that have non-zero actual work prior to 12/25/11

    SELECT     MSP_EpmProject_UserView.ProjectName, MSP_EpmTask_UserView.TaskName AS Task_Name, MSP_EpmResource_UserView.ResourceName                      
    FROM         MSP_EpmTask_UserView INNER JOIN
                          MSP_EpmProject_UserView ON MSP_EpmTask_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID INNER JOIN
                          MSP_EpmAssignment_UserView ON MSP_EpmTask_UserView.TaskUID = MSP_EpmAssignment_UserView.TaskUID INNER JOIN
                          MSP_EpmResource_UserView ON MSP_EpmAssignment_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID INNER JOIN
                          MSP_EpmAssignmentByDay_UserView ON MSP_EpmAssignment_UserView.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID
    WHERE     (MSP_EpmAssignmentByDay_UserView.AssignmentActualWork > 0) AND (MSP_EpmAssignmentByDay_UserView.TimeByDay < CONVERT(DATETIME, 
                          '2011-12-25 00:00:00', 102))
    GROUP BY MSP_EpmTask_UserView.TaskName, MSP_EpmProject_UserView.ProjectName, MSP_EpmResource_UserView.ResourceName
    
    

    then if you want you can remove the Task and Resource names to get just the project name:

    SELECT     MSP_EpmProject_UserView.ProjectName
    FROM         MSP_EpmTask_UserView INNER JOIN
                          MSP_EpmProject_UserView ON MSP_EpmTask_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID INNER JOIN
                          MSP_EpmAssignment_UserView ON MSP_EpmTask_UserView.TaskUID = MSP_EpmAssignment_UserView.TaskUID INNER JOIN
                          MSP_EpmResource_UserView ON MSP_EpmAssignment_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID INNER JOIN
                          MSP_EpmAssignmentByDay_UserView ON MSP_EpmAssignment_UserView.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID
    WHERE     (MSP_EpmAssignmentByDay_UserView.AssignmentActualWork > 0) AND (MSP_EpmAssignmentByDay_UserView.TimeByDay < CONVERT(DATETIME, 
                          '2011-12-25 00:00:00', 102))
    GROUP BY MSP_EpmProject_UserView.ProjectName
    


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn
    • Marked as answer by PeYl Wednesday, December 14, 2011 7:25 AM
    Tuesday, December 13, 2011 10:12 PM
    Moderator
  • Dear Brian and Miguel,

    Actually I was looking for a way to filter all projects that have Actual Work (sum is ok) = 0 hours after a certain date....I think your proposals will work where the sign ">" is changed to "=". The intention was to use this funtion to identify old projects that could be archived due to taht we ues Actaul Work (R12) in our reports and the Last Published date or when a task ends was not could enough to do this filtering. We have also some old project swith some old bugs (I assume) that have for some reason set the Actual Work = 0 hours for some resource and task which means that that tasks ends several years later...

    Thanks!

    Per

    Wednesday, December 14, 2011 7:30 AM