Project Server 2010: How does one identify a summary task as a subproject using the Reporting Database? RRS feed

  • Question

  • I am using Report Builder 3.0 to create a report that lists all the tasks in a Project file. This file has 10 tasks, One of these tasks is the project summary file (with Task Index of 0) and among the other tasks are tasks that represent subprojects (which are typed as summary tasks) AND tasks that represent summary tasks that are not subprojects.

    I need a way to differentiate summary tasks and subproject tasks using the Reporting Database. Is there a view field that applies that I have not found? (Similar to "Task_Is_Subproj" from the Published or Draft Database.) If no field, is there another way to do it? 

    Thanks in advance.

    Thursday, October 17, 2013 9:50 PM

All replies

  • There is a field in the MSP_EpmTask_UserView called TaskIsProjectSummary. I wonder if it shows true for subprojects. I've honestly never looked but this might be what you are seeking.

    The only other idea I have is to do an inline lookup on the MSP_EpmProject_UserView table to see if the ParentProjectUID is populated for the associated project. Performance would not be great if you had to do this.

    Hope this helps.

    Treb Gatte, Project MVP | @tgatte |

    Monday, October 21, 2013 1:53 AM
  • Hi GHancock1,

    You will find this information in numeric format in Draft or publish database Table - msp_projects and Column - Proj_Type.

    following are the details of each numeric value related to project:

    0 = Regular project (default)
    1 = Template
    2 = EGlobal
    4 = Proposal
    5 = InsertedProject 
    6 = MasterProject 

    you can use this table in conjunction with your current query to differentiate between summary task and sub project.

    hope this helps.

    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”.

    Monday, October 21, 2013 7:07 AM
  • Mr. Gatte

    The field TaskIsProjectSummary applies only to a task that represents the file it is actually inside. It is what I was refering to when I was talking about the task index of 0. (Shows False for Subproject task files)

    As for the ParentProjectUID, they always refer to the project the task is inside. It was a great suggestion, but unfortunately, I have already tried that as well.

    Thank you for the ideas!

    • Edited by GHancock1 Monday, October 21, 2013 1:49 PM clarification
    Monday, October 21, 2013 1:20 PM
  • Mr. Jamshed,

    My previous query uses the Reporting database, and there are two problems with using the Published or Draft databases.  The first is that all the documentation says that I should not. 

    Second, I do not know how to create a table that calculates from two datasets from two different databases at the same time. This is the reason I did not use the field TASK_Is_SubProj that tells me exactly what I want but it is from the Published Database. All the rest of my fields (about 100) are from the Reporting database.

    Specifically, what I am trying to do for this particular table cell is to do 3 things.

    1. Check and see if the task is a subproject, if it is assign the interger 2

    2. If it is not a subproject - Check and see if the task starts with the same characters as an enterprise custom field.

    3. If it does start with the enterprise custom field's string then it gets assigned the interger 1, otherwise a 0. (Code Snippet for 2&3 below)

    I am sorry this wasn't clear previously. Thank you for trying to help and I appreciate any further responses.

    Currently what I have is this:

    Code Snippet:


    • Edited by GHancock1 Monday, October 21, 2013 1:47 PM Added Code Snippet
    Monday, October 21, 2013 1:40 PM