|
|
| |
|
Local Resources in Project Plans
The following query displays the list of the local resource names by project. Also it displays “work, actual work and most recent assignment finish date” for each local resource.
Target Audience: Project Server Administrator/ Report Developer
How to Run: Run the following query on SQL Query Analyzer, by selecting Project Server database.
Script:
Create table #Local_Resources (res_name varchar(255))
insert into #Local_Resources
select r.res_name
from dbo.msp_resources r
where r.proj_id = 1
-- Select info on invalid resources
select r.proj_id As Proj_ID,
p.proj_name as Proj_Name,
r.res_name As Res_Name,
max(a.assn_finish_date) as Work_Till,
SUM(a.assn_work / 60000) AS Work,
SUM(a.assn_act_work / 60000) AS Actual_Work
from dbo.MSP_RESOURCES r
inner join dbo.MSP_PROJECTS p on
r.proj_id = p.proj_id
left outer join #Local_Resources vr on
r.res_name = vr.res_name
left outer join dbo.MSP_ASSIGNMENTS a on
r.proj_id = a.proj_id AND
r.res_uid = a.res_uid
where (r.res_uid >0) and
(vr.res_name is null)
group by p.proj_name,
r.proj_id,
r.res_name,
r.res_uid,
p.PROJ_MACHINE_ID
order by p.proj_name, r.res_name
-- Drop Temp Tables
drop table #Local_Resources
Sample Output:
|
| |
|
