November 6, 2014 at 7:08 am
Hi.
Have this custom code in SSRS report which have had in use for ages now...
Public Function cvtSecondToHHMMSS(Second As Integer) As String
Dim hr as Integer'Hours can be greater than 24
Dim mi as Integer
Dim sec as Integer
hr = int( Second / 3600 )
mi = int( ( Second - ( hr * 3600 ) ) / 60 )
sec =int( Second MOD 60 )
Return Cstr(hr)+":"+Right("0"+Cstr(mi), 2)+":"+Right("0"+Cstr(sec), 2)
End Function
If in a Tablix cell I have this value expression to calculate an average...
=iif(CountRows()=0,"na",code.cvtSecondToHHMMSS(Sum(Fields!DurationSecs.Value)/CountRows()))
it renders #Error when that row/column has no records, but all other row/column have correct result.
However, if in adjacent cell inside group, I have either of these value expressions...
=iif(CountRows()=0,"na",code.cvtSecondToHHMMSS(Avg(Fields!DurationSecs.Value))) or
=iif(CountRows()=0,"na",Sum(Fields!DurationSecs.Value)/CountRows())'no conversion
they work in all cases...
Ok so I know the answer... But I dont know why? Anyone? SSRS 2012
Best regards Dave.
November 6, 2014 at 9:14 am
Is it that pesky IIF "because IIF essentially calculates both return values and THEN decides which one to give you"
Not sure now...
November 6, 2014 at 3:49 pm
Try using a SWITCH rather than an IIF. That will eliminate any simple divide/0 errors from the IIF behavior.
Actually, it looks like you've got no NULL handling in your function, I suspect that is the root of your problem. Use a SWITCH to account for NULL and divide/0 and/or rewrite the function to handle NULLs.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply