none
SQL Reporting from Master Projects RRS feed

  • Question

  • Is there a way to extract data from  Master Projects (Project with sub-projects within them) using SQL? Is there a way to walk the tree (from Master) to all the sub-projects and get the details.

    We have developed various SQL Reports for variance and task performane analysis (late tasks etc) but the data from the Master projects is not available in the SQL database and so it doen not appear in the reports




    Tuesday, August 16, 2011 5:59 PM

Answers

  • the query below should get you going. It will output the name of all the tasks within subprojects along with the name of the subproject and the name of the master.

    SELECT   ProjectUserView_Master.ProjectName AS MasterProjectName, ProjectUserView_Sub.ProjectName AS SubProjectName, 
               TaskUserView_Sub.TaskName AS SubProjTaskName
    FROM     MSP_EpmProject_UserView AS ProjectUserView_Master INNER JOIN
               MSP_EpmTask_UserView AS TaskUserView_Master ON ProjectUserView_Master.ProjectUID = TaskUserView_Master.ProjectUID INNER JOIN
               MSP_EpmProject_UserView AS ProjectUserView_Sub ON TaskUserView_Master.TaskName = ProjectUserView_Sub.ProjectName INNER JOIN
               MSP_EpmTask_UserView AS TaskUserView_Sub ON ProjectUserView_Sub.ProjectUID = TaskUserView_Sub.ProjectUID
    WHERE   (ProjectUserView_Master.ProjectType = 6)
    

    Basically it finds all Master Projects and then uses the task names within that master to join over to the Projects UserView again to get the GUID for that project and then uses that GUID to join over to the TaskUserview to get the task names for the subproject.

    Again, not EXACTLY what you need Im sure but enough of a framework to get you going.


    Brian Kennemer – DeltaBahn Senior Architect
    Blog | Twitter | LinkedIn
    Thursday, August 18, 2011 12:51 AM
    Moderator

All replies

  • Godzilla,

    I don't use SQL but if I'm not mistaken it requires a database file for its query. Project eliminated the option to save project files in database format with Project 2007. However, you can get all the data you need by using Project VBA.

    John

    Tuesday, August 16, 2011 7:01 PM
  • Or are you using Project Server? If so teh reporting database has what you want.

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    Wednesday, August 17, 2011 8:14 AM
    Moderator
  •  I am able to get the assignments, tasks etc for sub projects but those don't exist for the master project in the reporting database. The master project exists just in the Projects table and the views but the underlying details are not there.

    Wednesday, August 17, 2011 4:42 PM
  • Godzilla,

    You have to understand that in a master/subproject structure, the subproject data does not belong to the master. The master only contains pointers to the individual subprojects.

    I don't use Project Server so I don't know the structure of the reporting database but I'm guessing you can access the subproject data just as Rod mentioned, you will just have to use the correct reference to subproject data. In the VBA world the following syntax is used to access subproject data from a master file. Note: in this example, the "query" will give the start date of the first task in the first subproject inserted into the master.

    SD = ActiveProject.Subprojects(1).SourceProject.Tasks(1).Start

    I realize you are using SQL and not VBA but perhaps this example will help guide you in the right direction. If not, I assume Rod will check in later to fill in details more specific to your needs. If for some reason he does not, you might consider posting to the Project Customization and Programming forum at, http://social.technet.microsoft.com/Forums/en-US/project2010custprog/threads.

    Hope this helps.

    John

    Wednesday, August 17, 2011 7:41 PM
  • the query below should get you going. It will output the name of all the tasks within subprojects along with the name of the subproject and the name of the master.

    SELECT   ProjectUserView_Master.ProjectName AS MasterProjectName, ProjectUserView_Sub.ProjectName AS SubProjectName, 
               TaskUserView_Sub.TaskName AS SubProjTaskName
    FROM     MSP_EpmProject_UserView AS ProjectUserView_Master INNER JOIN
               MSP_EpmTask_UserView AS TaskUserView_Master ON ProjectUserView_Master.ProjectUID = TaskUserView_Master.ProjectUID INNER JOIN
               MSP_EpmProject_UserView AS ProjectUserView_Sub ON TaskUserView_Master.TaskName = ProjectUserView_Sub.ProjectName INNER JOIN
               MSP_EpmTask_UserView AS TaskUserView_Sub ON ProjectUserView_Sub.ProjectUID = TaskUserView_Sub.ProjectUID
    WHERE   (ProjectUserView_Master.ProjectType = 6)
    

    Basically it finds all Master Projects and then uses the task names within that master to join over to the Projects UserView again to get the GUID for that project and then uses that GUID to join over to the TaskUserview to get the task names for the subproject.

    Again, not EXACTLY what you need Im sure but enough of a framework to get you going.


    Brian Kennemer – DeltaBahn Senior Architect
    Blog | Twitter | LinkedIn
    Thursday, August 18, 2011 12:51 AM
    Moderator