James,
go ahead and get grumpy....but your discussion, which i've seen several time
prior to posting this, does not address how to look at this data across ALL
Resources...since i only see 'my' tasks, as a PWA Admin, i cannot see what
everyone has on their My Tasks page...
I was fearing that the it was binary data / image and so i appreciate
In case I wasn't clear, my grumpiness is directed at MS for taking
away something we could report on in 2003.
Since you indicated that you didn't find a clear answer in your
searches, I'll try to provide one here for future search engine
indexing...
A High Level Outline of Reporting on Task Updates ("My Tasks") for All
Users Across the System
Project Server 2007 was designed to allow easy reporting on published
project data and all timesheet data. Other aspects of Project Server
can be very difficult to capture or report on. Some data is not in the
reporting database but can be found easily in the draft or published
databases. Task predecessors and successors and user last connect
times are two frequently requested pieces that can be found in tables
in these databases.
Other data only exist in the draft or published databases _and_ are
stored in a binary object. Think of this as really lightweight
encryption. The data is structured for the application to read, not a
human. 0x3F22 represents January 1, 2008, for example. The Task Update
data falls into this category. It is possible to write code to parse
this data, but dealing with all potential datatypes will be difficult
without knowing what could go into the data.
Enough geeky talk, what are the options?
1.
Use SQL to query the forbidden Published database. You can look at
what resources have updates pending for which projects and tasks. You
can not find out about the "contents" of the update: this data is
stored in a binary object in the TASK_CHANGE_DATA field of the
MSP_ASSIGNMENT_TRANSACTION table. Not too much effort thanks to B
Weed's code (see below)
2.
Write a PSI application to connect repeatedly to the server
impersonating every possible status manager, gathering all the pending
updates. This requires some development effort, and the final product
will probably not be very fast. Might be a good over night reporting
task, though.
3.
Do you really need to? Reporting on Timesheet entries is much easier.
Also, I've reformatted B Weed's SQL from the post mentioned above:
http://groups.google.com/group/microsoft.public.project.server/msg/6d149a0deb955914
SELECT
Case TR.ASSN_TRANS_ACTION_ENUM
When 0 Then 'Pending'
When 1 Then 'Accepted'
When 2 Then 'Rejected' End As Accepted,
CASE
WHEN P.WPROJ_LAST_PUB > TR.ASSN_TRANS_SUBMIT_DATE
THEN 'Yes'
ELSE 'No'
END AS Is_Published,
A.TASK_NAME,
p.PROJ_NAME,
R.RES_NAME as ResourceName,
RB.RES_NAME as StatusManager,
TR.ASSN_TRANS_SUBMIT_DATE
FROM
dbo.MSP_ASSIGNMENT_TRANSACTIONS TR WITH(READPAST)
LEFT OUTER JOIN
dbo.MSP_CONVERSIONS C
ON
((C.CONV_VALUE = TR.ASSN_TRANS_ERROR_ENUM)
AND (STRING_TYPE_UID =
'EB15F998-82B2-4E2E-BFD4-BBF15AD90C23'))
LEFT OUTER JOIN
dbo.MSP_ASSIGNMENTS_SAVED A WITH(READPAST)
ON TR.ASSN_UID = A.ASSN_UID
LEFT OUTER JOIN
dbo.MSP_PROJECTS p
ON A.PROJ_UID = p.PROJ_UID
LEFT OUTER JOIN
dbo.MSP_TASKS_SAVED TA
ON TR.TASK_UID = TA.TASK_UID
LEFT OUTER JOIN
dbo.MSP_PROJECT_RESOURCES R
ON A.RES_UID = R.RES_UID
AND R.PROJ_UID = p.PROJ_UID
LEFT OUTER JOIN
dbo.MSP_PROJECT_RESOURCES RD
ON TR.ASSN_TRANS_DELEGATEE_RES_UID = RD.RES_UID
AND p.PROJ_UID = RD.PROJ_UID
LEFT OUTER JOIN
dbo.MSP_RESOURCES RD1
ON TR.ASSN_TRANS_DELEGATEE_RES_UID = RD1.RES_UID
LEFT OUTER JOIN
dbo.MSP_PROJECT_RESOURCES RS
ON TR.ASSN_TRANS_SUBMITTER_RES_UID = RS.RES_UID
AND p.PROJ_UID = RS.PROJ_UID
LEFT OUTER JOIN
dbo.MSP_RESOURCES RS1
ON TR.ASSN_TRANS_SUBMITTER_RES_UID = RS1.RES_UID
LEFT OUTER JOIN dbo.MSP_RESOURCES RA
ON TR.ASSN_TRANS_APPROVER_RES_UID = RA.RES_UID
LEFT OUTER JOIN
dbo.MSP_RESOURCES RB
ON A.WRES_UID_Manager = RB.RES_UID
WHERE
((TR.ASSN_TRANS_ACTION_ENUM = 1
and p.WPROJ_LAST_PUB < TR.ASSN_TRANS_SUBMIT_DATE)
OR tr.ASSN_TRANS_ACTION_ENUM = 0)
and RA.RES_NAME is not null
ORDER BY p.PROJ_NAME
