Data Analysis - View

J

Jason

Hello,
I have been asked to create a view or report that would show Personnel over
and under 20% allocated.

Any suggestions on how I would accomplish this?
Thanks
 
C

Chak

Hello,
I have been asked to create a view or report that would show Personnel over
and under 20% allocated.

Any suggestions on how I would accomplish this?
Thanks
Click to expand...

Hi Jason,

I am not sure which version that you are using. If it is Project
Server 2003, please see the following SQL queries to get the resource
allocation. I am also not sure what's your criteria to determine the
over allocation. I mean over allocation per resource in single project
or resource allocation across the projects. In either case, I hope the
following queries will help you in genetating the data. Please extend
the following queries with your criteria to calculate the over/under
allocation.

****************************************************************************************
Resource Allocation By Project with task name per Day:

SELECT MSP_WEB_PROJECTS.PROJ_ID, MSP_WEB_PROJECTS.WPROJ_ID AS
WPROJ_ID, MSP_WEB_PROJECTS.PROJ_NAME,
MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart
AS ASSN_DAY, MSP_TASKS.TASK_NAME,
MSP_RESOURCES.RES_NAME AS RES_NAME,

MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeRegularWork / 60000 AS
REG_WRK
FROM MSP_VIEW_PROJ_ASSN_TP_BY_DAY INNER JOIN
MSP_ASSIGNMENTS ON
MSP_VIEW_PROJ_ASSN_TP_BY_DAY.ProjectUniqueID = MSP_ASSIGNMENTS.PROJ_ID
AND
MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentUniqueID
= MSP_ASSIGNMENTS.ASSN_UID INNER JOIN
MSP_RESOURCES ON MSP_ASSIGNMENTS.PROJ_ID =
MSP_RESOURCES.PROJ_ID AND
MSP_ASSIGNMENTS.RES_UID = MSP_RESOURCES.RES_UID
INNER JOIN
MSP_TASKS ON MSP_ASSIGNMENTS.PROJ_ID =
MSP_TASKS.PROJ_ID AND MSP_ASSIGNMENTS.TASK_UID = MSP_TASKS.TASK_UID
INNER JOIN
MSP_WEB_PROJECTS ON
MSP_VIEW_PROJ_ASSN_TP_BY_DAY.WPROJ_ID = MSP_WEB_PROJECTS.WPROJ_ID
WHERE (MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeRegularWork IS
NOT NULL) AND (MSP_RESOURCES.RES_NAME IS NOT NULL)

***************************************************************************************

Resource Allocation By Project per Day:


SELECT MSP_WEB_PROJECTS.PROJ_ID, MSP_WEB_PROJECTS.WPROJ_ID AS
WPROJ_ID, MSP_WEB_PROJECTS.PROJ_NAME,
MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart
AS ASSN_DAY,
MSP_RESOURCES.RES_NAME AS RES_NAME,

SUM(MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeRegularWork / 60000) AS
REG_WRK
FROM MSP_VIEW_PROJ_ASSN_TP_BY_DAY INNER JOIN
MSP_ASSIGNMENTS ON
MSP_VIEW_PROJ_ASSN_TP_BY_DAY.ProjectUniqueID = MSP_ASSIGNMENTS.PROJ_ID
AND
MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentUniqueID
= MSP_ASSIGNMENTS.ASSN_UID INNER JOIN
MSP_RESOURCES ON MSP_ASSIGNMENTS.PROJ_ID =
MSP_RESOURCES.PROJ_ID AND
MSP_ASSIGNMENTS.RES_UID = MSP_RESOURCES.RES_UID
INNER JOIN
MSP_TASKS ON MSP_ASSIGNMENTS.PROJ_ID =
MSP_TASKS.PROJ_ID AND MSP_ASSIGNMENTS.TASK_UID = MSP_TASKS.TASK_UID
INNER JOIN
MSP_WEB_PROJECTS ON
MSP_VIEW_PROJ_ASSN_TP_BY_DAY.WPROJ_ID = MSP_WEB_PROJECTS.WPROJ_ID
WHERE (MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeRegularWork IS
NOT NULL) AND (MSP_RESOURCES.RES_NAME IS NOT NULL)
GROUP BY MSP_WEB_PROJECTS.PROJ_ID, MSP_WEB_PROJECTS.WPROJ_ID,
MSP_WEB_PROJECTS.PROJ_NAME,

MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart,
MSP_RESOURCES.RES_NAME
*************************************************************************************************************************

Thanks
Chak
 
J

Jason

Hi Chak,
Sorry I should have been more specific. Currently running Project Server
2007 and the critieria is to identify any resource across all projects. I
orginally tired to develop a custom formula but quickly descovered that it
was not going to take care of this; which is why we thought the next step was
working within Data Analysis. Thanks for your time and help!
 
B

Ben Howard

Hi Jason,
Check my blog below for the "fun with analysis services posting". This
will get you started.
 
Top