visual studio - How can get a full subtraction of time of these two time values in SSRS? -

i have 2 time values in 2 separate expressions in ssrs subtract 1 other give me sub total time.

at present value 1 of 163:02:38 , expression follows:

=system.math.floor(sum(fields!staffed_time.value) / 3600) & ":" & microsoft.visualbasic.strings.format(microsoft.visualbasic.dateandtime.dateadd("s", sum(fields!staffed_time.value), "00:00"), "mm:ss") 

while value 2 of 5:12:46

=system.math.floor(sum(fields!time_in_default.value) / 3600) & ":" & microsoft.visualbasic.strings.format(microsoft.visualbasic.dateandtime.dateadd("s", sum(fields!time_in_default.value), "00:00"), "mm:ss") 

meaning sub total desire 157:49:52

now when use expression

=(system.math.floor(sum(fields!staffed_time.value) / 3600) - system.math.floor(sum(fields!time_in_default.value) / 3600)) & ":" & microsoft.visualbasic.strings.format(microsoft.visualbasic.dateandtime.dateadd("s", sum(fields!staffed_time.value), "00:00"), "mm:ss") 

it subtracts hour values in case remove 5 hours, therefore leaving me sub total of 158:02:38

therefore how can expression subtract minutes , seconds desired subtotal?

a better solution building large expression add following custom code report:

public function convertsecondstotime(seconds integer) string     dim ts timespan = timespan.fromseconds(seconds)     return floor(ts.totalhours).tostring() + ":" + ts.minutes.tostring() + ":" + ts.seconds.tostring() end function 

and use custom code in expression so:

=code.convertsecondstotime(sum(fields!staffed_time.value) - sum(fields!time_in_default.value)) 

your 2 fields represented in seconds, so, rather calculating , subtracting each unit of time (hours, minutes , seconds) separately , applying custom format, subtract in seconds (which in given example of 157:49:52 568192 seconds) , apply custom format.

the timespan class take seconds , convert units of time calling timespan.fromseconds.

you may wonder why use totalhours instead of hours. keep in mind timespan hours based on 24-hour clock, outside of treated days. in comparison, totalhours stated in timespan msdn documentation represents:

the value of current timespan structure expressed in whole , fractional hours.

in other words, represents whole time in decimal hours. comparable how current expression calculating hours, hence why use floor(ts.totalhours) in custom code.