CODEWITHSUNDEEP
visual-studioreporting-servicesssrs-2008
Dan Tracey
Dan Tracey

Reputation: 77

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

I currently have two time values in two separate expressions in SSRS which I would like to subtract one from the other to give me a sub total time.

At present value 1 is of 163:02:38 and the expression is as 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 is 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 that the sub total I desire would be 157:49:52

Now when I use this 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 only subtracts the hour values which in this case would remove 5 hours, therefore only leaving me with a sub total of 158:02:38

Therefore how can I get the expression to also subtract the minutes and seconds to get the desired subtotal?

Upvotes: 0

Views: 1440

Answers (3)

Aaron D
Aaron D

Reputation: 5876

The math in your third code sample is flawed in two ways. First, you are only subtracting the hours and completely disregarding the "minutes and seconds" subtraction. Second, you are rounding the hours before doing the subtraction, which could cause off-by-one issues under certain circumstances.

To solve your particular circumstance, you could fix your following formula like so:

=(System.Math.Floor((Sum(Fields!Staffed_Time.Value) - Sum(Fields!Time_in_Default.Value)) / 3600) & ":" & Microsoft.VisualBasic.Strings.Format(Microsoft.VisualBasic.DateAndTime.DateAdd("s", Sum(Fields!Staffed_Time.Value) - Sum(Fields!Time_in_Default.Value), "00:00"), "mm:ss")

Breaking this down into components:

  1. Subtract Time_in_default from Staffed_Time and then determine the hour count.
  2. Subtract the Time_in_default from Staffed_Time and display the minutes and seconds.

But, to make this even simpler, just use VisualBasic's Strings.Format for the whole piece and avoid the math altogether:

=Microsoft.VisualBasic.Strings.Format(Microsoft.VisualBasic.DateAndTime.DateAdd("s", Sum(Fields!Staffed_Time.Value) - Sum(Fields!Time_in_Default.Value), "00:00"), "HH:mm:ss")

That won't require any custom code and is reasonably easy to understand.

Upvotes: 0

BIDeveloper
BIDeveloper

Reputation: 2638

Assuming you have access to the database you are far better doing any complex data handling on the database side.

  1. It's faster to develop and easier to debug.
  2. Assuming you present the data via a stored procedure or view it is faster to run as it is compiled.
  3. It's easier to manage changes to the schema in the future.
  4. The view or stored procedure (i.e. you calculation) can be used by others.

Therefore, however you are creating your dataset, I would present a further column called e.g. TimeDifference and pass this into SSRS.

Upvotes: 1

Jonathon Ogden
Jonathon Ogden

Reputation: 1582

A better solution to building a large expression would be to add the following custom code to your report:

Public Function ConvertSecondsToTime(seconds As Integer) As String
    Dim ts as TimeSpan = TimeSpan.FromSeconds(seconds)
    return Floor(ts.TotalHours).ToString() + ":" + ts.Minutes.ToString() + ":" + ts.Seconds.ToString()
End Function

And use the custom code in an expression like so:

=code.ConvertSecondsToTime(Sum(Fields!Staffed_Time.Value) - Sum(Fields!Time_in_Default.Value))

Your two fields are represented in seconds, so, rather than calculating and subtracting each unit of time (hours, minutes and seconds) separately and applying the custom format, subtract in seconds (which in your given example of 157:49:52 would be 568192 seconds) and then apply the custom format.

The TimeSpan class will take the seconds and convert that into units of time by calling TimeSpan.FromSeconds.

You may wonder why we use TotalHours instead of Hours. Keep in mind that TimeSpan hours are based on a 24-hour clock, so anything outside of that will be treated as days. In comparison, TotalHours as stated in TimeSpan MSDN documentation represents:

the value of the current TimeSpan structure expressed in whole and fractional hours.

In other words, it represents the whole time in decimal hours. This is comparable to how your current expression is calculating the hours, hence why we use Floor(ts.TotalHours) in the custom code.

Upvotes: 2

Related Questions