BI report (SQL) - How to get the predecessors of each task/milestone RRS feed

  • Question

  • Hi,

    I am working with Project Server 2013, creating some reports in the BI center.

    I know that to get the predecessor UID i need to Join some tables with the Published or Draft DBs to get the links from MSP_LINKS table.

    But i am not sure what joins and field selections i should do to get the following:

    1. Predecessor names for each task (i want to put the task names on the rows and have a column next to it with the predecessors list of each task, can be grouped so that i will have multiple rows for each task if it has a few predecessors).
    2. Know if the predecessor is on the critical path.
    3. More advance option, know if each predecessor is on the critical task path (means that if i have 4 predecessors to a task but only 2 will make the task dates move if they change, then it would be nice to know which ones).
    4. General information for each predecessors (Start, Duration, Finish).

    Generally i would like to have a table like this if possible via SQL.  i would very appreciate any help on it and to know if it is doable. 

    Project Name Task Name TaskStartDate TaskFinishDate TaskDuration Predecessors Name Predecessor Start Date Predecessor Finish Date Predecessor Duration Predecessor IsCritical Predecessor Is on Critical path of the successor task (Nice to have)
    AAA A1       P1          
    A1       P2          
    A1       P3          
    A2       P4          
    A2       P5          
    A2       P6          
    A2       P7          
    A3       P8          

    Ofir Marco , MCTS P.Z. Projects

    Tuesday, April 5, 2016 5:43 AM