How to group time into a range like 9:00-10:00 AM,10:00-11:AM etc in SSRS charts???

• var05

  Hall of Fame

  Points: 3011

  More actions

  September 7, 2012 at 3:11 am

  #267367

  Hi All,

  I have a trend chart showing total services per 1 hour time slot across the day. But I wanted to show another chart showing the total services across a time range, say for eg

  Time Total Services

  8:00 AM 6

  9:00 AM 4

  10:00 AM 2

  11:00 AM 10

  Now I have to group the time axis in the below way :

  Time Range

  8:00 -9:00

  9:00 -10:00

  10:00-11:00

  how to group the time range by 1 hour hour time slot in ssrs

  Any help on this??

  Thanks

  Varsha

• JohnnyDBA

  SSCertifiable

  Points: 6355

  More actions

  September 10, 2012 at 12:21 pm

  #1535870

  If you're using a DATETIME, you can use the DATEPART function to get the hour and group on the value for the hour based on a 24 hour clock.

  DATEPART(hh, GETDATE())

  You could then group on the hour number and use some kind of CASE statement to give it a pretty name

  CASE

  ...

  WHEN DATEPART(hh, GETDATE()) = 14 THEN '2:00pm-3:00pm'

  WHEN DATEPART(hh, GETDATE()) = 15 THEN '3:00pm-4:00pm'

  ...

  END

  Hope that helps.

  John