Formula based task fields in PS2013 RRS feed

  • Question

  • Hi All

    In PS2013 I've created a task level custom formula field to calculate RAG status based on task progress.

    The formula also includes conditions like:

    • Now()-[Finish]>7 and [%Complete]<100, "Red"
    • Now()-[Start]>7 and [%Complete]=0, "Red"

    The formula works fine and brings up the reqired RAG in both PWA and Project Pro.

    The issue is, this fields (that calculates RAG) feeds into daily reports thats presentated to Sr.Mgmt. If a PM does not check-out and publish his plan the above two conditions show stale RAG values.

    Is there a way using which

    1. The formula can be calculated in the Reporting DB without the PM's having to open/publish the plan each day?
    2. The above conditions can be revamped so that the calculation can work like any other condition?

    Any help is highly appreciated.



    Thursday, January 30, 2014 4:47 PM

All replies

  • Meeta,

       I would say the simplest solution in your case would be to alter the report so that it makes the calculation in the report. 

    Remember it is not supported to query the draft database directly so if the %complete and other fields needs to be updated daily in the reporting database then consider adding a powershell script to your server as a daily scheduled task to publish the target project on a timer like this:

    $pwaURL = "<pwa url>"
    $projectUid = "<target project uid>"
    $svcPSProxy = New-WebServiceProxy -uri "$pwaURL/_vti_bin/PSI/Project.asmx?wsdl" -useDefaultCredential
    $G = [System.Guid]::NewGuid()
    $svcPSProxy.QueuePublish("$G", $projectUid, "true",$null)

    Just remember that calculated fields are not recalculated using queuepublish from the PSI - so your report must still do the calculation.

    Hope that helps,


    James Boman - Software Consultant for IPMO -

    Thursday, January 30, 2014 10:46 PM
  • Meeta,

    The reporting tables would have the same issue of stale data if the PMs haven't published. I know Paul Mather has a Powershell script to publish all projects. Do a search for him and you'll find a treasure trove of PS functions. It might be useful here as long as PMs have projects checked in at the end of each day and aren't concerned about republishes.

    Are you doing daily time tracking? If not, is your data really changing that much daily for this to be a significant issue?

    Treb Gatte, Project MVP, Managing Partner, Tumble Road LLC | @tgatte |

    Friday, January 31, 2014 9:03 AM
  • Thank you James and Trebb for your response.

    I think the best bet in my case would be to run a script/code and publish projects at the end of the day.

    My only concern was the huge volumne of projects we have might hamper system performance when the script runs.

    Thanks once again.

    Friday, January 31, 2014 2:12 PM