CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2008ssrs-2008-r2
user3312649
user3312649

Reputation: 300

SSRS Timesheet like form

I have this record in my sql server.. 

RecId  RefJobTicket    TimeStart   TimeEnd VerByStart  VerByEnd    Cargo   PreTrip Transit LoadingUnloading    WaitForAdvice   MealBreak   Breakdown   PostTrip    Refuel  Remarks
2          1            12:00       1:00        NULL    NULL        NULL       0       0           0                   0           0           0        NULL           0      NULL
49         1            3:00        4:00        NULL    NULL        NULL       0       0           0                   0           0           0        NULL           0      NULL
50         1            5:00        8:00        NULL    NULL        NULL       0       0           0                   0           0           0        NULL           0      NULL

In my SSRS REPORT.. I want to put it in a Pre Defined form that look like below according to the available records i have in the sql server: 

time start      time end     Refuel    Trip      Meal Break

12:01AM         1:00AM          0      Null         Null
 1:01AM         2:00AM
 2:01AM         3:00AM
 3:01AM         4:00AM        Null       0          Null
 4:01AM         5:00AM
 5:01AM         6:00AM        Null       Null        0
 6:01AM         7:00AM        Null       Null        0
 7:01AM         8:00AM        Null       Null        0

 8:01AM         9:00AM
 9:01AM        10:00AM
10:01AM        11:00AM
11:01AM        12:00PM
12:01PM         1:00PM
 1:01PM         2:00PM
 2:01PM         3:00PM
 3:01PM         4:00PM
 4:01PM         5:00PM
 5:01PM         6:00PM
 6:01PM         7:00PM
 7:01PM         8:00PM
 8:01PM         9:00PM
 9:01PM        10:00PM
10:01PM        11:00PM
11:01PM        12:00AM

Can i do this in ssrs r2?? Can any one help me?

Upvotes: 0

Views: 315

Answers (1)

Aftab Ansari
Aftab Ansari

Reputation: 946

Yes, You can do it. you need to take care of following points:

Step1: your query should return all the times (12:01AM to 12:00AM). You can do a cross join of following query on your main query. Note that, Report can not generate those times that are not coming from your sql query. 

;with Minute_Cycle
as
(
select cast('12:01AM' as time) Mint
Union ALL
Select DATEADD(HOUR,1,cast(Mint as time)) Mint from Minute_Cycle
where convert(varchar(15),cast(Mint as time),100)<>'11:01PM'
)
Select convert(varchar(15),Mint,100) as time_start, convert(varchar(15),DATEADD(MINUTE,59,Mint),100) time_End from Minute_Cycle

Step2 Place a table and design your report as per your choice.

Upvotes: 1

Related Questions