Which reporting (other) database table/view for determining whichprojects a resource has been made a

[anovak]

Given that I have an the resource uid, I want to know which projects
this person is team member on (regardless of whether they are assigned
tasks or not). Which database? Which view/table? Which columns?

Thanks,
Andy Novak
UNT

 

[Gary Chefetz]

Andy:

Have you downloaded the SDK which contains the data dictionary for the RDB?

 

[anovak]

Andy:

Have you downloaded the SDK which contains the data dictionary for the RDB?

--
Gary L Chefetz, MVP, MCT, MCITP
msProjectExpertshttp://www.msprojectexperts.com
FAQs and Referencehttp://www.projectserverexperts.com
BLOG:http://projectserverhelp.com

Yes, but I just thought someone would know off the of their head if
its even possible to query on that condition. I have an idea already
based on perusing through the database schemas in the SQL Mgt Studio,
but wanted to get some type of affirmation from another party as well.

 

[.jussi]

Hi Andy,

I am unsure whether this information is available through the _Reporting
database. The active views on both Project Pro and PWA use the _Published
database, and it may be that the project team is not visible in reporting.
I've used the reporting database quite extensively and don't recall ever
seeing this information there.

That said, in the _Published database (which you should never touch,
development towards it is unsupported and all the other normal caveats), it
is easily available in dbo.MSP_PROJECT_RESOURCES.

Simply the fact that it is so obviously available in the pub database would
suggest that since it is not easily seen in the reporting db, it is not there.

Hope that helps,
- Jussi

 

[Jonathan Sofer [MVP]]

I believe the team membership on projects can only be reported out of the
published database. The reporting database only stores resource assignments.
Disclaimer: Reading from the published DB is not recommened by Microsoft.

Here is a sample query:
SELECT TOP (100) PERCENT dbo.MSP_PROJECT_RESOURCES.PROJ_UID,
dbo.MSP_PROJECTS.PROJ_NAME,
dbo.MSP_PROJECT_RESOURCES.RES_UID AS ResourceCount,
dbo.MSP_RESOURCES.RES_NAME
FROM dbo.MSP_PROJECT_RESOURCES INNER JOIN
dbo.MSP_RESOURCES ON dbo.MSP_PROJECT_RESOURCES.RES_UID
= dbo.MSP_RESOURCES.RES_UID INNER JOIN
dbo.MSP_PROJECTS ON dbo.MSP_PROJECT_RESOURCES.PROJ_UID
= dbo.MSP_PROJECTS.PROJ_UID
ORDER BY dbo.MSP_PROJECTS.PROJ_NAME, dbo.MSP_RESOURCES.RES_NAME

Jonathan