none
Querying tasks with work planned in the past RRS feed

  • Question

  • We are using Project Server 2010.

    We have a custom filter in Project Pro that we use to highlight tasks on a schedule that have incomplete work planned prior to a date.  We use this to find tasks that need to be updated as completed or rescheduled.  This works great for a single project.  We've used it for years.  Here is the filter logic.

    (Summary = No

    And % Complete <100%

    And Active = Yes

    And Resume < date entered )

    OR

    (Summary = No

    And Active = Yes

    And Start < date entered

    And Actual Start = NA )

    I need to build a report to provide these tasks across all projects in the Project Server database.  I'm struggling to find the fields to do this.  Particularly since Resume is not in the database as far as I can figure out. I've looked through the SDK.  I'm coming up dry on ideas.  Anyone have thoughts on a solution to this?

    Thanks,


    Walter


    Walter

    Friday, June 15, 2012 6:26 PM

Answers

  • Walter,

    It depends on how accurate you want to be. Working at the Task level is going to give you a hint but the truth is at the Assignment by Day level of detail. You should take a look at the AssignmentByDay view in the RDB. This may provide some ideas as to how you would approach this problem.

    BTW, I've already created a report that shows the number of hours deliquent by Task by Project, which is part of the msProjectExperts Report Pack. In that report, if a task has a start date less than the Project Status date and has uncompleted work scheduled before the Project Status date, the work is considered deliquent. We did this to ensure PMs were following best practice in moving incomplete work forward on a weekly basis.

    One final caution. Working with any of the By Day tables typically benefits from date windowing techniques to cut down the amount of data processed. Do you really need all of the data if you are only using a month of it? Also, as I presented at ProjConf, you want to aggregate your data, then join the aggregated dataset to other tables/views. Otherwise, the joins at the By Day table level will kill your query performance.

    Hope this helps.

    --Treb

    Monday, June 18, 2012 7:11 AM
    Moderator

All replies

  • I usually go with a comparison of the Stop field and the Status Date.  That works fine in a custom field.  If the Stop field is not available within the database, perhaps create a task level custom field that triggers off of it - then query on the custom field?


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

    Monday, June 18, 2012 2:32 AM
    Moderator
  • Walter,

    It depends on how accurate you want to be. Working at the Task level is going to give you a hint but the truth is at the Assignment by Day level of detail. You should take a look at the AssignmentByDay view in the RDB. This may provide some ideas as to how you would approach this problem.

    BTW, I've already created a report that shows the number of hours deliquent by Task by Project, which is part of the msProjectExperts Report Pack. In that report, if a task has a start date less than the Project Status date and has uncompleted work scheduled before the Project Status date, the work is considered deliquent. We did this to ensure PMs were following best practice in moving incomplete work forward on a weekly basis.

    One final caution. Working with any of the By Day tables typically benefits from date windowing techniques to cut down the amount of data processed. Do you really need all of the data if you are only using a month of it? Also, as I presented at ProjConf, you want to aggregate your data, then join the aggregated dataset to other tables/views. Otherwise, the joins at the By Day table level will kill your query performance.

    Hope this helps.

    --Treb

    Monday, June 18, 2012 7:11 AM
    Moderator