none
TaskUID and timesheets (SQL) RRS feed

  • Question

  • Hi All,

    I have written a query to produce a report outlining peoples timesheets.  This works perfectly and the data returned matches what is entered into the Project Web App.  

    However if I attempt to bring across TASK related information it appears that there are errors.  In particular time is being allocated to TASKUID's that individuals are not allocated to.

    If I query the table MSP_EpmAssignment I can review the TASKUID for a resource.  If I then query any of the timesheet related tables (e.g. MSP_TimesheetActual) I see time has been entered against TASKUID's that are not the same as those in the assignment table.

    I understand that TASKUID does not have a uniqueness constraint,  however I am unsure what would be causing this referential integrity issue.    

    Is this common?

    Thanks in advance, Clint

    Sunday, September 9, 2012 4:56 AM

All replies

  • Clint, 

    Are you joining via MSP_TimesheetTask to get the correct TaskUID?

    Thanks!

    Treb Gatte @tgatte

    Sunday, September 9, 2012 5:19 AM
    Moderator
  • Hi Treb,

    Thanks for the response.  Yes I do.  Please see below

    Here is an example query:

    SELECT
    TSL.TimesheetLineUID, 
    TSL.ProjectNameUID,
    TSL.TaskNameUID,
    TST.TaskUID,
    TSR.ResourceUID,
    ASN.TaskUID
    FROM MSP_TimesheetLine TSL
    INNER JOIN MSP_TimesheetProject TSP ON TSP.ProjectNameUID = TSL.ProjectNameUID
    INNER JOIN MSP_TimesheetTask TST ON TST.TaskNameUID = TSL.TaskNameUID
    INNER JOIN MSP_Timesheet TS ON TS.TimesheetUID = TSL.TimesheetUID
    INNER JOIN MSP_TimesheetResource TSR ON TSR.ResourceNameUID = TS.OwnerResourceNameUID
    LEFT JOIN MSP_EpmAssignment ASN ON ASN.ProjectUID = TSP.ProjectUID AND ASN.TaskUID = TST.TaskUID AND ASN.ResourceUID = TSR.ResourceUID

    What is interesting here is the join to MSP_EpmAssignment (ASN)

    Result:

    I get three rows.  Data from all tables.  But two of the rows receive no data from MSP_EpmAssignment (thus the left join)

    Sunday, September 9, 2012 5:49 AM
  • Take a look at this thread:

    http://social.msdn.microsoft.com/Forums/en-US/project2010custprog/thread/5202451a-bdfa-45bf-a975-50eb00edeefc

    /Lars H


    //Lars Hammarberg www.dovregroup.com Gold Certified Partner

    Sunday, September 9, 2012 6:00 PM
  • Hi Lars,

    Thank you for responding,  I had come across some of your other posts on this issue and had built a function using your code as a base to check the result set of this task issue.

    What I found is that the function does not help me.

    Here is a query:

    SELECT distinct
    T.TaskUID,
    T.TaskName, 
    dbo.ufn_FindUltimateTaskUIDForTN(T.TaskNameUID) FnTaskUID
    from MSP_TimeSheetTask T
    JOIN MSP_TimesheetLine L ON L.TaskNameUID = T.TaskNameUID
    LEFT JOIN MSP_EpmAssignment A ON A.AssignmentUID = L.AssignmentUID
    JOIN MSP_EpmProject P ON P.ProjectUID = A.ProjectUID
    INNER JOIN MSP_TimesheetActual AS S ON L.TimesheetLineUID = S.TimesheetLineUID
    WHERE t.TaskUID = '7E1651F6-388C-4CB7-A5CA-FC170A88D5ED'

    The result of this is:

    T.Taskuid, T.taskname, Function Result

    7E1651F6-388C-4CB7-A5CA-FC170A88D5ED Project Manager 7E1651F6-388C-4CB7-A5CA-FC170A88D5ED
    7E1651F6-388C-4CB7-A5CA-FC170A88D5ED Solution Architect 7E1651F6-388C-4CB7-A5CA-FC170A88D5ED

    This suggests that the parenttaskuid is the same.

    There are two issues here.  

    1. The TASKUID has two task names.  And as such the timesheet data is reported incorrectly.

    2. If I add the ResourceName to the WHERE clause I do not get any results.

    It appears that the Assignment table has differing TaskUID's assigned to resources.

    I am at a bit of a loss on how to tackle this.  To me it seems like a bit of a data issue.

    Monday, September 10, 2012 3:12 AM
  • A little known fact is that AssignmentUID is not a unique number when it comes to timesheets...

    Try this to verify the above:

    Open Project Pro with a task usage view and a split view showing resource assignements in the lower pane. Display the GUID field in the task usage pane - this field will show task guids and assignment guids.

    Create a task and assign a resource to the task. Save and publish.

    Sign in to pwa with the above used resource account and create a timesheet entry, add some hours actual work and save the entry. This step is not necessary to prove the above statement, but serves to show the assignment in the timesheet database tables.

    Now change the resource in the plan using the lower pane - note the re-use of the previous assignment guid ! Save and publish.

    Create a timesheet for the newly assigned resource and - after adding some actual work - run the following query against the reporting database to verify the above (if just looking at the task usage guid field isn't enough):

    SELECT ts.OwnerResourceNameUID, tsl.* FROM [dbo].[MSP_TimesheetLine] tsl inner join [dbo].[MSP_Timesheet] ts on tsl.TimesheetUID = ts.TimesheetUID order by [CreatedDate] desc

    Note the re-use of the assignmentuid... the assignmentuid will, as far as I've been able to determine, match the latest entry in the plan when querying the project database tables.

    /Lars H


    //Lars Hammarberg www.dovregroup.com Gold Certified Partner


    Monday, September 10, 2012 7:52 AM