# 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

• 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

### 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