I've spent over a week on this problem and I hope someone can help. In a nutshell - I need to get the child project task name (task name summary) value from the EPM DB and join it to my standard projects query.
I have an SSRS report which replicates the OOTB EPM Resource by Project report. This is working as expected.
My client also uses MS Project to define a Master Proejct which has Tasks representing child projects.
i.e. We have normal project in EPM, Project 1, 2, 3 and 4. And we have Project 5 which is a master project and has child projects (represented by Tasks).
How on earth do I get the Task Name Summary field from the reporting database? I can't see it anywhere.
Running a query on the Tasks table returns the vales I want to see, however as soon as I join it to my main query the task name summary fields do not display, I only see Resource type values i.e. DBA, Tester etc. when I should see the child project values.
Here is the query which returns all my tasks:
SELECT dbo.MSP_EpmProject.ProjectName AS MProject, dbo.MSP_EpmTask.TaskName AS ChildProject, MSP_EpmTask.ProjectUID AS ChildProjectUID
FROM dbo.MSP_EpmProject INNER JOIN
dbo.MSP_EpmTask ON dbo.MSP_EpmProject.ProjectUID = dbo.MSP_EpmTask.ProjectUID
(dbo.MSP_EpmTask.TaskIsProjectSummary = 0)