I just wanted to post this as follow up to an interesting thread that started a while back on one of the Project Server newsgroups. The thread, originally started by Rambunctious was asking how to get the name of the parent task for each child task out of the Project Server database for use in an Excel Report.
Barbara Henhapl provided an excellent response, which I am reposting for posterity with her gracious permission. (Thanks also to Rambunctious for posting the original question.)
1) Modify the ODC file used by the Excel report to generate the appropriate data set. For more information on modifying the ODC file, please refer to this post.
2) Specifically, the change you would make is to pull the TaskParentUID field for each task in the MSP_EPMTask_UserView table, and then tie it back to the TaskName field using the following syntax:
SELECT SummaryTask.TaskName AS SummaryTaskName, Tasklist.TaskName
FROM dbo.MSP_EpmTask_UserView AS Tasklist INNER JOIN
dbo.MSP_EpmTask_UserView AS SummaryTask ON Tasklist.TaskParentUID = SummaryTask.TaskUID
3) To make that easy, Barbara went ahead and pulled the ODC file from the Projects and Tasks demo report in the BI Center templates folder, and modified that to include the key fields. The result should look something like this text file.
What does that get you?
…which I bet would work with the technique I described in this post to make a dynamic WBS based view of the project for reporting purposes. (Not that you couldn’t do so with Visual Reports, but I wonder if there might be an advantage to doing it this way.)
Thanks again to Barbara and Rambunctious for prompting the thoughts…..any mistakes in this posting are definitely mine.