What table contains the data displayed in 'My Tasks' view?

[JenStover]

MS Project Server 2007

Does anyone know what table contains the data displayed in My Tasks?

 

[Marc Soester [MVP]]

Not sure which table it is, but when utilising the reporting db you can get
this info of the EPM task user view.
Hope this helps

 

[Lizzie Beth]

I am trying to pull the My Task information for one or more resources for a
specific period. I've used the userview you mentioned as well as the
userviews for epm assignment by day, epm resource, epm assignment, and epm
project. I want to show the resource name, project name, task, work day,
actual work for task.

I'm able to pull some information that is posted to the plan yet other
information is not showing. I'm not able to see work submitted yet not
posted.

Is there something I'm missing? Is there a better way to get this data?

Thank you,
Lizzie Beth

 

[Barbara - Austria]

Hi Lizzie,

I searched for work submitted some time ago. My results:
- work submitted can only be found in published DB
- work submitted by day is stored in binary format in database, so not
readable with Reporting Services

I was only able to get work submitted as a summary with following
statement (Published DB!!!):
SELECT dbo.MSP_RESOURCES.RES_NAME, dbo.MSP_PROJECTS.PROJ_NAME,
Selected_ASSN.TASK_NAME, Selected_ASSN.Update_Status,
MSP_RESOURCES_1.RES_NAME AS Status_MGR, Selected_ASSN.MOD_DATE,
Selected_ASSN.Stunden AS Hours_DIFF
FROM (SELECT dbo.MSP_ASSIGNMENTS_SAVED.ASSN_UID,
dbo.MSP_ASSIGNMENTS_SAVED.RES_UID, dbo.MSP_ASSIGNMENTS_SAVED.PROJ_UID,
dbo.MSP_ASSIGNMENTS_SAVED.TASK_NAME, CASE WHEN
dbo.MSP_ASSIGNMENTS_SAVED.WASSN_SEND_UPDATE_NEEDED = 1 AND
MSP_ASSIGNMENTS_SAVED.WASSN_ACTUALS_PENDING = 0 THEN 'Saved' WHEN
dbo.MSP_ASSIGNMENTS_SAVED.WASSN_SEND_UPDATE_NEEDED = 1 AND
MSP_ASSIGNMENTS_SAVED.WASSN_ACTUALS_PENDING = 2 THEN 'Rejected' WHEN
dbo.MSP_ASSIGNMENTS_SAVED.WASSN_SEND_UPDATE_NEEDED = 0 AND
MSP_ASSIGNMENTS_SAVED.WASSN_ACTUALS_PENDING = 0 THEN 'Submitted' WHEN
dbo.MSP_ASSIGNMENTS_SAVED.WASSN_SEND_UPDATE_NEEDED = 1 AND
MSP_ASSIGNMENTS_SAVED.WASSN_ACTUALS_PENDING = 1 THEN 'Submitted' WHEN
dbo.MSP_ASSIGNMENTS_SAVED.WASSN_SEND_UPDATE_NEEDED = 0 AND
MSP_ASSIGNMENTS_SAVED.WASSN_ACTUALS_PENDING = 1 THEN 'Accepted' END AS
Update_Status, dbo.MSP_ASSIGNMENTS_SAVED.WRES_UID_MANAGER,
dbo.MSP_ASSIGNMENTS_SAVED.MOD_DATE,
(dbo.MSP_ASSIGNMENTS_SAVED.ASSN_ACT_WORK -
ISNULL(dbo.MSP_ASSIGNMENTS.ASSN_ACT_WORK, 0)) / 60000 AS Stunden
FROM dbo.MSP_ASSIGNMENTS_SAVED LEFT OUTER JOIN
dbo.MSP_ASSIGNMENTS ON dbo.MSP_ASSIGNMENTS_SAVED.ASSN_UID =
dbo.MSP_ASSIGNMENTS.ASSN_UID
WHERE ((dbo.MSP_ASSIGNMENTS_SAVED.ASSN_ACT_WORK -
ISNULL(dbo.MSP_ASSIGNMENTS.ASSN_ACT_WORK, 0)) / 60000 <> 0)) AS
Selected_ASSN INNER JOIN
dbo.MSP_RESOURCES ON Selected_ASSN.RES_UID = dbo.MSP_RESOURCES.RES_UID
INNER JOIN
dbo.MSP_PROJECTS ON Selected_ASSN.PROJ_UID = dbo.MSP_PROJECTS.PROJ_UID
INNER JOIN
dbo.MSP_RESOURCES AS MSP_RESOURCES_1 ON Selected_ASSN.WRES_UID_MANAGER
= MSP_RESOURCES_1.RES_UID
WHERE (Selected_ASSN.Stunden <> 0)

