none
Query Against Project Server Database for Time Entered By Day, By Resource... RRS feed

  • Question

  • We are using Project Server 2010. I am trying to create a report (outside of the UI) that will pull time entered by day, by resource by task directly from the database. We have some custom fields we are tying in like client name, project id, employee id, job id, etc. I am able to gather all of this information from a query BUT it is showing the accumulated time, not the time entered by day. Can anyone point me in the right direction as to where I would find the actual time and amount of time that was entered in the database? I'm at a loss for some reason and i really need to be able to report on resource time spent by day on x task.

    Below is my current query if it helps at all:

    SELECT DISTINCT
           [ProjectServer_Reporting].[dbo].[MSP_EpmResource_UserView].[ResourceName]
          ,[ProjectServer_Reporting].[dbo].[MSP_EpmResource_UserView].[Employee ID]
          ,[TaskName]
          ,[ProjectServer_Reporting].[dbo].[MSP_EpmProject_UserView].[ProjectName]
          ,CAST([Job ID]AS INT) AS [JOB ID]
          ,[ProjectServer_Reporting].[dbo].[MSP_EpmProject_UserView].[Customer]
          ,CAST([Client ID]AS INT) AS [CLIENT ID]
          ,CAST([TaskWork] AS DECIMAL (18,2)) AS [TaskWork HRS]
          ,CAST([TaskActualWork] AS DECIMAL (18,2)) AS [TaskActualWork HRS]
          ,[TaskPercentCompleted]
          ,[TaskPercentWorkCompleted]
          ,[TaskCreatedDate]
          ,[TaskModifiedDate]
          ,[TaskStartDate]
          ,[TaskFinishDate]
          ,CAST([TaskRegularWork] AS DECIMAL (18,2)) AS [TaskRegularWork HRS]
          ,CAST([TaskRemainingWork] AS DECIMAL (18,2)) AS [TaskRemainingWork HRS]
          ,CAST([TaskActualRegularWork] AS DECIMAL (18,2)) AS [TaskActualRegularWork HRS]
          ,CAST([TaskRemainingRegularWork] AS DECIMAL (18,2)) AS [TaskRemainingRegularWork HRS]
      FROM [ProjectServer_Reporting].[dbo].[MSP_EpmTask_UserView]
      INNER JOIN [ProjectServer_Reporting].[dbo].[MSP_EpmAssignment_UserView] ON
        [ProjectServer_Reporting].[dbo].[MSP_EpmAssignment_UserView].[TaskUID] = [ProjectServer_Reporting].[dbo].[MSP_EpmTask_UserView].[TaskUID]
      JOIN [ProjectServer_Reporting].[dbo].[MSP_EpmResource_UserView] ON
        [ProjectServer_Reporting].[dbo].[MSP_EpmResource_UserView].[ResourceUID] = [ProjectServer_Reporting].[dbo].[MSP_EpmAssignment_UserView].[ResourceUID]
      JOIN [ProjectServer_Reporting].[dbo].[MSP_EpmProject_UserView] ON
        [ProjectServer_Reporting].[dbo].[MSP_EpmProject_UserView].[ProjectUID] = [ProjectServer_Reporting].[dbo].[MSP_EpmAssignment_UserView].[ProjectUID]
      ORDER BY TaskModifiedDate DESC

    Thank you so much in advance for any assistance with this.

    Ian Sutherland

    Monday, October 15, 2012 3:44 PM

