none
Timesheet Line Status - is it all a lie? RRS feed

  • Question

  • Setup:  Project Server 2010, Single Entry Mode, "Require Line Approval Before Timesheet Approval" checked. (We don't actually use Timesheets, resources always use "send status" rather than "submit timesheet".

    The issue:  When you pull timesheet data (either using the sample report or when you create something less clunky to query the DB, either way) each "line" has a status of either "Approved", "Pending Approval", "Pending", or "Rejected".  I've seen some other people dance around this topic, but I can't find anywhere that it's directly addressed.  The problem is, the "default" status appears to be whatever the previous week was, rather than "Pending".  The only way I've been able to determine if a line status was *really* sent is by filtering either by the sum of work for the line being greater than 0, or by the "comment" field (FYI it's so annoying that Comment is not included in the TimesheetLine_UserView view) not being blank.

    I have been filtering on these items for my own time reporting use (that's one of my fabulous jobs here, bugging people to enter project hours) but now I'm trying to make a report that the Resource Managers can use to monitor their own groups, and it's really tricky, because I want them to be able to see the line items which have actual 0 work, but I don't want to have to explain that the "Approved" or "Pending Approval Status" is a lie.  I may be able to solve this with a "case" statement in the query, but I'm wondering if this is something Microsoft knows about, or if it's an issue that no one has been able to quantify well enough to get it any attention.

    Thoughts?

    Tuesday, August 28, 2012 4:23 PM

All replies

  • Hi Elli,

    Stupid question for my own understanding. If you all require is task updates and no admin time, then why use Single Entry Mode? Why not use the Tasks page instead?

    Also, the Send Status button was intended to be used on an exception basis when just the Task update was needed and not the Timesheet entry as well. It wasn't intended to be used on a regular basis.

    One side effect is that anyone can go back, revise and submit time on a prior timesheet that wasn't previously submitted. Send Status doesn't lock the sheet to further modification.

    As for pulling the data, each line represents an individual timesheet, where the statuses are as follows:

    • Pending - Timesheet created by resource but not submitted
    • Pending Approval - Timesheet created and submitted but not approved
    • Approved - Timesheet process complete with Approved status
    • Rejected - Timesheet process complete with Rejected status 

    What you need to get the list of who hasn't sent their timesheet is a SQL Statement that looks for MSP_Resources which do not have a Timesheet status of Pending Approval, Approved or Rejected for the current period. Pending simply means they clicked on the Timesheet link and the Timesheet was created, but it doesn't mean they did anything. If nothing or Pending, it's the same state, no timesheet.

    I'm not sure I'm answering your question exactly. Please review and let me know your thoughts.

    Treb Gatte @tgatte

    Tuesday, August 28, 2012 6:56 PM
    Moderator
  • Treb,

    Thanks for your reply. I will answer each of your questions as best I can:

    The problem with task updates is that you can't audit the submissions - you can only see the work "appear" in the schedules once it's been approved, which makes it practically impossible to enforce time reporting at the scale we are working with in my company.

    The fact that you can go back, revise and submit time on a prior timesheet is exactly WHY we aren't using the submit timesheet option. We don't want a timesheet to be locked every time the resources make an update. Again, we're not actually using the timesheets functionality.

    I didn't know that "send status" wasn't intended for regular use. What, other than the item listed above, might be the negative repercussions of this?

    I hope I answered your question, but let me now address your explanation above:

    Those are timesheet statuses. We're looking for timesheet line statuses.  We don't need to just know if someone hit 'submit' on their timesheet, we need to see what they have submitted for each task on their list for a given week.

    I have come up with a case statement which works well enough to give me the data that I want- but again, we're dealing with timesheet line status, not timesheet status. I really would just like to know if MS is aware of this 'default value' issue and to find out if they intend to change or fix it at a later date.

    Tuesday, August 28, 2012 8:24 PM
  • Hi Elli,

    Ok, I see. You are attempting to solve the "have you submitted your task update" problem when the data is not in the reporting database. It is in the Published database but that's not documented on how to use.

    The repercussions of the Send Status is that you are using functionality in a way it wasn't intended to be used. It works but it makes me a bit nervous. This also makes for lightly tested scenarios. That said, it's a novel approach to an issue many are having. I applaud your creativity!

    Timesheet line statuses are here: http://msdn.microsoft.com/en-us/library/office/microsoft.office.project.server.library.timesheetenum.linestatus_di_pj14mref.aspx and are in the MSP_TimesheetLine table. The actual daily time entries are in the MSP_TimesheetActuals table. I'd post the ERD of these tables but IE keeps crashing when I attempt to attach to this post.

    As for the default issue, are you at the latest patch level (June 2012 CU?). It may have been fixed already.

    Treb Gatte @tgatte

    Wednesday, August 29, 2012 12:16 AM
    Moderator
  • Here's the ERD Diagram from my ProjConf presentation.

    Treb Gatte @tgatte

    Wednesday, August 29, 2012 12:19 AM
    Moderator
  • Ok Treb, it looks like we're getting closer to a point of understanding!  I really appreciate all your help.  I'm aware of the relationships between the databases, trust me.  It's starting to look to me like my SQL queries for Project Server are a fair sight more complicated than a lot of people out there.  As of yesterday, I'm using a cast function in my select statement to categorize the lines by blank comments and 0 actual work, and it seems to be working. (though I'm not 100% satisfied with it - the "blank" comments aren't actually null, there appears to be some kind of value there, like a paragraph character or something, so I have to use a 'not like' instead of 'is null'...)

    We are not on the June CU yet - we're waiting for a specific hotfix and I'd like to avoid "stab in the dark" updates if I can help it.  There's no mention of this issue as a fix in any hotfixes, which is part of why I'm trying to find out if it's known.  It could be in a fix but just not mentioned anywhere.  I suppose I could try to contact MS directly, but I already have too many open cases, and I was hoping that I wouldn't be the only person who had ever noticed this.

    PS I think the creativity of which you speak is really more a result of desperation and an initial lack of education - that's the root of thinking outside the box, right? :)  I don't know what the system can't do, so I never take no for an answer!

    Wednesday, August 29, 2012 3:30 PM
  • Hi Elli,

    Ok, just level setting with the database stuff as the person looking at this post in 6 months might not know.

    I'll take a look in our system and see if I see the same thing. Any chance you can post your query? That would help me see it exactly as you do.

    I doubt you would see this particular issue listed explicitly in a CU description, even if it was fixed. The items listed tend to be the high level description of the fix, even if something like this was fixed as part of another issue.

    It could be the fact that you are off the beaten path as far as tool use is why no one else has seen this. :-)

    Treb Gatte @tgatte

    Wednesday, August 29, 2012 4:34 PM
    Moderator
  • Treb,

    Ok, here's my query.  I took out the case statement, obviously...since that fixes the problem. :)

    When you scan down a tabulated pivot table of this data (organized by first resource, then task name, then periodname, then line status) you'll see that the TimesheetLineStatus doesn't look specific to each period, making it useless to see if a resource submitted that line for that period.  However, if you add the comment line, you can see that approved status suddenly becomes a "real" value for certain lines - if there's no comment, it wasn't approved! (supported by the fact that there will be no actual work for those weeks, either)  My thought was that the TimeSheetLine status should be 'pending' by default, meaning not submitted, until it actually HAS been submitted, then it should change to "Pending Approval", "Rejected", or "Approved."  Instead, it seems like the status from the previous week carries into the next until it's submitted again.  Is this making sense?

    SELECT
    MSP_EpmResource_UserView.ResourceName,
    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

    FROM
    MSP_TimesheetLine_UserView

    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

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

    WHERE
    (MSP_TimesheetPeriod.StartDate <current_timestamp-7)

    Wednesday, August 29, 2012 5:19 PM
  • Elli,

    I figured out the TimesheetLineStatus mystery. Basically, the lines that are marked either Pending or Pending Approval are the lines that have time entries for the current period. Otherwise, no data was updated. So, you can ignore the lines that say Approved or Rejected as they've not been modified from the prior period.

    After tracing through this, I can see why this is driving you crazy. There are basically three states to check in order to determine if the person has created and entered information in the timesheet.

    The underlying cases to check are as follows.

    • Determine if the person created a timesheet. So, you have to compare the resource table to the MSP_Timesheet table
    • Did the resource create a timesheet but enter no data? If they clicked on the Timesheet link but did nothing, no TimesheetLines exist yet. The only record of them doing so is that a record now exists in the Timesheet table.
    • Has the resource entered time and either saved or submitted it? This is determined by whether the timesheetline status contains Pending.

    So, the query below provides a list of resources which fit into one of the three above categories for the current period. I don't think it's the most optimal query but it finds the right people in the right state. It only returns the list of resources at this point and the timesheet status. Since I did this as modular queries and unioned the results, you can easily take each query block and make it it's own report.

    Hope this helps. Thanks for providing a great mystery to explore!

    Treb Gatte @tgatte

    SELECT 'No Timesheet' as TSStatus , ResourceName , ResourceUID FROM MSP_EpmResource_UserView --Looks for those resources which have not clicked the timesheet link and created a timesheet WHERE ResourceUID NOT IN (SELECT MSP_TimesheetResource.ResourceUID FROM MSP_Timesheet INNER JOIN MSP_TimesheetResource ON MSP_Timesheet.OwnerResourceNameUID = MSP_TimesheetResource.ResourceNameUID WHERE (MSP_Timesheet.PeriodUID = (SELECT PeriodUID FROM MSP_TimesheetPeriod WHERE (StartDate <= CURRENT_TIMESTAMP) AND (EndDate >= CURRENT_TIMESTAMP)))) AND ResourceType = 2 --Work Resource AND ResourceIsActive = 1 --Active AND ResourceNTAccount IS NOT NULL --Has a user account so can generate a timesheet

    UNION SELECT 'No Timesheet Entries' as TSStatus, ResourceName, ResourceUID FROM MSP_EpmResource_UserView --Looks for those resources which have created a timesheet but entered no time WHERE ResourceUID IN (SELECT MSP_TimesheetResource.ResourceUID FROM MSP_Timesheet INNER JOIN MSP_TimesheetResource ON MSP_Timesheet.OwnerResourceNameUID = MSP_TimesheetResource.ResourceNameUID WHERE (MSP_Timesheet.PeriodUID = (SELECT PeriodUID FROM MSP_TimesheetPeriod WHERE (StartDate <= CURRENT_TIMESTAMP) AND (EndDate >= CURRENT_TIMESTAMP)))) AND ResourceUID NOT IN (SELECT ResourceUID FROM MSP_TimesheetLine_UserView WHERE MSP_TimesheetLine_UserView.PeriodUID = (SELECT PeriodUID FROM MSP_TimesheetPeriod WHERE (StartDate <= CURRENT_TIMESTAMP) AND (EndDate >= CURRENT_TIMESTAMP))) AND ResourceType = 2 --Work Resource AND ResourceIsActive = 1 --Active AND ResourceNTAccount IS NOT NULL --Has a user account so can generate a timesheet

    UNION SELECT 'Updates Pending Approval' as TSStatus , ResourceName , ResourceUID FROM MSP_EpmResource_UserView --Looks for those resources which entered time but haven't been approved yet WHERE ResourceUID IN (SELECT MSP_TimesheetLine_UserView.ResourceUID FROM MSP_TimesheetLine_UserView INNER JOIN MSP_TimesheetPeriod ON MSP_TimesheetPeriod.PeriodUID = MSP_TimesheetLine_UserView.PeriodUID WHERE (MSP_TimesheetLine_UserView.TimesheetLineStatus LIKE '%Pending%') AND MSP_TimesheetLine_UserView.PeriodUID = (SELECT PeriodUID FROM MSP_TimesheetPeriod WHERE (StartDate <= CURRENT_TIMESTAMP) AND (EndDate >= CURRENT_TIMESTAMP))) AND ResourceType = 2 --Work Resource AND ResourceIsActive = 1 --Active AND ResourceNTAccount IS NOT NULL --Has a user account so can generate a timesheet


    Thursday, August 30, 2012 5:07 AM
    Moderator
  • First, that's a brilliant query and I might hit you up for some help with a couple other queries that I'm working on. :)

    However, I'm not 100% sold on your explanation of how the line statuses work.  Are you saying that if a resource creates a timesheet, updates and submits their status, and the project manager approves the time, the line will still say "pending" or "pending approval"?  If no line items have been approved in your dataset, then you won't be able to see what I'm talking about - when the item gets approved, the status changes to approved, and the only way to tell the difference between a real status of 'approved' and a "fake" status of approved is to check if there is actual work, or a comment on that item.

    Can you replicate this at all?

    Thursday, August 30, 2012 3:59 PM
  • Elli,

    No, the line status will show approved/rejected. That is by design behavior.

    Did you attempt to do this using OLAP? If I do a Who did What type report, OLAP usually works better for me. Just wondering.

    Treb Gatte @tgatte

    Thursday, August 30, 2012 4:42 PM
    Moderator
  • So now I feel like we're running around in circles again...

    My point is that the only way to tell between a "real approved" and "fake approved" status is by the comments or work.  It seems to me like every line should have a new default status for a new period until it is created or submitted.  Your query will help me find those who haven't created a timesheet (I already  have a mechanism for doing this, but yours is admittedly cooler) and those who have submitted time that hasn't been approved, but it doesn't also find time that has been submitted and approved- which means that the lines which have been submitted and approved will go into the same list as those which haven't been submitted...

    Thursday, August 30, 2012 4:51 PM
  • Elli,

    I have some other ideas so I'll get back to you shortly.

    Treb Gatte @tgatte

    Friday, August 31, 2012 5:45 PM
    Moderator
  • OK Elli, I think I'm on the same page as you now - or at least a nearby chapter. I think you know all the following - but just saying out loud to level set, as this is the design that is causing you the pain. The timesheets have a period, but the underlying assignment may span several timesheets.  The submittal of entered time will submit all time for that assignment - even across other timesheets.  So if I have 4 timesheets covering 4 weeks, with one assignment on all of them, and have the first timesheet is approved - and I then add time across all three other timesheets, then submit status for just the 2nd timesheet I will see just this line showing Pending Approval.  The other subsequent lines will still be pending as far as the timesheet line status is concerned.  However, if I navigate to one of the other timesheets and make a change (add some admin time for example) and then save - the line on the assignment submitted on the prior timesheet will now show as Pending Approval.

    If I now approve the status update then all 4 timesheet lines will now show as status 1 - approved.  2 will have gone from 4 to 1, and the remaining one will have been 0 as it hadn't been touched and will go straight to 1.

    That is all 'by design'.  The other scenario is deleting tasks and I cover this in the blog post from today - but I'm guessing that is the least of your issues.  If you can clarify what you are wanting from your report then I can try and think of other options - or maybe my description above may help you confirm what you are seeing in your reports.

    Best regards,

    Brian.


    Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page

    Wednesday, February 19, 2014 10:10 PM
    Owner
  • Brian,

    You have successfully explained to me the reason for the behavior - thank you!  At least now I understand the reasons behind it.

    Maybe there is a better solution to what we are doing - as I stated at the start of the thread, we aren't really using timesheets, we just need a weekly time submission per task per resource, and we need that update to hit the reporting database before the PM approves it - so we can see if the resource has submitted time even if it's not showing up as actuals in the project schedule.

    Task Updates don't hit the reporting db, so SEM with "Require Line Approval Before Timesheet Approval" was the only way we could find to put the data where we could get to it.

    You can see where this becomes a problem - the timesheet status is meaningless to us - we want to know, per line, per week, if the item has been submitted by the resource.  The PM approval is a vital piece of that, and from what I can tell, the only difinitive way to know that an individual line for a specific week (timesheet period) has been approved is that there will be a comment there, by default showing the PM's login and date stamp. 

    I have seen evidence that other people are looking for this weekly, assignment specific data on whether or not individual resources have submitted hours, but other than my somewhat jury rigged query (with the caveat that it being approved out of order can mess up the data) I haven't found a solution for this.


    Elli J Project Solutions Specialist Blog: http://projectserverpants.wordpress.com/

    Wednesday, February 19, 2014 10:36 PM