Report on Tasks Pending Approval

[RQ]

Using 2007 -

We have created a project plan and assigned tasks to several Status Managers
for approval. We want to know if we can create a report of tasks pending
approval by Status Manager.

I am wondering if this will be available once we setup the Data Analysis
reports

RQ

 

[Paul Conroy]

This information isn't available in the cube or reporting database, however
you may be able to obtain it programmatically.

A different approach would be to report against all tasks which have overdue
work which implies that either and update is pending or that the work has not
been completed.

 

[Piet Remen]

Hi RQ. As Paul said, the information is not available from the cube or
reporting database. However I believe you can extract this information from
the publish or even the draft database using SQL calls. I think you may have
to scan the assignment tables for any potential changes made but not
approved, then link that back to determine what tasks from what project have
not been approved.

The following is a SQL sample I hope may get you started.

SELECT
atc.ASSN_TRANS_DATE_ENTERED
,t.TaskUID AS TaskUID
,t.TaskName
FROM ProjectServer_Reporting.dbo.MSP_EpmTask_UserView AS t
INNER JOIN ProjectServer_Published.dbo.MSP_TASKS_SAVED AS ts
ON t.TaskUID = ts.TASK_PUBLISHED_UID
INNER JOIN ProjectServer_Published.dbo.MSP_ASSIGNMENT_TRANSACTIONS AS at
ON ts.TASK_UID = at.TASK_UID
AND ts.PROJ_UID = at.PROJ_UID
WHERE (at.ASSN_TRANS_ACTION_ENUM = 1)

Also check out the MSP_TASKS_SUBMITTED and MSP_TASKS_SAVED tables to see if
they can identify pending approval tasks for you, which may be a cleaner way
then the assignment tables. Hope this helps.

Regards,

Piet Remen

 

[RQ]

Paul, Piet

Thank you, this will give us some next steps to look into

RQ

 

[B Weed]

Here is another query to try.
The Accepted Column indicates if the time has been Accepted, Pending
or Rejected. The IS_Published column indicates if the plan has been
published since the entry into the table. We use the query to
identify Time that has been accepted but the project not publsihed and
also for Pending Approvals. Accepted with IS_Published is NO would be
the first and anything Pending would be the second. The ModDate on a
pending would be the time the resource submitted the time. On an
accepted entry, it would be the time the PM accepted the time. The
STRING TYPE UID is probably specific to my environment, so you would
need to replace accordingly. Hope this helps also.

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 = 0 or P.WPROJ_LAST_PUB <
TR.ASSN_TRANS_SUBMIT_DATE) and
RA.RES_NAME is not null
ORDER BY TR.ASSN_TRANS_SUBMIT_DATE

 

[Kevin]

Hi there, I think your SQL statement will be of great value. However it
seems you have only posted the FROM section down. I would be very keen to
see the SELECT half of the statement to see which columns you are getting
from this. I have tried to look at them, but doing a SELECT * returns so
many rows it is hard to know exactly what I am looking at.

Thanks in advance.

 

[B Weed]

My apologizes. Here is the select statement we are using.

SELECT Accepted = Case TR.ASSN_TRANS_ACTION_ENUM When 0 Then
'Pending' When 1 Then 'Accepted' When 2 Then 'Rejected' End,
IS_PUBLISHED = CASE WHEN P.WPROJ_LAST_PUB >
TR.ASSN_TRANS_SUBMIT_DATE THEN 'Yes' ELSE 'No' END,
A.TASK_NAME, P.PROJ_NAME, R.RES_NAME, RB.RES_NAME,
TR.ASSN_TRANS_SUBMIT_DATE

 

[Kevin]

You are a dead set champion. Thanks so much.

Where did you source what the ASSN_TRANS_ACTION_EMUN values meant? I an't
seem to locate it on MSDN.