none
Summary and Subtasks with Traffic light info

    Question

  • 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.

    Thanks!

    Friday, July 12, 2013 10:36 AM

Answers

All replies

  • I think you want to display an indicator on your report for the traffic lights. This might be helpful to you: http://technet.microsoft.com/en-us/library/dd283116.aspx#Indicator

    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'

    ELSE 'WHITE'

    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