none
Time Phased Data RRS feed

  • Question

  • Using 2007 - I need to create an array of tasks by month.  That is, I need to count the number of tasks that occur in any given month.  If any part of a task starts/finishes in a month period, I need to count it and export it to Excel.   I will be scanning the file vertically and then capturing the appropriate data for each task horizontally.  My boss wants to graph the data as a profile of effort - somewhat like a staffing chart.

    example:

    JAN   FEB   MAR   APR ...

    20     30    75    35

    I looked at the time phased data routines in Rod Gill's book but I can't get them to work (error messages) and I am not sure it that's what I need.  I tried creating an array but I can't get the elements to accept the data.

    What is the best approach here?

    Regards,

    GEM1941

     

    Friday, May 20, 2011 7:23 PM

All replies

  • GEM1941,

    I don't know that there is any one best approach, several methods will get you the data you want. I personally would use the following pseudo-code.

    1. Create a filter using the FilterEdit Method to pare down the file to just those tasks with starts and finishes in the month you want

    2. On that filtered data set use a SelectAll and then the Count property of ActiveSelection.Tasks to give you the number of tasks that occur in that month.

    3. It is unclear if you really need to capture timescaled data for the tasks of interest. If you don't then you can simply cycle through the ActiveSelection.Tasks and either capture the desired data in arrays for export or write the data into Excel on the fly. If you do need timescaled data, then you will need to use the task, resource or assignment syntax for the TimescaledData Method.

    Having Rod's book is an excellent starting point. If you can give us more details on which module code you tried and what error message you received, perhaps we can help you get it working. As far as not accepting data you attempted to put into an array, it is probably due to the data type being wrong (e.g trying to write floating point data into an integer array, etc.)

    John

    Friday, May 20, 2011 8:57 PM
  • If you need to write code for the solution, John has a good approach.  From there you can go into Excel and make what ever graph you like using arrays and the like.

    If all you need are some charts with that type of data, you will probably be better served by purchasing a 3rd party product.  A tool called Decision Edge  (www.decisionedge.com), or more specifically http://decisionedge.com/products/decisionedge-microsoft-office-project

    This tool is an add-in for MS Project (2003 and later) that will make more graphs than known to most of us.  The graph you speak of (Start/Finishes) is a canned report from the tool.  The tool allows you to save as a graphics object (such as jpg, bmp, etc) and can be formatted to your liking.

     

    The way I see it is like this -- It is going to cost your company thousands of dollars for you to code and debug using VBA or Visual Studio to make your graph ... just as soon as it is complete, they will request something else.

     

    The product I mentioned is a couple hundred bucks ...  Cheaper in the long run.

     


    If you feel this post answered the question, please vote for it. I am also available here:
    msprojectblog.com
    Monday, May 23, 2011 1:46 PM
    Moderator
  • Thanks to all - I like the 3d party app idea - saves me a lot of time and effort - although I miss out on all the agony/eccstasy of doing it myself ;-)

    GEM1941

     

    Monday, May 23, 2011 4:10 PM
  • GEM1941,

    You're welcome. I personally like the total flexibility and challenge of writing macros but like Jim said, in the long run it might make more sense to utilize a 3rd party option if it exists.

    John

    Monday, May 23, 2011 7:49 PM