Hi Jose,
the great help is the MS doc Pjsvrdb.htm.
There is a number of examples and detailed info about database structure.
Here is a list of examples from there:
Find all projects currently stored in the Project Server database
Find all resources that have assignments currently stored in the Project Server database
Find all resources that have undeleted messages stored in the Project Server database
Find all requested status reports currently stored in the Project Server database
Find all status reports and their most recent responses
Find the names of all resources assigned to each assignment in each project
Find all timephased data for a particular resource
Find all timephased data for a particular project
Find a list of assignments and assignment data for a team member
Find all overdue assignments
Find all assignments that have been delegated to another user
Learning it, I created tool for my company. I was pretty tired with portfolio and PWA views, because they are exremely slow and I could not create analitical views we needed.
So I come up to solution with direct access to project DB and extracting needed data from there.
I created next queries for my tool:
1) current workload of the team - extracting summary workload for particular day (work, actual work and baseline work)
2) current projects - shows not completed projects and summary info for them like work, actual work, baseline work
3) current assignments - who does what now
4) comparing actuals with baselines hours - needed for some report in our company. Our bonus system is based on difference between planned work and baseline one's
Now some queries from my tool, which may help you
This query extracts work, actual work and baseline work for all not completed projects of pointed manager
SELECT
mp.PROJ_NAME as project,
ISNULL(mt.TaskWork/60000,0.0) as Work,
ISNULL(mt.TaskActualWork/60000,0.0) as Actual,
ISNULL(mt.TaskBaselineWork/60000,0.0) as Baseline,
ISNULL(TaskPercentWorkComplete,0.0) as RemainingWork,
0.0 as deltaCol
FROM MSP_VIEW_PROJ_TASKS_STD mt
INNER JOIN msp_web_projects mp
ON mt.wproj_id=mp.wproj_id
INNER JOIN MSP_WEB_RESOURCES mr
ON mr.WRES_ID = mp.WRES_ID
WHERE
mp.wres_id=112 -- id of manager
AND TaskID=0 -- extract only project summary task
AND TaskPercentWorkComplete <> 100 --extract only not completed projects
This query extracts current assignments for all resources of active projects for particular manager
SELECT
mp.PROJ_NAME as project,
CASE WHEN (GROUPING(ma.AssignmentResourceName) = 1) THEN max('ResourceSummaryTotal')
ELSE max(ma.AssignmentResourceName)
END AS resName,
CASE WHEN (GROUPING(ma.AssignmentTaskSummaryName) = 1) THEN 'TaskSummaryTotal'
ELSE ISNULL(ma.AssignmentTaskSummaryName, 'UNKNOWN Summary')
END AS taskSummary,
CASE WHEN (GROUPING(ma.AssignmentTaskName) = 1) THEN 'TaskTotal'
ELSE ISNULL(ma.AssignmentTaskName, 'UNKNOWN task')
END AS task,
CASE
WHEN (GROUPING(ma.AssignmentTaskSummaryName) = 1)
THEN max('0')
WHEN (GROUPING(ma.AssignmentTaskName) = 1 AND ma.AssignmentTaskSummaryName = '')
THEN max('00')
WHEN (GROUPING(ma.AssignmentTaskName) = 1)
THEN min(CAST(mt.TaskOutlineNumber as varchar(20))+'!')
WHEN (min(CHARINDEX ('.',CAST(mt.TaskOutlineNumber as varchar(20) ) ))) = 0
THEN min('000')
ELSE min(CAST(mt.TaskOutlineNumber as varchar(20))+'t')
END AS id,
SUM(ISNULL (ma.AssignmentWork/60000,0.0)) as Work,
SUM(ISNULL (ma.AssignmentActualWork/60000,0.0)) as Actual,
SUM(ISNULL (ma.AssignmentBaselineWork/60000,0.0)) as Baseline
FROM MSP_VIEW_PROJ_ASSN_STD ma
INNER JOIN msp_web_projects mp
ON ma.wproj_id=mp.wproj_id
INNER JOIN MSP_VIEW_PROJ_TASKS_STD mt
ON mt.wproj_id=ma.wproj_id AND mt.TaskUniqueID=ma.TaskUniqueID
WHERE
mp.WRES_ID=112 -- id of project manager
AND ma.AssignmentPercentWorkComplete<>100
AND ma.AssignmentResourceName<>'Unassigned'
GROUP BY
ma.AssignmentResourceName,mp.PROJ_NAME,ma.AssignmentTaskSummaryName,ma.AssignmentTaskName WITH ROLLUP
HAVING
SUM(ISNULL (ma.AssignmentWork/60000,0.0)) <>0
AND
SUM(ISNULL (ma.AssignmentBaselineWork/60000,0.0)) <>0
ORDER BY
ma.AssignmentResourceName,mp.PROJ_NAME,id, task
Hope it will be useful for you,
Boris Key,
Ukraine
