Group by Week in SSRS fetch Report and Sum the Hours for each week

idrees naik

I have create an SSRS report using fetch xml on time entry table, with grouping by week using DATEPART("ww", Fields!msydn_date.Value) expression. it works but it show the duration of first day in the week not the sum of duration of entire week. My data should look like this

Resource	Week1	Week2	Week3
Resource A	80	70	80
Resource b	40	60	30
Resource c	20	10	60

But my data looks like this which is the duration for first day of the week not the entire week duration

Resource	Week1	Week2	Week3
Resource A	8	7	8
Resource b	4	6	3
Resource c	2	1	6

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Tuhin Paul3y ago
To display the sum of the duration for the entire week instead of the duration for the first day of the week, you can modify your expression to use the SUM function.

Replace your current expression:
```
DATEPART("ww", Fields!msydn_date.Value)
```
with this modified expression:
```
=DATEPART("ww", Fields!msydn_date.Value) & " - " & 
FORMAT(SUM(Fields!duration.Value), "#,##0.00")
```
This modified expression concatenates the week number with the sum of the duration for the week, formatted with commas and two decimal places. This should give you the desired output.
0