excel servies sql query to pull summary task with all associated subtasks for specific projects RRS feed

  • Question

  • Does anyone know of a way to pull a summary task with task name of "Test Phase" and all its associated subtasks for any project that has the summary task section labeled as such?  I can pull for one project in SSRS but I need to be able to (due to a project template we use that has "Test Phase" as a summary section) look for every project that has this summary section in it and pull all of the associated subtasks into an excel pivot table.  The intent is to be able to look across our portfolio and find all Testing Phases for the portfolio in order to do some scheduling analysis.  I have tried a number of different queries but cannot seem to get it to work.  ??


    Tuesday, May 21, 2013 3:54 AM

All replies

  • Hi

    I am quite sure that there is a more elegant way by using taskuid and taskparentuid, but that would have to be recursive, as you will want also the tasks in lower outline levels.

    The easy way that should word according to my tests is something like:

    select p.projectname,t1.taskname as Parenttask,t2.taskoutlinenumber, t2.taskname from msp_epmtask t1 inner join msp_epmtask t2 on t1.projectuid=t2.projectuid and t2.taskoutlinenumber like t1.taskoutlinenumber+'.%'
    inner join msp_epmproject p on p.projectuid=t1.projectuid
    where t1.taskname='Test Phase

    I hope that helps.

    Kind regards


    Christoph Muelder | Senior Consultant, MCSE, MCT | SOLVIN information management GmbH, Germany

    Tuesday, May 21, 2013 7:23 PM