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:29

My 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.