Answers

  • Hi Ian,

    just use a view containing data by day. I leave it up to you, to get your fields in. Just the base query how to join and get data:

    SELECT ProjectServer_Reporting.dbo.MSP_EpmAssignmentByDay_UserView.TimeByDay, ProjectServer_Reporting.dbo.MSP_EpmProject_UserView.ProjectName, ProjectServer_Reporting.dbo.MSP_EpmTask_UserView.TaskName, ProjectServer_Reporting.dbo.MSP_EpmResource_UserView.ResourceName, ProjectServer_Reporting.dbo.MSP_EpmAssignmentByDay_UserView.AssignmentActualWork, ProjectServer_Reporting.dbo.MSP_EpmAssignmentByDay_UserView.AssignmentActualRegularWork FROM ProjectServer_Reporting.dbo.MSP_EpmAssignmentByDay_UserView INNER JOIN ProjectServer_Reporting.dbo.MSP_EpmAssignment_UserView ON ProjectServer_Reporting.dbo.MSP_EpmAssignmentByDay_UserView.AssignmentUID = ProjectServer_Reporting.dbo.MSP_EpmAssignment_UserView.AssignmentUID INNER JOIN ProjectServer_Reporting.dbo.MSP_EpmProject_UserView ON ProjectServer_Reporting.dbo.MSP_EpmAssignment_UserView.ProjectUID = ProjectServer_Reporting.dbo.MSP_EpmProject_UserView.ProjectUID INNER JOIN ProjectServer_Reporting.dbo.MSP_EpmTask_UserView ON ProjectServer_Reporting.dbo.MSP_EpmAssignment_UserView.TaskUID = ProjectServer_Reporting.dbo.MSP_EpmTask_UserView.TaskUID INNER JOIN ProjectServer_Reporting.dbo.MSP_EpmResource_UserView ON ProjectServer_Reporting.dbo.MSP_EpmAssignment_UserView.ResourceUID = ProjectServer_Reporting.dbo.MSP_EpmResource_UserView.ResourceUID

    Hope that helps?
    Barbara

    Monday, October 15, 2012 4:11 PM
    Moderator

