0

I'm a little stuck here. I have a date field that shows the appointment start dateTime for an officer. eg. 13/04/2017 1:44:00 PM.

I am writing a report and would like to first group it by officer, then into the week, and then into days of the week. The grouping by officer is no problem, however, I am unsure how to convert the date field so that it shows the appointments in groups by the weeks the appointments were completed. Each officer will complete up to 6 appointments in any day so it is important to break it down for ease of use.

Are there any calculated fields I could use that would A: give me the relevant working week e.g 17/04/2017 - 21/04/2017 (Monday to Friday) and b: the day of the week eg. monday, Tuesday, Weds etc etc? Thanks, I hope that makes sense. I am still fairly new to Report builder/SSRS so hopefully I have given enough information.

Thanks

Freddie

Freddie
  • 51
  • 1
  • 2
  • 12
  • 1
    Pick a day to use as your anchor day of the week - (i.e. Sunday). Then use a formula to calculate the Sunday of the week your date is in: `DATEADD(DAY, 1 - DATEPART(DW, DATE_FIELD), @DATE_FIELD)`. Group By the calculated date. Use DATEPART with **dw** to get the day of the week (`DATEPART(DW, DATE_FIELD)` ). This **ass**u**me**s that Sunday is the first day of your week. – Hannover Fist Apr 18 '17 at 20:47
  • Thanks for your help. I have used some of your formula to get the day of the week as an anchor. I needed to get the monday and friday of the week so that I could group the report by working week. I used "=DateAdd(DateInterval.Day,1,DateAdd(DateInterval.WeekOfYear,Fields!Week.Value -1, Fields!firstDateOfYear.Value))" for the monday and changed the value to get the Friday. This allowed me to group in to working weeks monday to friday and then by using the datepart formula, i got the day of the week which I could then sort on. Took me a while to get my head round it but got there in the end! – Freddie Apr 20 '17 at 13:03

0 Answers0