|
|
|
1. Local Resources used in the Project Plan:
The following query displays the list of local resources used in enterprise projects.
How to Run: Run the following SQL query by selecting Published database.
SQL Query:
SELECT DISTINCT MSP_PROJECT_RESOURCES.PROJ_UID,
MSP_PROJECTS.PROJ_NAME,
MSP_PROJECT_RESOURCES.RES_NAME
FROM MSP_PROJECT_RESOURCES INNER JOIN
MSP_PROJECTS ON MSP_PROJECT_RESOURCES.PROJ_UID = MSP_PROJECTS.PROJ_UID
WHERE MSP_PROJECT_RESOURCES.RES_IS_ENTERPRISE_RESOURCE = 0 AND
MSP_PROJECT_RESOURCES.RES_NAME IS NOT NULL
2. Resources used in the Project Plan
The following query displays the list of resource names (local and enterprise) appears in the enterprise projects by project name. This query lists all the names you see in resource sheet view of the project plan.
How to Run: Run the following SQL query by selecting Published database.
SQL Query:
SELECT DISTINCT MSP_PROJECT_RESOURCES.PROJ_UID,
MSP_PROJECTS.PROJ_NAME,
MSP_PROJECT_RESOURCES.RES_NAME,
CASE
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 2 AND
MSP_PROJECT_RESOURCES.RES_IS_ENTERPRISE_RESOURCE = 0
THEN 'Work Resource - Local'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 2 AND
MSP_PROJECT_RESOURCES.RES_IS_ENTERPRISE_RESOURCE = 1
THEN 'Work Resource - Enterprise'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 20 THEN 'Generic Work Resource'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 21 THEN 'Material Resource'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 22 THEN 'Generic Material Resource'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 25 THEN 'Cost Resource'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 26 THEN 'Generic Cost Resource'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 50 THEN 'Budget Work Resource'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 51 THEN 'Budget Cost Resource'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 52 THEN 'Budget Material Resource' ,
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 53 THEN 'Generic Budget Work Resource'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 54 THEN 'Generic Budget Cost Resource'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 55 THEN 'Generic Budget Material Resource'
END AS RES_DESC
FROM MSP_PROJECT_RESOURCES INNER JOIN
MSP_PROJECTS ON MSP_PROJECT_RESOURCES.PROJ_UID = MSP_PROJECTS.PROJ_UID
WHERE MSP_PROJECT_RESOURCES.RES_NAME IS NOT NULL
| |
|
|
