Sign in
Microsoft.com
 
Microsoft
 
 
 
Microsoft >
> 
> 
> 
Reporting on Status Changes

Unanswered Reporting on Status Changes

  • Friday, 30 March, 2012 10:05 PM
     
     
    Sign In to Vote
    0

    I would like to create a report that lists all the task status changes that have been submited.  I can't seem to find that information in the reporting database.  Where can I find it?

    Thanks!

     

All Replies

  • Sunday, 1 April, 2012 1:19 PM
     
     
    Sign In to Vote
    0

    Hello Craig,

    Download the SDK for project server.  There is a schema for the reporting database.

    http://msdn.microsoft.com/en-us/library/ms512767.aspx

    Cheers!

    Michael Wharton, MBA, PMP, MCT, MCSD, MCSE+I, MCDBA, MCC 2011, MCC2012
    Website http://www.WhartonComputer.com
    Blog http://MyProjectExpert.com

     
  • Monday, 2 April, 2012 1:12 PM
     
     
    Sign In to Vote
    0

    If this is for task status on a timesheet then the following query will help get you going. If from a task update through my tasks then you might need to look at the publish databse.


    SELECT TOP 10
     tsp.StartDate
     ,tsp.EndDate
     ,p.ProjectName
     ,p.ProjectUID 
     ,pm.ResourceName AS ProjectManager
     ,tsr.ResourceName AS TimeSheetResourceName 
     ,tst.TaskName
     ,tsa.TimeByDay
     ,tsa.ActualWorkBillable
     ,tsa.ActualOvertimeWorkBillable 
     ,tss.[Description]
    FROM MSP_TimesheetActual tsa
    INNER JOIN MSP_TimesheetLine tsl
     ON tsa.TimesheetLineUID = tsl.TimesheetLineUID
    INNER JOIN MSP_TimesheetLineStatus tss
     ON tsl.TimesheetLineStatus = tss.TimesheetLineStatusID
    INNER JOIN MSP_TimesheetProject tsProj
     ON tsl.ProjectNameUID = tsProj.ProjectNameUID
    INNER JOIN MSP_Timesheet ts
     ON  tsl.TimesheetUID = ts.TimesheetUID
    INNER JOIN MSP_TimesheetResource tsr
     ON ts.OwnerResourceNameUID = tsr.ResourceNameUID
    INNER JOIN MSP_EpmResource_UserView tsrDetails
     ON tsr.ResourceUID= tsrDetails.ResourceUID
    INNER JOIN MSP_TimesheetPeriod tsp
     ON ts.PeriodUID = tsp.PeriodUID
    INNER JOIN MSP_TimesheetTask tst
     ON tsl.TaskNameUID = tst.TaskNameUID
    INNER JOIN MSP_EpmProject_UserView p
     ON tsProj.ProjectUID = p.ProjectUID
    INNER JOIN MSP_EpmResource_UserView pm
     ON p.ProjectOwnerResourceUID = pm.ResourceUID
    WHERE 1=1
     --AND (tsp.PeriodUID = @vuidPeriodUID)
     --AND (p.ProjectUID = @vuidProjectUID OR @vuidProjectUID IS NULL)
     --AND tsa.ActualWorkBillable <> 0
    ORDER BY
     tsp.EndDate
     ,p.ProjectName

    Regards,

    Piet Remen
    http://pietremen.blogspot.com.au

     
  • Tuesday, 3 April, 2012 12:38 PM
     
     
    Sign In to Vote
    0

    Thanks for the responses.  I am tasks updates from the My Task page, so it looks like I need to go to the Published database.  Does anyone know where that data is?

     
  • Tuesday, 3 April, 2012 2:05 PM
     
     
    Sign In to Vote
    0
    Hmmmm, not sure sorry. At a guess try looking at the [MSP_ASSIGNMENT_TRANSACTIONS] table. Other thing is you could perhaps use SQL profiler to track a stored procedure which updates the task after a task update and see if that points you down the path of a stored proc or table.

    Regards,

    Piet Remen
    http://pietremen.blogspot.com.au