SSRS Calculating and Converting Time with Expression
Recently I came across a challenging issue with calculating a time span within a group inside an SSRS Report. I am working on an issue where I need to be able to calculate labor pace operations per hour, and the time a person works on a particular labor task. My report data looks like the following:
Employee No |
Time |
Weight |
240 | 07:34:21 | 13.805 |
07:56:09 | 13.505 | |
08:24:03 | 14.890 | |
09:05:17 | 9.975 | |
09:46:51 | 13.990 | |
09:48:47 | 11.560 | |
10:14:31 | 11.510 | |
10:44:13 | 13.325 | |
11:07:37 | 10.245 | |
12:09:19 | 14.765 | |
12:20:58 | 13.170 | |
12:42:54 | 15.735 | |
13:00:49 | 15.305 | |
13:18:46 | 16.865 | |
13:28:50 | 15.365 |
As you can see, this person submitted their first weight at 7:34 AM with a weight of 13.805 lbs. In SSRS, it’s fairly straightforward to have a total for the weight, however, a total for the shift proved to be a little more challenging. I was hoping I could simply use the SUM() function in hopes that SSRS would know how to handle the time format and just give me a total time. No such luck. Through a series of trials and errors with the TimeSpan function, I realized that I could use the Min() and Max() functions with a simple math equation. Ultimately, my formula wound up being the following:=(Max(Fields!Time.Value)-Min(Fields!Time.Value))when applying the function, it yielded the a total of:05:54:29My next challenge was formatting the total so that it could be used in an equation with the total weight in order to determine my average lbs per hour. After several hours of trial and error with various TimeSpan and DateDiff/DatePart functions, I found simple was best and ended with this formula to get my results:=(Sum(Fields!Weight.Value))/(((DatePart("h",(Max(Fields!Time.Value)))*60+DatePart("n",(Max(Fields!Time.Value))))-(DatePart("h",(Min(Fields!Time.Value)))*60+DatePart("n",(Min(Fields!Time.Value)))))/60)this got me an average of 34.58 lbs/hr and was able to use the formula throughout the report.