December 29, 2009 at 11:19 am
I appologize for the double post (previously posted on the BI forum) apparently the wrong forum.
I'm using Frank Fernando's function to convert seconds to hh:mm:ss in a property expression in SSRS and it works great on my detail line. Now I need to use a similar method to display the average (hh:mm:ss) duration on my group footer.
I know very little vb so my approach would be to somehow divide each of the values HOURS, MINUTES, and SECONDS by the DISTINCT COUNT of my grouping key which is LOAD_ID but I can't find a VB Script method for that.
I may be way off base here, if so could someone point me in the right direction or have a better way to get to hh:mm:ss (as an average when not aggregating the group seconds in the query).
example of my approach:
Function GetDurationAsHHMMSS(ByVal TOTALSECS As Double, ByVal LOADID As String) As String
Dim HOURS As Integer
HOURS = Math.Floor(TOTALSECS / 3600)/COUNT(DISTINCT LOADID)
.
.
.
This is the current function used in the detail line that works fine:
Function GetDurationAsHHMMSS(ByVal TOTALSECS As Double) As String
Dim HOURS As Integer
HOURS = Math.Floor(TOTALSECS / 3600)
Dim MINUTES As Integer
MINUTES = Math.Floor ( ( TOTALSECS - ( HOURS * 3600 ) ) / 60 )
Dim SECONDS As Integer
SECONDS = Math.Floor(TOTALSECS - (HOURS * 3600) - (MINUTES * 60))
Dim HHMMSS As String
If HOURS > 999 Then
HHMMSS = Microsoft.VisualBasic.Right("0000" + RTrim(CType(HOURS, String)), 4) & ":" & Microsoft.VisualBasic.Right("00" & RTrim(CType(MINUTES, String)), 2) & ":" & Microsoft.VisualBasic.Right("00" & RTrim(CType(SECONDS, String)), 2)
ElseIf HOURS > 99 Then
HHMMSS = Microsoft.VisualBasic.Right("000" + RTrim(CType(HOURS, String)), 3) & ":" & Microsoft.VisualBasic.Right("00" + RTrim(CType(MINUTES, String)), 2) & ":" & Microsoft.VisualBasic.Right("00" & RTrim(CType(SECONDS, String)), 2)
Else
HHMMSS = Microsoft.VisualBasic.Right("00" + RTrim(CType(HOURS, String)), 2) & ":" & Microsoft.VisualBasic.Right("00" + RTrim(CType(MINUTES, String)), 2) & ":" & Microsoft.VisualBasic.Right("00" & RTrim(CType(SECONDS, String)), 2)
End If
Return HHMMSS
End Function
Frank's Article : http://www.sqlservercentral.com/articles/Reporting+Services+%28SSRS%29/67660/
Any help is greatly appreciated.
Thanks,
Scott
January 4, 2010 at 11:47 pm
Hi Scott,
Would it work if you use =GetAvgDurationAsHHMMSS(secs, CountDistinct(Fields!LOAD_ID.Value)) as the expression in your textbox at the group level, and then something like this in the Code:
Function GetAvgDurationAsHHMMSS(ByVal TOTALSECS As Double, ByVal GroupRowCount As Integer) As String
Dim HOURS As Integer
HOURS = Math.Floor((TOTALSECS / 3600) / GroupRowCount)
.
.
.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply