Who is on a team?

J

John Sitka

PS 2003

I need to audit who is on an single Enterprise Project Team across hundreds of Published projects

ProjectA.Published Bill, Jane, Jill
ProjectB.Published Bill, Jane
ProjectC.Published Bill, Jane
ProjectD.Published Jane, Jill, CustomerX

......

just the tables or views where this is membership is identified would be fine

Thanks.
 
J

James Fraser

PS 2003

I need to audit who is on an single Enterprise Project Team across hundreds of Published projects

ProjectA.Published Bill, Jane, Jill
ProjectB.Published Bill, Jane
ProjectC.Published Bill, Jane
ProjectD.Published Jane, Jill, CustomerX

.....

just the tables or views where this is membership is identified would be fine

Thanks.
Click to expand...

MSP_RESOURCES


James Fraser
 
J

John Sitka

Thanks
immediate audit needs served by...

SELECT C.PROJ_ID, C.PROJ_NAME,
B.PROJ_ID,
B.RES_UID,
B.RES_NAME FROM ProjectServer.dbo.MSP_PROJECTS C
LEFT JOIN ProjectServer.dbo.MSP_RESOURCES B
ON C.PROJ_ID = B.PROJ_ID
WHERE C.PROJ_ID NOT IN (the set of all projects you would like to exclude)
AND
B.RES_UID >=1
ORDER BY C.PROJ_ID,B.RES_NAME

absence makes the brain grow softer.
 
D

Dale Howard [MVP]

John --

An alternate approach would be to create a custom Portfolio Analyzer view.
If you drop the Project dimension and the Resources dimension in the Row
Fields drop area, you have a PivotTable that you can quickly and easily
export to Excel with the data you want to see. You could even add the Work
or Cost total fields to the Total Fields drop area for totals on Work or
Cost. Hope this helps.
 
J

John Sitka

Good trick, but not quite what I needed this time???

I've included external customer PWA accounts as team members
but some just watch progress and don't have assignments.

The audit is to keep track of managers who do a Save As for a project
and don't double check for the correct customer on their published plans.



Dale Howard said:
John --

An alternate approach would be to create a custom Portfolio Analyzer view. If you drop the Project dimension and the Resources
dimension in the Row Fields drop area, you have a PivotTable that you can quickly and easily export to Excel with the data you
want to see. You could even add the Work or Cost total fields to the Total Fields drop area for totals on Work or Cost. Hope
this helps.
Click to expand...
 
Top