SSRS 2016 "Function doesn't return a value on all code paths"

Question

I am in the process of restoring some reports. To do this I had to grab and re-create the RDL files with the method here: Where does a published RDL file sit.

I am taking the output from the query in that other thread, save results as (as one commenter noted that you might not get all of the data otherwise), then opening it in a text editor to strip out some junk characters at the beginning, and replacing two double quotes in a row with one double quote, then uploading the RDL file to the report server.

This has been working so far for reports without any custom functions - however, once I got to a report with SumLookup and AvgLookup functions, I get the following error:

An error has occurred.

An unexpected error occurred while compiling expressions. Native compiler return value: '[BC42353] Function 'AvgLookup' doesn't return a value on all code paths. Are you missing a 'Return' statement?'.

The original AvgLookup function which I googled before and has been working fine up until now is:

Function AvgLookup(ByVal items As Object()) As Decimal
If items Is Nothing Then
    Return Nothing
End If
Dim suma As Decimal = 0
Dim avga As Decimal = 0
Dim counta As Integer = 0
For Each item As Object In items
    If Not item Is Nothing Then

    counta += 1

    suma += Convert.ToDecimal(item)
    End If
Next
If counta &gt; 0 Then
    avga = suma / counta
Else
    avga = 0
End If
Return avga
End Function

I tried replacing this with a simple function that simply returns 1:

Function AvgLookup(ByVal items As Object()) As Decimal
Return 1
End Function

But this gives me the same error.

Everything I have searched for so far seems to involve a function which actually doesn't always have a return value, so I am at a bit of a loss that the simple "Return 1" function gives the same error.

All I can think to do is delete all custom code from the RDL files as well as any references to those functions and then replace them after uploading. Is there possibly another solution?

Does this work? Function AvgLookup() As Decimal Return 1 End Function — Ross Bush, Oct 10 '17 at 14:24
I appreciate the response - just tried that and got the same error. What I ended up doing: Remove the function from the .rdl, Search and replace "Code.AvgLookup" with "AVg" (note the capitalization), Upload this file, Add the AvgLookup function in Report Builder, Download the .rdl, Search and replace "AVg" (case sensitive) with "Code.AvgLookup", reupload. I didn't have too many reports that used this function, so it wasn't too big of a deal. But I would be curious to know _why_ this happens, it seems strange. — C Black, Oct 10 '17 at 14:44
hmmm, one last thing I would try. See if there is some weird edge case conversion in your ui logic. Try using As String as the result and Return "1". If that does not yield anything more substantial then yes, I would delete the existing .rdl from ssrs and re-deploy freshly. — Ross Bush, Oct 10 '17 at 14:48
Slightly different error with string and returning "1", but basically the same: `An unexpected error occurred while compiling expressions. Native compiler return value: ‘[BC42105] Function 'AvgLookup' doesn't return a value on all code paths. A null reference exception could occur at run time when the result is used.’.` — C Black, Oct 10 '17 at 14:51
Perhaps using the Public modifier? Public Function AvgLookup(...) — Ross Bush, Oct 10 '17 at 14:58
Same thing unfortunately, tried that with both string and decimal. — C Black, Oct 10 '17 at 15:06
And now I'm going to go slap myself in the face...figured out what the issue was, will post the solution as an answer just in case anyone ever runs into this in the future. — C Black, Oct 10 '17 at 15:10

score 0 · Answer 1 · answered Oct 10 '17 at 15:16

So I eventually figured out the issue.

Essentially, the query that retrieved the actual code for the report files from the report server leaves the code all on one line. Functions need to have each command on a new line. When I edited the RDL file, I was keeping the function all on that same one line.

So I've learned my lesson to paste exactly the code I have when asking for help, instead of creating new lines for better readability if they aren't really there, as that may have solved the issue much sooner.

SSRS 2016 "Function doesn't return a value on all code paths"

1 Answers1