Summary and Subtasks with Traffic light info


  • Hi all,

    Need some suggestions again.

    I have  a table with columns that are sub tasks, another column with summary task the sub task belongs to and another column that has subtask statuses (Red, Amber, Green). 

    I need to calculate Summary Task status based on the RAGs/traffic light of subtasks. If a sub task is red then summary is red, if it is not red, but Amber then Amber and if it is not red and Amber, but green then green.

    I am not sure how to calculate in sql query and then show it in status report. Any suggestions will be appreciated.


    Friday, July 12, 2013 10:36 AM


All replies

  • I think you want to display an indicator on your report for the traffic lights. This might be helpful to you:

    To calculate the status of the task, I would assign values to the statuses of the subtasks (red = -1, amber = 0, green = 1).  Then get the min subtask status number. 

    CASE WHEN MIN(SubtaskStatusNum) = -1 then 'RED'

    WHEN MIN(SubtaskStatusNum) = 0 then 'AMBER'

    WHEN MIN(SubtaskStatusNum) = 1 then 'GREEN'


    Saturday, July 13, 2013 7:53 PM
  • Thanks. I managed to do this by counting RAGs and grouping them at summary level then did a check against reds, amber and greens for each summary level and I got my result I wanted.

    Thanks for your help though.

    Thursday, July 18, 2013 1:02 AM