SSRS Lookup Function Returns #Error

Question

I am just getting into SSRS and have hit a problem with a lookup function which I cannot see the problem with.

I have an expression with a lookup function which should return a value from another dataset but it's returning #Error

I want to list all the mortgages a person has and return the description string of the Mortage Type in another table.

So, my Mortgage Table looks like this:

And my lookup table looks like this:

I want to join the Mortgages.MortgageType to the TypeIndex.ID and return the TypeIndex.Description

Which I thought the following should do:

=Lookup(Fields!MortgageType.Value,Fields!ID.Value,Fields!Description.Value, "TypeIndex")

I have checked and checked but just cannot seem to fix the problem?

Answer 1

In this case your lookup dataset may returning more than one value for Description field. If TypeIndex dataset will return more than one value for 'Description' for a ID then it will throw an error.

To handle this you can have a function in your report code part and later in the expression you can use that function. The function would be something like below. This has been not tested and you might have a modify a bit.

Function MergeLookup(ByVal items As Object()) As String
If items Is Nothing Then
Return Nothing
End If
Dim suma As String = New String()
Dim ct as Integer = New Integer()
suma = ""
ct = 0
For Each item As Object In items
suma += Convert.ToString(item) + ","
ct += 1
Next
If (ct = 0) Then return 0 else return suma 
End Function

Now In the expression you can call this function like below.

=Code.MergeLookup(Lookup(Fields!MortgageType.Value,Fields!ID.Value,Fields!Description.Value, "TypeIndex"))

THis MergeLookup function will return a string with comma separated for different 'Description' values for the same ID.

Let me know if you are still facing some issues.