none
MSProject 2010 Reports - Automation RRS feed

  • Question

  • Good afternoon techie colleagues

    I have created a number of schedules that now display traffic light indicators for various statuses of tasks - either a 5, 4, 3, 2 or 1 (determined by a (pilfered from elsewhere) formula within a custom number field). At present, on a weekly basis I have to manually count the number of each in order to report back the number of schemes complete, at risk, late on target or not due.

    It would be very helpful to be able to automate this task such that totals of each are calculated automatically to avoid having to manually count them. I've tried amending a few of the built in visual reports but, to be honest, I'm a total stranger to pivot tables (I thought a pivot table was the fold-down dinner table one my gran used to have in her kitchen....) so I'm getting nowhere fast.

    I could just copy and paste the columns into Excel and total them there - I can manage that - but it's almost as fast to count them manually....

    Any suggestions as to how I can achieve what's needed with a report template please?

    Many thanks in advance

    Kind regards

    Peter

    Tuesday, November 15, 2016 2:46 PM

All replies

  • Peter,

    I can think of a couple of ways to do this. One is to use 5 more custom Number fields to sum up each indicator

    Number2 = IIF([Number1]=5,1,0)

    Number3 = IIF([Number1]=4,1,0)

    and so forth

    Then use the rollup sum function for each number field's "calculation for task and group summary rows".

    Another method would be to use VBA to basically do the same thing but it wouldn't require "burning" 5 additional custom Number fields. For example each indicator value's sum could be calculated by the code and then written into the Project Summary Task Notes field. Or, if you really wanted to get fancy, the data could be exported to Excel with a report automatically created. No need for a drop-leaf table :-)

    You could try the first approach yourself. If you like the second approach, I can help you with that.

    John


    • Edited by John - Project Tuesday, November 15, 2016 6:33 PM Excel option
    Tuesday, November 15, 2016 5:02 PM
  • There is often more than one way to skin a cat. I prefer the easiest way.

    Use a spare number field with a formula, where the formula is just the number "1", and roll up to the sum for summary and group rows. Then make a custom group which groups on the traffic light field.

    This has the added advantage of getting the job done without having to do anything with the visual reports or the pivot tables. John's suggestion is good, as always, but it uses too many number fields.

    Wednesday, November 16, 2016 12:23 AM