All replies

  • Hi Ian,

    just use a view containing data by day. I leave it up to you, to get your fields in. Just the base query how to join and get data:

    SELECT ProjectServer_Reporting.dbo.MSP_EpmAssignmentByDay_UserView.TimeByDay, ProjectServer_Reporting.dbo.MSP_EpmProject_UserView.ProjectName, ProjectServer_Reporting.dbo.MSP_EpmTask_UserView.TaskName, ProjectServer_Reporting.dbo.MSP_EpmResource_UserView.ResourceName, ProjectServer_Reporting.dbo.MSP_EpmAssignmentByDay_UserView.AssignmentActualWork, ProjectServer_Reporting.dbo.MSP_EpmAssignmentByDay_UserView.AssignmentActualRegularWork FROM ProjectServer_Reporting.dbo.MSP_EpmAssignmentByDay_UserView INNER JOIN ProjectServer_Reporting.dbo.MSP_EpmAssignment_UserView ON ProjectServer_Reporting.dbo.MSP_EpmAssignmentByDay_UserView.AssignmentUID = ProjectServer_Reporting.dbo.MSP_EpmAssignment_UserView.AssignmentUID INNER JOIN ProjectServer_Reporting.dbo.MSP_EpmProject_UserView ON ProjectServer_Reporting.dbo.MSP_EpmAssignment_UserView.ProjectUID = ProjectServer_Reporting.dbo.MSP_EpmProject_UserView.ProjectUID INNER JOIN ProjectServer_Reporting.dbo.MSP_EpmTask_UserView ON ProjectServer_Reporting.dbo.MSP_EpmAssignment_UserView.TaskUID = ProjectServer_Reporting.dbo.MSP_EpmTask_UserView.TaskUID INNER JOIN ProjectServer_Reporting.dbo.MSP_EpmResource_UserView ON ProjectServer_Reporting.dbo.MSP_EpmAssignment_UserView.ResourceUID = ProjectServer_Reporting.dbo.MSP_EpmResource_UserView.ResourceUID

    Hope that helps?
    Barbara

    Monday, October 15, 2012 4:11 PM
    Moderator
  • When using the ByDay tables (Assignment, Task, Resource), it's best to approach these from a set perspective and use subqueries to deliver the relevant set of detail prior to joining with other tables. Otherwise, you incur a lot of overhead resolving the joins.

    For example, if I wanted to get the amount of BaselineBudgetCost by Fiscal Year, with the ability to go lower in granularity, I might create the following query. It'll work but it will be very slow on large datasets. This query also uses a dynamic data window selection that I got from SQLAuthority.com

    SELECT    MSP_EpmAssignmentBaselineByDay.ProjectUID
    	, MSP_EpmAssignmentBaselineByDay.TimeByDay
    	, MSP_EpmAssignmentBaselineByDay.AssignmentBaselineBudgetCost
    	, MSP_TimeByDay.FiscalYear
    FROM      MSP_EpmAssignmentBaselineByDay 
    	  INNER JOIN MSP_EpmAssignment_UserView 
    	  ON MSP_EpmAssignmentBaselineByDay.ProjectUID = MSP_EpmAssignment_UserView.ProjectUID 
    	  AND MSP_EpmAssignmentBaselineByDay.AssignmentUID = MSP_EpmAssignment_UserView.AssignmentUID 
    	  INNER JOIN MSP_TimeByDay 
    	  ON MSP_EpmAssignmentBaselineByDay.TimeByDay = MSP_TimeByDay.TimeByDay
    WHERE     (MSP_EpmAssignmentBaselineByDay.BaselineNumber = 1) 
    AND 	  (MSP_EpmAssignmentBaselineByDay.TimeByDay >= DATEADD(mm, (YEAR(GETDATE()) - 1901) 
                          * 12 + 7 - 1, 0) + (1 - 1)) 
    AND 	(MSP_EpmAssignmentBaselineByDay.TimeByDay < DATEADD(mm, (YEAR(GETDATE()) - 1900) * 12 + 7 - 1, 0) + (1 - 1))
    

    A better way to deliver this data is to query the ByDay tables in a subquery, then join to the higher level tables/views. So, the query would now look like this. This should perform better on larger datasets.

    SELECT	
    	 FiscalYear
    	,AssnDetail.ProjectUID
    	,AssnDetail.AssignmentBaselineBudgetCost
     FROM	MSP_TimeByDay LEFT JOIN 
    	(SELECT
    	 	   MSP_EpmAssignmentBaselineByDay.ProjectUID 
    		 , MSP_EpmAssignmentBaselineByDay.TimeByDay
    		 , MSP_EpmAssignmentBaselineByDay.AssignmentBaselineBudgetCost
    	 FROM	   MSP_EpmAssignmentBaselineByDay INNER JOIN MSP_EpmAssignment_UserView 
    	 	   ON  MSP_EpmAssignmentBaselineByDay.ProjectUID = MSP_EpmAssignment_UserView.ProjectUID 
    	 	   AND MSP_EpmAssignmentBaselineByDay.AssignmentUID = MSP_EpmAssignment_UserView.AssignmentUID 
    	 WHERE	   MSP_EpmAssignmentBaselineByDay.BaselineNumber = 1
    	 AND	   MSP_EpmAssignmentBaselineByDay.TimeByDay >= dateadd(mm,(YEAR(GETDATE())-1901)* 12 + 7 - 1,0) + (1-1)
    	 AND	   MSP_EpmAssignmentBaselineByDay.TimeByDay < dateadd(mm,(YEAR(GETDATE())-1900)* 12 + 7 - 1,0) + (1-1)
    -- Technique for selection creates a date on the first day of the fiscal year, which is 7/1/2011 and ends this year.
    	) AS AssnDetail
    	 ON MSP_TimeByDay.TimeByDay = AssnDetail.TimeByDay
    WHERE	MSP_TimeByDay.TimeByDay >= dateadd(mm,(YEAR(GETDATE())-1901)* 12 + 7 - 1,0) + (1-1)
    AND	MSP_TimeByDay.TimeByDay < dateadd(mm,(YEAR(GETDATE())-1900)* 12 + 7 - 1,0) + (1-1)
    AND	AssnDetail.AssignmentBaselineBudgetCost IS NOT NULL
    

    Hopefully, this helps you create the query in the most efficient manner.

    Treb Gatte

    @tgatte | http://www.AboutMSProject.com

    Monday, October 15, 2012 7:36 PM
    Moderator
  • Thank you so much Barbara. This is exactly what I needed to get going in the right direction.
    Wednesday, October 17, 2012 2:40 PM
  • Hello there,

    You sound really helpful and I'm in need of some help.

    Your responses above are good (and related) but I have a slightly different challenge with aggregating Task costs, spread across Fiscal Periods defined in Project Server. I had started with the Task by day table to give gaps between periods (where there is no task or cost value logged). However, I was advised to just use the timebyday table (which gives the Fiscal years).

    My Issue: I have a dynamic matrix table that has row columns projects etc and columns Year/Period/task date groupings. This works perfectly however I'm unable to plot periods where no exist. (I'm now trying a union to ensure the Period values I need are there but I'm not sure if this will work).

    I hope this makes sense, do you have any ideas?

    Tuesday, June 4, 2013 9:44 PM