I hope that helps?

If someone knows a better way to get this data, I am really interested in!

Regards
Barbara

Am 18.02.2010 16:46, schrieb Lizzie Beth:

 

[Lizzie Beth]

Barbara,
This is very helpful!

Lizzie

Hi Lizzie,

I searched for work submitted some time ago. My results:
- work submitted can only be found in published DB
- work submitted by day is stored in binary format in database, so not
readable with Reporting Services

I was only able to get work submitted as a summary with following
statement (Published DB!!!):
SELECT dbo.MSP_RESOURCES.RES_NAME, dbo.MSP_PROJECTS.PROJ_NAME,
Selected_ASSN.TASK_NAME, Selected_ASSN.Update_Status,
MSP_RESOURCES_1.RES_NAME AS Status_MGR, Selected_ASSN.MOD_DATE,
Selected_ASSN.Stunden AS Hours_DIFF
FROM (SELECT dbo.MSP_ASSIGNMENTS_SAVED.ASSN_UID,
dbo.MSP_ASSIGNMENTS_SAVED.RES_UID, dbo.MSP_ASSIGNMENTS_SAVED.PROJ_UID,
dbo.MSP_ASSIGNMENTS_SAVED.TASK_NAME, CASE WHEN
dbo.MSP_ASSIGNMENTS_SAVED.WASSN_SEND_UPDATE_NEEDED = 1 AND
MSP_ASSIGNMENTS_SAVED.WASSN_ACTUALS_PENDING = 0 THEN 'Saved' WHEN
dbo.MSP_ASSIGNMENTS_SAVED.WASSN_SEND_UPDATE_NEEDED = 1 AND
MSP_ASSIGNMENTS_SAVED.WASSN_ACTUALS_PENDING = 2 THEN 'Rejected' WHEN
dbo.MSP_ASSIGNMENTS_SAVED.WASSN_SEND_UPDATE_NEEDED = 0 AND
MSP_ASSIGNMENTS_SAVED.WASSN_ACTUALS_PENDING = 0 THEN 'Submitted' WHEN
dbo.MSP_ASSIGNMENTS_SAVED.WASSN_SEND_UPDATE_NEEDED = 1 AND
MSP_ASSIGNMENTS_SAVED.WASSN_ACTUALS_PENDING = 1 THEN 'Submitted' WHEN
dbo.MSP_ASSIGNMENTS_SAVED.WASSN_SEND_UPDATE_NEEDED = 0 AND
MSP_ASSIGNMENTS_SAVED.WASSN_ACTUALS_PENDING = 1 THEN 'Accepted' END AS
Update_Status, dbo.MSP_ASSIGNMENTS_SAVED.WRES_UID_MANAGER,
dbo.MSP_ASSIGNMENTS_SAVED.MOD_DATE,
(dbo.MSP_ASSIGNMENTS_SAVED.ASSN_ACT_WORK -
ISNULL(dbo.MSP_ASSIGNMENTS.ASSN_ACT_WORK, 0)) / 60000 AS Stunden
FROM dbo.MSP_ASSIGNMENTS_SAVED LEFT OUTER JOIN
dbo.MSP_ASSIGNMENTS ON dbo.MSP_ASSIGNMENTS_SAVED.ASSN_UID =
dbo.MSP_ASSIGNMENTS.ASSN_UID
WHERE ((dbo.MSP_ASSIGNMENTS_SAVED.ASSN_ACT_WORK -
ISNULL(dbo.MSP_ASSIGNMENTS.ASSN_ACT_WORK, 0)) / 60000 <> 0)) AS
Selected_ASSN INNER JOIN
dbo.MSP_RESOURCES ON Selected_ASSN.RES_UID = dbo.MSP_RESOURCES.RES_UID
INNER JOIN
dbo.MSP_PROJECTS ON Selected_ASSN.PROJ_UID = dbo.MSP_PROJECTS.PROJ_UID
INNER JOIN
dbo.MSP_RESOURCES AS MSP_RESOURCES_1 ON Selected_ASSN.WRES_UID_MANAGER
= MSP_RESOURCES_1.RES_UID
WHERE (Selected_ASSN.Stunden <> 0)

I hope that helps?

If someone knows a better way to get this data, I am really interested in!

Regards
Barbara

Am 18.02.2010 16:46, schrieb Lizzie Beth:
.