Then you have multiple options:
Preaggregate using a derived table or CTE:

SELECT W.ID,
   W.Group,
   SUM( WD.Payment) AS TotalPayment,
   ...

Don't aggregate the values on your Widget table, just group by them.

SELECT W.ID,
   W.Group,
   SUM( WD.Payment) AS Payment,
   ...

I find that it's easier to learn about Tally Tables in this article from Dwain Camps:
Tally Tables in T-SQL « dwaincsql

If you want to have any chance of being helped. I suggest that you start with the best practices to post in forums.
How to Post Performance Problems...

One simply does not introduces Dynamic SQL
without a section on how to prevent SQL Injection.

The basic changes are simple enough to include them in...

Have you tried something like this?

(left(Fields!SLAHours.Value,Len(Fields!SLAHours.Value)-6)*3600)
+(mid(Fields!SLAHours.Value,Len(Fields!SLAHours.Value)-4,2)*60)
+(right(Fields!SLAHours.Value,2))


This should be an improvement.

select DEP_Code, DEP_Name, cast(0 as bit) Favourite,
  case
  when DEP_Code = 'ACHR' then 7
  when DEP_Code = 'IT' then...

Maybe this is a simpler solution:

SELECT *
FROM dbo.Candidate AS c
WHERE c.candidate_id NOT IN(
        SELECT c.candidate_id
        FROM dbo.Candidate AS c
        CROSS JOIN dbo.Skill AS...

Your solutions seem very clever, but I wouldn't test my luck like that when having a varchar(max) column.