0

I have 2 tablix in my report. I need to find the total time in hours and minutes ignoring any day counts. In the appointments tablix I am using expression

=FLOOR(Sum(Fields!actualdurationminutes.Value) / 60) & ":" & RIGHT("0" & (Sum(Fields!actualdurationminutes.Value) 
MOD 60), 2)

--acutaldurationminutes is a whole number field automatically populated by code behind the field calculating from the start and end times.

I want to do the same in the events tablix but the column I need to total is made up of the time difference between 2 datetime values instead of a single value. I tried to add DATEDIFF into the expression above to get a single time value but it is giving an error:

=FLOOR(Sum(DateDiff(DateInterval.Minute,Fields!ccx_startdate.Value,Fields!ccx_enddate.Value))/60)&":"& RIGHT("0"&(Sum(DATEDIFF(DateInterval.Minute, Fields!ccx_startdate.Value,Fields!ccx_enddate.Value))MOD 60),2)

I have spent many hours trying to find a solution to this and used this to help with the DateDiff part of the expression: https://stackoverflow.com/questions/20084113/time-difference-between-datetime-fields-ssrs - help to find how to use datediff in expression however there still seems to be something not quite right. I am getting error 'Argument not specified for parameter 'Length' of 'Public function RIGHT(str as String, Length as Integer)AS STRING'

When I have got this right I will then need to add together the answer from both expressions to get the total time per customer in hh:mm

I would be grateful for any help on this. I am learner level so I apologize if I have been too detailed.

Community
  • 1
  • 1
Caroline Allen
  • 183
  • 14

1 Answers1

0

Try using the following code:

=(FLOOR(Sum(DateDiff(DateInterval.Minute, Fields!ccx_startdate.Value, Fields!ccx_enddate.Value))/60)) &":"& FLOOR(Sum(DateDiff(DateInterval.Minute,Fields!ccx_startdate.Value,Fields!ccx_enddate.Value)))-((FLOOR(Sum(DateDiff(DateInterval.Minute, Fields!ccx_startdate.Value, Fields!ccx_enddate.Value))/60))*60)

SuperSimmer 44
  • 964
  • 2
  • 7
  • 12
  • Marked as correct answer, however could you tell me what I need to add to get 7:0 to read as 07:00 (but I don't want 25 hours to read as 1:01:00 - just as the number of hours 25:00) - is that just formatting in the tablix cell or do I need to add something to the expression? – Caroline Allen Jan 30 '17 at 09:57
  • Using the same original code but adding in format conditions: please try: =Format((FLOOR(Sum(DateDiff(DateInterval.Minute, Fields!ccx_startdate.Value, Fields!ccx_enddate.Value))/60)),"00") &":"& Format(FLOOR(Sum(DateDiff(DateInterval.Minute,Fields!ccx_startdate.Value,Fields!ccx_enddate.Value)))-((FLOOR(Sum(DateDiff(DateInterval.Minute, Fields!ccx_startdate.Value, Fields!ccx_enddate.Value))/60))*60),"00") – SuperSimmer 44 Jan 30 '17 at 11:48
  • note that if the number of hours exceeds 99hrs, only the first two numbers are displayed – SuperSimmer 44 Jan 30 '17 at 11:50
  • So, the formatting is wrapped around the existing expression. This works beautifully and the clarification was very useful. – Caroline Allen Jan 31 '17 at 07:48