Time Reporting Queries (it’s about time)

One of the things I feel a bit like a pioneer about (seriously, it’s pretty similar to churning your own butter) is time reporting. Specifically when it comes to task level status for time reporting from the reporting database. In this post I will include two helpful queries for time reporting – one for timesheet usage, the other for timesheet line status usage. The first is pretty clean, and even shows you the resources who didn’t even create a timesheet. The second one is more of a struggle, because it has to compensate for the “expected behavior” (undocumented feature?) regarding the line status and its lack of unique value for a given week. But more on that later on.

Timesheet Reporting

If you’re using timesheets, reporting on a week by week basis shouldn’t be too hard. The first query below uses the trick of pulling data from the resource table, then using an outer join to grab that timesheet data. If you pull this sucker into a pivot table, you can pivot it into submission and let everyone know that big brother is, without a doubt, watching.

Timesheet Line Status

As you can see by this forum post, timesheet line status is a gabillion times more complicated.

The following applies to this setup: Project Server 2010, Single Entry Mode, “Require Line Approval Before Timesheet Approval” checked.

Here are the different statuses:

Pending
Pending Approval
Approved
Rejected

Looks pretty straightforward, but here’s where it starts to go sideways – all but one of those statuses is often a lie. The lucky winner is “Pending Approval”. That one can usually be believed.

But there’s a way to determine if the others are real or not. See, when a timesheet is created, the line status is carried over from the week before. So if a task was “approved” last week, the status of the line for this week will also be “approved”, until the resource submits it, and then it will change to “pending approval”. The part which doesn’t carry over is the comments – whenever something is approved or rejected, a comment is added to the line. The default comment is the user name and date. So as long as you only show the approved or rejected status with comments, or the pending approval status for the given pay period, you can tell whether someone submitted their time. I’ve created a query which will take care of that logic for you. Note that the blank comments aren’t actually NULL, so I am using a like clause in my case statement to look for the letter A – our domain has an A in it, so every comment will contain one. So yep, that’s time reporting in an ugly, yet tidy bucket for you.

Stop! Query time! (doesn’t this make you want to dance? And wear stupid trousers?)

Timesheet Reporting query:

SELECT
MSP_TimesheetLine_UserView.PeriodName as [PeriodName],
MSP_TimesheetLine_UserView.PeriodStatus as [Period Status],
MSP_TimesheetLine_UserView.TimesheetName as [TimesheetName],
MSP_TimesheetLine_UserView.TimesheetStatus as [Timesheet Status],
MSP_TimesheetLine_UserView.TimesheetLineClass as [Timesheet Line Class],
MSP_TimesheetLine_UserView.ProjectName as [ProjectName],
MSP_TimesheetLine_UserView.TaskName as [TaskName],
MSP_TimesheetLine_UserView.PlannedWork as [Planned Work],
MSP_TimesheetLine_UserView.ActualWorkBillable as [Billable Actual Work],
MSP_TimesheetLine_UserView.ActualWorkNonBillable as [Non Billable Actual Work],
MSP_TimesheetLine_UserView.ActualOvertimeWorkBillable as [BillableActOvertimeWork],
MSP_TimesheetLine_UserView.ActualOvertimeWorkNonBillable as [Non Billable ActOTWork],
MSP_TimesheetLine_UserView.ActualWorkBillable +
MSP_TimesheetLine_UserView.ActualWorkNonBillable +
MSP_TimesheetLine_UserView.ActualOvertimeWorkBillable +
MSP_TimesheetLine_UserView.ActualOvertimeWorkNonBillable as [TimesheetLineActWork],
MSP_TimesheetLine_UserView.PeriodStartDate as [Period Start Date],
MSP_TimesheetLine_UserView.PeriodEndDate as [Period End Date],
MSP_TimesheetLine_UserView.[RBS] as [RBS],
MSP_EpmResource_UserView.ResourceName

FROM

MSP_EpmResource_UserView

LEFT OUTER JOIN

MSP_TimesheetLine_UserView ON
MSP_EpmResource_UserView.ResourceUID = MSP_TimesheetLine_UserView.ResourceUID

Timesheet Line Status query:

SELECT
ResourceName,
ProjectName,
PlannedWork,
ResourceGroup,
StartDate,
type,
Status,
Comment,
PeriodName,
TaskName

FROM

(SELECT
MSP_EpmResource_UserView.ResourceName,
MSP_EpmResource_UserView.ResourceIsActive,
MSP_EpmResource_UserView.”Staff Type” as [type],
MSP_EpmResource_UserView.ResourceGroup,
MSP_TimesheetLine_UserView.ProjectName,
MSP_TimesheetLine_UserView.PeriodName,
MSP_TimesheetLine_UserView.TaskName,
MSP_TimesheetLine_UserView.PlannedWork,
MSP_TimesheetLine_UserView.ActualWorkBillable,
MSP_TimesheetLine_UserView.TimesheetLineStatus,
MSP_TimesheetPeriod.EndDate,
MSP_TimesheetPeriod.StartDate,
MSP_TimeSheetLine.Comment,
case
when MSP_TimeSheetLine.Comment not like ‘%a%’ AND MSP_TimesheetLine_UserView.TimesheetLineStatus not like ‘%pending approval%’ then ‘Unsubmitted’
else MSP_TimesheetLine_UserView.TimesheetLineStatus end as status

FROM
MSP_EpmResource_UserView

LEFT OUTER JOIN
MSP_TimesheetLine_UserView ON
MSP_EpmResource_UserView.ResourceUID = MSP_TimesheetLine_UserView.ResourceUID

LEFT OUTER JOIN
MSP_TimeSheetLine ON
MSP_TimeSheetLine.TimesheetlineUID = MSP_TimeSheetLine_UserView.TimesheetlineUID

LEFT OUTER JOIN
MSP_TimesheetPeriod ON
MSP_TimesheetPeriod.PeriodUID = MSP_TimesheetLine_UserView.PeriodUID

WHERE
(MSP_EpmResource_UserView.ResourceIsActive 0))as x