none
Project Server 2013 - Reporting on Workflow Stages and Phases RRS feed

  • Question

  • I just received a requirement to build a report listing all the projects and their corresponding Workflow Stages/Phases in a chart.  I did a chart using cube with the Project NonTimephased Data, but how will I do this with workflow stages and phases.
    Wednesday, January 7, 2015 8:27 PM

Answers

  • I don't think that data is available from the cube.  You'll need to pull that from a SQL query with the following tables:

    Projects

    Workflow Status Information

    Workflow Stages

    Workflow Phases


    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky

    Thursday, January 8, 2015 2:47 AM
    Moderator

All replies

  • I don't think that data is available from the cube.  You'll need to pull that from a SQL query with the following tables:

    Projects

    Workflow Status Information

    Workflow Stages

    Workflow Phases


    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky

    Thursday, January 8, 2015 2:47 AM
    Moderator
  • Hi,

    Further to Andrew's reply, just thought of sharing a sql query to fetch this data as i can see "NewB" in your Avatar :)

    and since this data is also not stored in a straight forward manner.

    hope this helps.

    SELECT P.ProjectUID
    , P.ProjectName
    , WP.PhaseName
    , WS.StageName
    , WSI.StageOrder
    , EPT.EnterpriseProjectTypeName
    , WSI.StageEntryDate
    , WSI.StageStatus
    --, CASE WHEN WSI.StageStatus = 0 THEN 'Not Started'
    --WHEN WSI.StageStatus = 1 THEN 'Waiting for Input'
    --WHEN WSI.StageStatus = 2 THEN 'Waiting for Approval'
    --WHEN WSI.StageStatus = 3 THEN 'Workflow Processing'
    --WHEN WSI.StageStatus = 4 THEN 'Stage Completed'
    --WHEN WSI.StageStatus = 5 THEN 'Completed with Errors'
    --WHEN WSI.StageStatus = 6 THEN 'Workflow Completed' END AS [Stage Status Description]    
    FROM  MSP_EpmWorkflowStage  AS WS             
    INNER JOIN MSP_EpmWorkflowPhase AS WP ON  WS.PhaseUID =WP.PhaseUID           
    INNER JOIN MSP_EpmWorkflowStatusInformation AS WSI ON WS.StageUID =WSI.StageUID   
    INNER JOIN dbo.MSP_EpmProject AS P ON WSI.ProjectUID =P.ProjectUID               
    INNER JOIN dbo.MSP_EpmEnterpriseProjectType AS EPT ON  P.EnterpriseProjectTypeUID = EPT.EnterpriseProjectTypeUID
    WHERE WSI.StageEntryDate IS NOT NULL AND (WSI.StageStatus != 0 AND WSI.StageStatus != 4)            
    ORDER BY P.ProjectName
    


    Khurram Jamshed - MBA, PMP, MCTS, MCITP ( Blog, Twitter, Linkedin )
    If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”.

    Thursday, January 8, 2015 3:09 AM