CODEWITHSUNDEEP
reporting-servicesssrs-tablix
Surya Prakash
Surya Prakash

Reputation: 33

Grouping by date and time values from a DateTime value in SSRS in 24 hour format

I have a Stored Procedure which will be returning the columns Date( in yyyy/mm/DD HH:mm:ss format),Names and Data. When I'm making the Date part of Date(in MM/dd/yyyy format as parent row group ,Time part of Date as child row group and Name as column group and Data as the value in the matrix, I'm getting the time values consistently from 1:00 ,1:30,2:00,2:30 .. to 11:30 and it is displaying 23:59,12:00, 00:30 and jumping to the other Date group instead of staring from 00:30,01:00 ...to 11:30 and continuing as 12:00,12:30,13:00 to 23:59. The SP results will contain the time for one of the names from 00:30,01;00,01:30 to 23:30 and 23:59 and the other name will have times as 01:00,02:00 to 23:00 and 23:59.

I'm getting the values correctly up to only 11:30 starting from 01:00. I need the time values to be 00:30,01:00,01:30...12:00,12:30...23:00,23:59

Note:(since grouping is involved and one of the name's time does not equals other, 0 will be displayed in those cells - It is also working fine but only up to time 11:00 and it is displaying 23:59,12:00,00:30 and jumping to the other Date group

Can someone please help on this?

Thanks in advance.

Upvotes: 0

Views: 438

Answers (2)

Hannover Fist
Hannover Fist

Reputation: 10860

It sounds like you are grouping for time in a 12-hour format but not including the period (AM/PM) which is tt in the formatting.

hh is for a 12 hour so you would need to add the tt

=FORMAT(Fields!DATE_FIELD, "hh:mm tt"

HH is for a 12 hour so you would not need to add the tt

=FORMAT(Fields!DATE_FIELD, "HH:mm"

Upvotes: 1

Surya Prakash
Surya Prakash

Reputation: 33

The issue seems to be the type of grouping. I have used date for one of the groups and string type for other group. Now I have changed both to string and it is working fine.

Upvotes: 0

Related Questions