SQL Query to Get Project Resources by Role

I was developing some reports on Project Server 2003 recently, while working I got a Question.

Question : I need to get Projects, Its Resources, Resource Role in Project Server and Project Created by in a report.

The query to get this done is give below.

SELECT     MSP_PROJECTS.PROJ_NAME AS Project, MSP_WEB_RESOURCES.RES_NAME AS Resource,
                      MSP_WEB_SECURITY_GROUPS.WSEC_GRP_NAME AS [Role In Project], MSP_PROJECTS.PROJ_PROP_AUTHOR AS [Project Created By]
                      –MSP_RESOURCES.PROJ_ID, MSP_RESOURCES.RES_EUID, MSP_RESOURCES.RES_UID
FROM         MSP_WEB_SECURITY_GROUP_MEMBERS INNER JOIN
                      MSP_WEB_SECURITY_GROUPS ON
                      MSP_WEB_SECURITY_GROUP_MEMBERS.WSEC_GRP_GUID = MSP_WEB_SECURITY_GROUPS.WSEC_GRP_GUID INNER JOIN
                      MSP_WEB_RESOURCES ON MSP_WEB_SECURITY_GROUP_MEMBERS.WRES_GUID = MSP_WEB_RESOURCES.WRES_GUID INNER JOIN
                      MSP_PROJECTS INNER JOIN
                      MSP_RESOURCES ON MSP_PROJECTS.PROJ_ID = MSP_RESOURCES.PROJ_ID ON
                      MSP_WEB_RESOURCES.RES_EUID = MSP_RESOURCES.RES_UID
WHERE     (MSP_RESOURCES.RES_UID > 0) AND (MSP_RESOURCES.RES_EUID IS NOT NULL) AND (MSP_PROJECTS.PROJ_TYPE = 0)
ORDER BY Project, Resource, [Role In Project]

 

Note: Please do test this query in local server dev before using it on production environments.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s