none
SSRS Report: Actual Hours entered weekly (non-timesheet) RRS feed

  • Question

  • I have been struggling with this for awhile now.  Here is what I am after:

    I need to create a report that will show hours entered weekly by each resource broken down by project.  We do not use timesheets, rather we update our time via tasks (My Tasks).

    The report would look like:

    Name:       |Project Name |Actual Hours Worked|Week Ending |
    Employee1 | Project X        |                4                 | 1/20/12          |
                      |  Project Y        |               10                |                       |
    Employee2 | Project G        |               14                | 1/20/12          |
                      |  Project J        |               18                |                       |

    Is this do-able or are timesheets necessary for this?  What/how should I query the reporting db?

    Wednesday, February 1, 2012 10:16 PM

Answers

  • Hi cdub22,

    This SQL query will help you:

    SELECT 
    	P.ProjectName,
    	R.ResourceName as Employee, 
    	YEAR(TimeByDay) as MyYear, 
    	DATEPART(wk,TimeByDay) as MyWeek, 
    	DATEADD(d,-DATEPART(DW,TimeByDay), TimeByDay) as "Week Ending", 
    	SUM(ABD.AssignmentActualWork) as "Actual hours Worked"
    FROM dbo.MSP_EpmAssignmentByDay_UserView ABD
    inner join dbo.MSP_EpmAssignment_UserView A on a.AssignmentUID=ABD.AssignmentUID
    inner join dbo.MSP_EpmResource_UserView R on R.ResourceUID=A.ResourceUID
    inner join dbo.MSP_EpmProject_UserView P on P.ProjectUID=A.ProjectUID
    GROUP BY 
    	P.PRojectName,
    	R.ResourceName, 
    	YEAR(TimeByDay), 
    	DATEPART(wk,TimeByDay) , 
    	DATEADD(d,-DATEPART(DW,TimeByDay), TimeByDay)
    Regards
    Wim

    Tuesday, April 10, 2012 11:37 AM
  • Wim,

    Thank you for the help.  It works very nice but doesn't quite give me the output I wanted.

    Here is what I ended up creating (if it helps someone else):

    SELECT        MSP_EpmProject_UserView.ProjectName, MSP_EpmTask_UserView.TaskName, MSP_EpmAssignment.AssignmentStartDate,
                             MSP_EpmAssignment.AssignmentFinishDate, MSP_EpmAssignment.AssignmentActualStartDate, MSP_EpmAssignment.AssignmentActualFinishDate,
                             MSP_EpmAssignment.AssignmentPercentWorkCompleted, MSP_EpmAssignmentByDay_UserView.TimeByDay,
                             MSP_EpmAssignmentByDay_UserView.AssignmentWork, MSP_EpmAssignmentByDay_UserView.AssignmentActualWork,
                             MSP_EpmAssignmentByDay_UserView.AssignmentRemainingWork, MSP_EpmAssignmentByDay_UserView.AssignmentActualRegularWork,
                             MSP_EpmResource_UserView.ResourceName, MSP_TimeByDay.TimeWeekOfTheYear, MSP_EpmResource_UserView.ResourceCostCenter,
                             MSP_EpmResource_UserView.ResourceIsActive, MSP_TimeByDay.TimeDayOfTheWeek, MSP_TimeByDay.TimeMonthOfTheYear, MSP_TimeByDay.TimeQuarter,
                             MSP_TimeByDay.FiscalPeriodName, MSP_TimeByDay.FiscalQuarter, MSP_TimeByDay.FiscalYear, MSP_TimeByDay.TimeYear,
                             MSP_TimeByDay.TimeDayOfTheMonth
    FROM            MSP_EpmAssignmentByDay_UserView INNER JOIN
                             MSP_EpmProject_UserView ON MSP_EpmAssignmentByDay_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID INNER JOIN
                             MSP_EpmTask_UserView ON MSP_EpmAssignmentByDay_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID AND
                             MSP_EpmAssignmentByDay_UserView.TaskUID = MSP_EpmTask_UserView.TaskUID INNER JOIN
                             MSP_EpmAssignment ON MSP_EpmAssignmentByDay_UserView.AssignmentUID = MSP_EpmAssignment.AssignmentUID AND
                             MSP_EpmAssignmentByDay_UserView.ProjectUID = MSP_EpmAssignment.ProjectUID AND
                             MSP_EpmAssignmentByDay_UserView.TaskUID = MSP_EpmAssignment.TaskUID INNER JOIN
                             MSP_EpmResource_UserView ON MSP_EpmAssignment.ResourceUID = MSP_EpmResource_UserView.ResourceUID INNER JOIN
                             MSP_TimeByDay ON MSP_EpmAssignmentByDay_UserView.TimeByDay = MSP_TimeByDay.TimeByDay
    WHERE        (MSP_EpmAssignmentByDay_UserView.AssignmentActualWork <> 0.000000) AND (MSP_EpmResource_UserView.ResourceCostCenter = N'8542') AND
                             (MSP_EpmAssignmentByDay_UserView.TimeByDay >= @StartDt) AND (MSP_EpmAssignmentByDay_UserView.TimeByDay <= @EndDt)

    My SQL skills leave something to be desired but this dos give me a nice report with start and stop date parameters.

    Tuesday, April 10, 2012 1:03 PM

All replies

  • Hi cdub22,

    This SQL query will help you:

    SELECT 
    	P.ProjectName,
    	R.ResourceName as Employee, 
    	YEAR(TimeByDay) as MyYear, 
    	DATEPART(wk,TimeByDay) as MyWeek, 
    	DATEADD(d,-DATEPART(DW,TimeByDay), TimeByDay) as "Week Ending", 
    	SUM(ABD.AssignmentActualWork) as "Actual hours Worked"
    FROM dbo.MSP_EpmAssignmentByDay_UserView ABD
    inner join dbo.MSP_EpmAssignment_UserView A on a.AssignmentUID=ABD.AssignmentUID
    inner join dbo.MSP_EpmResource_UserView R on R.ResourceUID=A.ResourceUID
    inner join dbo.MSP_EpmProject_UserView P on P.ProjectUID=A.ProjectUID
    GROUP BY 
    	P.PRojectName,
    	R.ResourceName, 
    	YEAR(TimeByDay), 
    	DATEPART(wk,TimeByDay) , 
    	DATEADD(d,-DATEPART(DW,TimeByDay), TimeByDay)
    Regards
    Wim

    Tuesday, April 10, 2012 11:37 AM
  • Wim,

    Thank you for the help.  It works very nice but doesn't quite give me the output I wanted.

    Here is what I ended up creating (if it helps someone else):

    SELECT        MSP_EpmProject_UserView.ProjectName, MSP_EpmTask_UserView.TaskName, MSP_EpmAssignment.AssignmentStartDate,
                             MSP_EpmAssignment.AssignmentFinishDate, MSP_EpmAssignment.AssignmentActualStartDate, MSP_EpmAssignment.AssignmentActualFinishDate,
                             MSP_EpmAssignment.AssignmentPercentWorkCompleted, MSP_EpmAssignmentByDay_UserView.TimeByDay,
                             MSP_EpmAssignmentByDay_UserView.AssignmentWork, MSP_EpmAssignmentByDay_UserView.AssignmentActualWork,
                             MSP_EpmAssignmentByDay_UserView.AssignmentRemainingWork, MSP_EpmAssignmentByDay_UserView.AssignmentActualRegularWork,
                             MSP_EpmResource_UserView.ResourceName, MSP_TimeByDay.TimeWeekOfTheYear, MSP_EpmResource_UserView.ResourceCostCenter,
                             MSP_EpmResource_UserView.ResourceIsActive, MSP_TimeByDay.TimeDayOfTheWeek, MSP_TimeByDay.TimeMonthOfTheYear, MSP_TimeByDay.TimeQuarter,
                             MSP_TimeByDay.FiscalPeriodName, MSP_TimeByDay.FiscalQuarter, MSP_TimeByDay.FiscalYear, MSP_TimeByDay.TimeYear,
                             MSP_TimeByDay.TimeDayOfTheMonth
    FROM            MSP_EpmAssignmentByDay_UserView INNER JOIN
                             MSP_EpmProject_UserView ON MSP_EpmAssignmentByDay_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID INNER JOIN
                             MSP_EpmTask_UserView ON MSP_EpmAssignmentByDay_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID AND
                             MSP_EpmAssignmentByDay_UserView.TaskUID = MSP_EpmTask_UserView.TaskUID INNER JOIN
                             MSP_EpmAssignment ON MSP_EpmAssignmentByDay_UserView.AssignmentUID = MSP_EpmAssignment.AssignmentUID AND
                             MSP_EpmAssignmentByDay_UserView.ProjectUID = MSP_EpmAssignment.ProjectUID AND
                             MSP_EpmAssignmentByDay_UserView.TaskUID = MSP_EpmAssignment.TaskUID INNER JOIN
                             MSP_EpmResource_UserView ON MSP_EpmAssignment.ResourceUID = MSP_EpmResource_UserView.ResourceUID INNER JOIN
                             MSP_TimeByDay ON MSP_EpmAssignmentByDay_UserView.TimeByDay = MSP_TimeByDay.TimeByDay
    WHERE        (MSP_EpmAssignmentByDay_UserView.AssignmentActualWork <> 0.000000) AND (MSP_EpmResource_UserView.ResourceCostCenter = N'8542') AND
                             (MSP_EpmAssignmentByDay_UserView.TimeByDay >= @StartDt) AND (MSP_EpmAssignmentByDay_UserView.TimeByDay <= @EndDt)

    My SQL skills leave something to be desired but this dos give me a nice report with start and stop date parameters.

    Tuesday, April 10, 2012 1:03 PM
  • Hi BK, 

    above is the fantastic query, if possible to add the same resource capacity.

    Thanks

    Gesh

    Thursday, April 5, 2018 7:10 AM