Reporting Services and Project Server 2007 Project Tasks

[jleifb]

Hello,

I am trying to build a report that would give me all tasks under a summary
task for each project. I can tell which tasks are a summary task, but I
cannot tell which tasks go with which summary task.

Thanks,
Josh

 

[James Fraser]

I am trying to build a report that would give me all tasks under a summary
task for each project. I can tell which tasks are a summary task, but I
cannot tell which tasks go with which summary task.

I wrote up the response below, then realized I wasn't sure if you are
using Project Server. Are you writing this report against a database,
Project Server, or Project client?

My response for Project Server:
Generally, it is easier to deal with this in the application layer,
since SQL doesn't do so well with recursive joins.

This should show all the data you need to complete the job from the
Reporting database:
----
select
t.TaskOutlineNumber,
t.TaskName,
t.TaskOutlineLevel
from
MSP_EPMTask t
where t.ProjectUID = 'ProjectGUIDgoesHERE'
order by t.TaskIndex
----

If you just want things joined to their parent, how about:

-----
select
t.TaskOutlineNumber,
t.TaskName,
t.TaskOutlineLevel,
parent.TaskName as ParentName,
parent.TaskOutlineLevel
from
MSP_EPMTask t
left outer join
MSP_EPMTask parent
on t.TaskParentUID = parent.TaskUID
where t.ProjectUID = (Select top 1 ProjectUID from MSP_EPMProject)
order by t.TaskIndex
-----

Note that Task 0 always lists itself as a parent (causing even more
recursion problems...)


Hopt this helps...
James Fraser

 

[James Fraser]

I wrote up the response below, then realized I wasn't sure if you are
using Project Server. Are you writing this report against a database,
Project Server, or Project client?

Or I could have put my brain in gear and read the subject line...
Doh...

jf

 

[jleifb]

Hello James,

Thank you so much! Works like a charm!!

Thanks,
Josh