February 18, 2016 at 6:13 am
Hi,
I have a column of varchar datatype which stores the values in the format like 112:10:20. I need to do a sum of these values on the report level in SSRS. For e.g. if the values are
112:30:20
25:14:15
100:57:23
Then the resultset should be - 237:101:58
Can somebody please advise on how to achieve this in SSRS ? Thanks.
February 18, 2016 at 9:02 am
SSRS doesn't really have a native timespan type. You can achieve your goal using custom functions though. I came up with this so far.
Function StrToTimeSpan(a As String) As System.TimeSpan
Dim hours = a.Split(":").GetValue(0)
Dim mins = a.Split(":").GetValue(1)
Dim secs = a.Split(":").GetValue(2)
Return New System.TimeSpan(hours, mins, secs)
End Function
Function TimeSpanAdd(spans As Object()) As String
Dim ts As New System.TimeSpan(0)
For Each t As String In spans
ts = ts.Add(StrToTimeSpan(t))
Next
Return ts.ToString()
End Function
I then called this via an expression like so:
=Code.TimeSpanAdd(LookupSet(1,1,Fields!TimeSpans.Value,"DataSet1"))
The lookupset gets all timespans in the dataset as an array which the function needs.
The output is slightly different - has days - but should be easy to change, this will help.
February 18, 2016 at 9:34 am
Get me? (2/18/2016)
SSRS doesn't really have a native timespan type. You can achieve your goal using custom functions though. I came up with this so far.
Function StrToTimeSpan(a As String) As System.TimeSpan
Dim hours = a.Split(":").GetValue(0)
Dim mins = a.Split(":").GetValue(1)
Dim secs = a.Split(":").GetValue(2)
Return New System.TimeSpan(hours, mins, secs)
End Function
Function TimeSpanAdd(spans As Object()) As String
Dim ts As New System.TimeSpan(0)
For Each t As String In spans
ts = ts.Add(StrToTimeSpan(t))
Next
Return ts.ToString()
End Function
I then called this via an expression like so:
=Code.TimeSpanAdd(LookupSet(1,1,Fields!TimeSpans.Value,"DataSet1"))
The lookupset gets all timespans in the dataset as an array which the function needs.
The output is slightly different - has days - but should be easy to change, this will help.
Actually the datatype of the column in sql is varchar, however, it stores the values in the format like 123:12:23. It is not a timespan column, sorry if my initial query was confusing.
I just need to be able to do a Sum or Average for this type of values as shown as an example in my initial query, is that possible maybe like using charindex or something ? Thanks.
February 18, 2016 at 10:01 am
Nope, I understood that the columns were varchars, hence the StrToTimeSpan function. What I didn't get was that the totals would be summing of the various time units rather then the actual timespan.
If you wanted to do that I guess the best way would be to create calculated fields on the report dataset using Split.
E.g.
for the hours =Cint(Split(Fields!TimeSpan.Value,":").GetValue(0))
for the minutes =Cint(Split(Fields!TimeSpan.Value,":").GetValue(1))
etc
You can then summarize and aggregate those values.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply