Then the case code I gave you should work. I've used similar code many times in 2008 R2. You may have to tweak on the syntax a little.

The IIf function is new to SQL Server 2012.

Try using a case statement:

select

Total_within_target = sum(case when Wait_Status.Value="Within Target"

then Fields!Surgical_Case_Count.Value

else 0

end),

Total_missing = sum(case when Wait_Status.Value="Missing"

then Fields!Surgical_Case_Count.Value

else 0

end),

Total_above_target = sum(case when Wait_Status.Value="above_target"

then Fields!Surgical_Case_Count.Value

else 0

end)

from ...