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

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.

Answer

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

idrees naik

1.4k

1 Dynamics CRM

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

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

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (1)